You are the IT manager for a mid-sized organization with 45 employees who each have a computer, either a laptop or a des
PLEASE DOWNLOAD HERE: https://theacademicessays.com/downloads/manager-mid-sized-organization-45-employees-computer-either-laptop-desktop-provided-organization-responsibility-ensure/ You are the IT manager for a mid-sized organization with 45 employees who each have a computer, either a laptop or a desktop, provided by the organization. It is your responsibility to ensure that the costs and depreciated value of the computer systems are known so that orders may be placed for new systems with sufficient time to replace the depreciated systems. The organization depreciates each desktop system over 5 years and each laptop over 4 years. The goal is to utilize Microsoft Excel skills to format data for business needs, analyze business data, and to make conclusions based on the data. Click here to download the spreadsheet for this assignment. On Sheet1, complete the following tasks: 1. Rename the tab, “Sheet1†to an appropriate name corresponding to the data included. 2. The data converted to the wrong format when extracted from the database, format the data in the Purchase Date column into a useful date format that includes the Month and Year only. 3. Make the Initial Cost and Depreciated Value cells automatically adjust to wrap on multiple lines to fit the headers. 4. Format the column headers to use Times New Roman, 10-point font, bold, and centered horizontally and vertically. Format the column headers with a dark background color of your choice and white font. 5. Adjust all columns to fit the widest data value or word in the column. The Depreciated Value columns should be wide enough to fit the headers wrapped onto three lines. 6. Make the system owner, department, system type, and purchase date data not bold. 7. Sort the data by department then system type, then purchase date. List the laptops before desktops for each department. 8. Add filters to the data so that a user can filter data by any column while still able to view the column headers. 9. Add a column between the Purchase Date and Initial Cost columns called Replacement Date to calculate the month and year that each computer system will have a depreciated value of $0. Use an appropriate function and/or formula to ensure that if a new system is purchased and added to the inventory, the function/formula can be copied to the new row and the data will be accurate. The function/formula must be the same for all rows and identify the correct system type (laptop or desktop) to determine if the system is depreciated over 4 or 5 years. (*Do not include a leap year, assume all years have 365 days). You have heard that the "IF" function is a useful feature of Excel but have not used it before. Use the Microsoft Excel onboard help feature by hitting F1 and look up how to use the "IF" function and apply what you learn to improve the functioning of your spreadsheet 10. In the Depreciated Value columns, use a function and/or formula to calculate the depreciated value of the system after 1 year, 2 years, 3 years, 4 years, and 5 years. The function/formula must be the same for each row of a column and must determine if the system being depreciated is a laptop or desktop. 11. Merge cells A50 through E50. 12. Align the “Average Cost†to the right of the merged cells. 13. Using a function, calculate the average initial cost. Use a mixed reference for the range of cells to ensure that the rows stay the same while the columns change if the function is copied. 14. Add Minimum Cost and Maximum Cost rows that are formatted similarly to row 50 (Average Cost row). Use functions to calculate the minimum and maximum costs for the systems purchased. 15. Convert the Initial Cost data including the Average Cost, Minimum Cost, and Maximum Cost, and the Depreciated Value data from a generic number to accounting format with no decimal places. 16. Copy the average, minimum, and maximum functions to the Depreciated Value columns. 17. Merge and center the title “Desktop & Laptop Systems Inventory & Depreciation†above all of the columns with data. 18. The CEO has asked you use conditional formatting to create an easy visual on the spreadsheet of any device that is near the end of its useful life. Utilize conditional formatting for all columns that contain a depreciated value of less than $500 they will appear red. 19. Create a pie chart to show the initial cost totals by department. You may create a set of summary data to the right of the current data to use for the creation of your chart. On the “Sales Data†sheet, complete the following tasks: 1. Use Autofill to add in the months, March through December in row 3. 2. Add monthly totals on row 22 and use a function to calculate the total sales for each month. 3. Format all the sales data including the employee names as a table. 4. Create a 3D column chart that shows the monthly sales totals. 5. Create a stacked column chart that shows the sales data by month for each employee and move the chart to a separate sheet. 6. Based on the monthly sales chart and the employee sales chart, what do you conclude about the company’s performance and the individual sales employees? Please submit your assignment. For assistance with your assignment, please use your text, Web resources, and all course materials. Grading Criteria Project Criteria Exceeds: 90%–100% Very Good: 80%–89% Meets: 70%–79% Needs Improvement: Below 70% Content (75%) Response covers all topics indicated in the assignment and adds additional content. Response covers most topics indicated in the assignment. Response covers many of the topics indicated in the assignment. Response covers none to some of the topics indicated in the assignment. Effective Communication (10%) Demonstrates outstanding or exemplary application of written, visual, or oral skills. Demonstrates outstanding expression of topic, main idea, and purpose. Audience is addressed appropriately. Language clearly and effectively communicates ideas and content relevant to the assignment. Errors in grammar, spelling, and sentence structure are minimal. Organization is clear. Format is consistently appropriate to assignment. Presentation and delivery are confident and persuasive Demonstrates very good written, visual, or oral skills. Demonstrates sound expression of topic, main idea, and purpose. Audience is usually addressed appropriately. Language does not interfere with the communication of ideas and content relevant to the assignment. Errors in grammar, spelling, and sentence structure are present, but do not distract from the message. Organization is apparent and mostly clear. Format is appropriate to assignment, but not entirely consistent. The Demonstrates acceptable written, visual, or oral skills. Demonstrates reasonable expression of topic, main idea, and purpose. Sometimes, audience is addressed appropriately. Language does not interfere with the communication of ideas and content relevant to the assignment. Errors in grammar, spelling, and sentence structure are present and may distract from the message. Organization is a bit unclear. Format is inconsistent. The writing was of collegiate level with several Demonstrates inadequate or partially proficient application of written, visual, or oral skills. Demonstrates inadequate or partial expression of topic, main idea, and purpose. Audience is often not addressed appropriately. Language often impedes the communication of ideas and content relevant to the assignment. Errors in grammar, spelling, and sentence structure are frequent and often distract from meaning or presentation. Organization is inadequate, confusing, and distracting. The format is inadequate and (where applicable). The writing was of collegiate level with no errors in spelling or grammar. writing was of collegiate level with two or less errors in spelling or grammar. errors in spelling or grammar. obscures meaning. The writing was less than collegiate level with numerous errors in spelling or grammar. Supporting Analysis (15%) Analysis exceeds minimum requirements. Sources are used to support analysis, are appropriate, and are properly referenced. Basic analysis provided to support assertions. Sources are cited, appropriate, and properly referenced. Limited analysis provided to support assertions. Some sources are cited, appropriate, and properly referenced. No or inaccurate analysis, no sources are cited when needed, analysis and/or sources are not appropriate. When sources are used, they are not properly referenced.
61 views • 4 slides
