New Perspectives Excel 2019 | Modules 9-12: SAM Capstone Project 1a

Please contact us if you need this project or any other project in any subject to be completed.
Whatsapp: +1-343-321-3276
Email: essayserviceusa@gmail.com
WhatsApp Link: https://wa.me/13433213276
#NewPerspectivesExcel2019 #Modules9-12SAMCapstoneProject1a
#W&KEngineering

New Perspectives Excel 2019 | Modules 9-12: SAM Capstone Project 1a
W&K Engineering
ANALYZE DATA AND WORK WITH FINANCIAL TOOLS
PROJECT STEPS
1. Hwan Sung is a financial analyst at W&K Engineering in Bismarck, North Dakota. He is using an Excel workbook to analyze the financial data for engineering projects, recent revenue, and funding for additional construction equipment. He asks for your help in analyzing data, making financial calculations, and correcting errors.
Go to the Projects worksheet and then unprotect it so you can edit the contents.
2. The engineering projects table should show projects from multiple states. Clear the filter in the State field.
3. Instead of seeing the data sorted by Estimate amount, Hwan wants to see the projects by date. Sort the table by the Project Start field from oldest to newest.
4. Hwan created a macro named CopyStats to copy the project statistics in the range I3:L22, and then paste them on a new worksheet. When he runs the CopyStats macro,
5. Return to the Projects worksheet. Hwan also created a macro named AddRow that inserts a new blank row in the Projects table in the range A4:G37.
6. In the Projects table, Hwan wants to make sure that everyone entering project information enters the correct project categories
7. Hwan wants to make sure all of the categories entered in the Category column of the Projects table are valid.
8. In the range I3:L7, Hwan wants to summarize project information.
9. Go to the Projects by State worksheet. Hwan has created a PivotTable on this worksheet to list projects by state, start date, and category.
10. Reduce the clutter in the PivotTable by modifying it as follows:
11. Hwan occasionally would like to focus on the project data according to the start date. Add a Timeline Slicer as follows to the Projects by State worksheet:
12. Return to the Projects worksheet. In the range I17:J22, Hwan wants to display the project estimate amounts per state.
13. Go to the Project Payments worksheet, which contains a PivotTable showing the estimate and total paid amounts for each client. Add Category as the second field in the Rows area to include the category of each project in the PivotTable.
14. Hwan wants to know the difference and the percentage of difference between the estimated and the total paid amounts.
15. Hwan needs to identify the location of each project.
16. Go to the Projects by Category worksheet, where Hwan has inserted a PivotTable showing the total estimates per category.
17. Hwan asks for another way to compare the total estimates for each project category and state.
18. Go to the Monthly Sales worksheet, where Hwan wants to list the sales per month in fiscal year order (March–February).
19. Go to the Employees worksheet. Hwan wants to display information about employees in the five locations where W&K Engineering has offices.
20. Hwan wants to show the number of employees by title and the average of their years employed at each W&K Engineering office.
21. In order to relate the data in the Employees and Offices tables to make a proper comparison.
22. Go to the Expansion Loan worksheet, which contains data about a loan to expand the business by purchasing additional construction equipment.
23. Before performing any calculations, Hwan asks you to correct the errors in the worksheet.
24. Now Hwan is ready to calculate the annual principal and interest payments for the expansion loan.
25. Calculate the cumulative principal payments as follows:
26. Hwan wants to compare straight-line depreciation amounts with declining balance depreciation amounts to determine which method is more favorable for the company's balance sheet.
27. Calculate the declining balance depreciation amounts for the construction equipment as follows:
28. Hwan also wants to determine the depreciation balance for the first year and the last year of the useful life of the construction equipment.
29. Go to the Investments & Projections worksheet. Besides using a bank loan to fund their upcoming expansion, W&K Engineering is looking for investors.
30. The Monthly Revenue Projections scatter chart in the range E3:K20 is based on monthly revenue estimates listed on the hidden Monthly Revenue Projections worksheet. Hwan wants to include a trendline for this chart that shows investors how revenues will most likely increase quickly at first and then level off in later months. Modify the Monthly Revenue Projections scatter chart as follows to include a logarithmic trendline:
a. Add a Trendline to the Monthly Revenue Projections scatter chart.
b. Format the trendline to use the Logarithmic option.