Reference no: EM131149183
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.
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?
Attachment:- Data for Business Needs.xlsx