New Perspectives Excel 2019 | Modules 9-12: SAM Capstone Project 1a W&K Engineering ANALYZE DATA AND WORK WITH FINANCIAL TOOLS     GETTING STARTED Open the file NP_EX19_CS9-12a_FirstLastName_1

Subject:MS ExcelPrice:25.99 Bought15

Share With

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

W&K Engineering

ANALYZE DATA AND WORK WITH FINANCIAL TOOLS

• GETTING STARTED

• Open the file NP_EX19_CS9-12a_FirstLastName_1.xlsm, available for download from the SAM website.
• Save the file as NP_EX19_CS9-12a_FirstLastName_2.xlsm by changing the “1” to a “2”.
  • If you do not see the .xlsm file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
  • Support_EX19_CS9-12a_Employees.accdb
  • Support_EX19_CS9-12a_Sales.csv
• With the file NP_EX19_CS9-12a_FirstLastName_2.xlsm still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

• 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. 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, however, it includes extra data from column G. View and edit the macro as follows:
   1. Open the CopyStats macro in the Visual Basic Editor. The macro should select and copy the range I3:L22.
   2. In the CopyStats macro VBA code, change the range specified in the first line of code after the comments (Range("G3:L22").Select) to use I3:L22 as the range.
   3. Save and close the CopyStats macro.
   4. Run the CopyStats macro.
   5. Use Project Stats as the name of the new worksheet the macro created.
3. 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. He wants to assign the AddRow macro to a new button to make it easy to run the macro and add a new project. Create the button as follows:
   1. Insert a Button (Form Control) button in cell I1 to the left of the Print Stats button.
   2. Assign the AddRow macro to the new button.
   3. Change the height of the new button to 0.3" and the width to 1".
   4. Align the new button to the left side of cell I1, and then align its top and bottom with the top and bottom of the Print Stats button.
   5. Edit the text to display Add Project on the new button.
4. In the Projects table, Hwan wants to make sure that everyone entering project information enters the correct project categories, which are listed in the range I4:I7. He asks you to ensure users enter a value from this range. Create a data validation rule as follows:
   1. In the range C4:C37, create a data validation rule that accepts only List values from the range I4:I7.
   2. Create an input message that uses Project Category as the title and the following text as the input message:
      Enter a project category.
   3. Create a Stop style error alert that uses Invalid Category as the title and the following text as the error message:
      Enter a category listed in column I.
5. Hwan wants to make sure all of the categories entered in the Category column of the Projects table are valid. Identify and correct any invalid values as follows:
   1. In the range C4:C37, circle invalid data.
   2. Change the invalid entry to Site development to correct the error.
6. In the range I3:L7, Hwan wants to summarize project information. Calculate the number of projects, the total estimates, and the average estimate in each category as follows:
   1. In cell J4, enter a formula using the COUNTIF function that counts the number of projects in the Bridge category, checking that the column of categories in the Projects table (Projects[Category]) is equal to the value in cell I4.
   2. Fill the range J5:J7 with the formula in cell J4, filling without formatting.
   3. In cell K4, enter a formula using the SUMIF function that totals the estimates for projects in the Bridge category, checking that the column of categories in the Projects table (Projects[Category]) is equal to the value in cell I4, and that the formula totals the values in the Estimate column (Projects[Estimate]).
   4. Fill the range K5:K7 with the formula in cell K4, filling without formatting.
   5. In cell L4, enter a formula using the AVERAGEIF function that averages the estimates of projects in the Bridge category, checking that the column of categories in the Projects table (Projects[Category]) is equal to the value in cell I4, and that the formula averages the values in the Estimate column (Projects[Estimate]).
   6. Fill the range L5:L7 with the formula in cell L4, filling without formatting.
7. Hwan needs to identify the number of projects that have an estimate of more than $50,000. In cell J11, create a formula using the DCOUNT function to count the number of projects with estimates of more than $50,000, using the data in the entire Projects table (Projects[#All]) and counting the values in the column of estimates ("Estimate") that are equal to the values in the range I9:I10.
8. Hwan also needs to calculate the total amount paid by client W-0338 for engineering projects. In cell J15, create a formula using the DSUM function to calculate the total paid by client W-0338, using the data in the entire Projects table (Projects[#All]) and totaling the values in the Paid to Date column ("Paid to Date") that are equal to the values in the range I13:I14.
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. Refresh the PivotTable so it displays the changes you made to the Category data on the Projects worksheet.
10. Reduce the clutter in the PivotTable by modifying it as follows:
    1. Change the report layout to show the PivotTable in Outline Form.
    2. Group the Project Start values by Months.
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:
    1. Insert a Timeline Slicer based on the Project Start field.
    2. Move and resize the Timeline Slicer so it covers the range A34:F40.
    3. Adjust the Timeline Slicer to view all months.
12. Return to the Projects worksheet. In the range I17:J22, Hwan wants to display the project estimate amounts per state. He can retrieve this information from the PivotTable on the Projects by State worksheet. Display the estimates per state as follows:
    1. In cell J18, insert a formula using the GETPIVOTDATA function to display the total estimate amount for projects in Montana from cell G5 on the Projects by State worksheet.
    2. In cell J19, insert a formula using the GETPIVOTDATA function to display the total estimate amount for projects in North Dakota from cell G10 on the Projects by State worksheet.
    3. In cell J20, insert a formula using the GETPIVOTDATA function to display the total estimate amount for projects in Nebraska from cell G15 on the Projects by State worksheet.
    4. In cell J21, insert a formula using the GETPIVOTDATA function to display the total estimate amount for projects in South Dakota from cell G21 on the Projects by State worksheet.
    5. In cell J22, insert a formula using the GETPIVOTDATA function to display the total estimate amount for projects in Wyoming from cell G26 on the Projects by State worksheet.
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. Provide this information for Hwan as follows:
    1. Insert a calculated field named Amt Owed that subtracts the Paid to Date field amount from the Estimate field amount.
    2. Insert another calculated field named % Difference that subtracts the Paid to Date field amounts from the Estimate field amounts, and then divides the result by the Estimate field amount.
    3. Change the number format of the Sum of % Difference amounts to Percentage with 2 decimal points.
    4. Change the column heading in cell D3 to Amount Owed and change the column heading in cell E3 to % Owed to simplify the column headings.
15. Hwan needs to identify the location of each project. Add a slicer to the PivotTable as follows:
    1. Add a slicer based on the State field.
    2. Move and resize the slicer so it covers the range G3:I11.
    3. Use the slicer to filter the PivotTable to show only projects in Nebraska (NE).
16. Go to the Projects by Category worksheet, where Hwan has inserted a PivotTable showing the total estimates per category. He wants to further divide the project information by state. Display the State field values as column headings.
17. Hwan asks for another way to compare the total estimates for each project category and state. Create a PivotChart as follows:
    1. Based on the PivotTable on the Projects by Category worksheet, create a 100% Stacked Column PivotChart.
    2. Move and resize the PivotChart so that its upper-left corner is in cell A11 and its lower-right corner is in cell G25.
18. Go to the Monthly Sales worksheet, where Hwan wants to list the sales per month in fiscal year order (March–February). He has a text file that already contains this data. Use Power Query to create a query and load data from a CSV file into a new table as follows:
    1. Create a new query that imports data from the Support_EX19_CS9-12a_Sales.csv text file.
    2. Edit the query to remove the Projects and Notes columns.
    3. Close and load the query data to a table in cell A3 of the existing worksheet.
    4. Change the table style to Green, Table Style Medium 2 to coordinate with the rest of the workbook.
    5. Format the values of the Sales ($mil) column as Currency with 0 decimal places and the $ symbol.
19. Go to the Employees worksheet. Hwan wants to display information about employees in the five locations where W&K Engineering has offices. This information is available in an Access database. Import the data from the Access database and insert a PivotTable as follows:
    1. Create a new query that imports data from the Support_EX19_CS9-12a_Employees.accdb database.
    2. Select the Employees and Offices tables for the import.
    3. Only create a connection to the data and add the data to the Data Model.
    4. In cell A3 of the Employees worksheet, use Power Pivot to insert a PivotTable based on the data in the Employees and Offices tables.
20. Hwan wants to show the number of employees by title and the average of their years employed at each W&K Engineering office. Modify the new PivotTable on the Employees worksheet as follows to provide this information:
    1. In the new PivotTable, display the OfficeCity field from the Offices table as the row headings.
    2. Display the Title field from the Employees table as the first Values field.
    3. Display the YearsEmployed field from the Employees table as the second Values field.
    4. Change the summary function of the Sum of YearsEmployed field to calculate the average number of years.
    5. Change the number format of the Average of YearsEmployed field to use the Number number format with one decimal place.
21. In order to relate the data in the Employees and Offices tables to make a proper comparison, use the Power Pivot window to create a relationship between the Employees and Offices tables based on the OfficeID field.
22. Go to the Expansion Loan worksheet, which contains data about a loan to expand the business by purchasing additional construction equipment. Hwan wants to include a worksheet title to match the format of the other worksheets in the workbook.
    Insert and format WordArt as follows:
    1. Insert WordArt using the Fill: Green, Accent color 1; Shadow style.
    2. Type Expansion Loan as the worksheet title.
    3. Change the text fill of the WordArt to Black, Text 1, Lighter 25%.
    4. Change the font size to 24 point.
    5. Move the WordArt to row 1 so that it spans columns D:F.
23. Before performing any calculations, Hwan asks you to correct the errors in the worksheet.
    1. In cell L16, use the Error Checking command to identify the error in the cell.
    2. Correct the error to total the values in the range B16:K16.
    3. Use Trace Precedents arrows to find the source of the #DIV/0! error in cell B19.
    4. Correct the formula in cell B19, which should divide the remaining principal (cell B18) by the loan amount (cell C4) to find the percentage of remaining principal.
    5. Fill the range C19:K19 with the formula in cell B19 without formatting to correct the remaining #DIV/0! errors.
    6. Remove any remaining trace arrows.
24. Now Hwan is ready to calculate the annual principal and interest payments for the expansion loan. Start by calculating the cumulative interest payments as follows:
    1. In cell B16, enter a formula using the CUMIPMT function to calculate the cumulative interest paid on the loan for Year 1 (payment 1 in cell B14 through payment 12 in cell B15). Use 0 as the type argument in your formula because payments are made at the end of the period.
    2. Use absolute references for the rate, nper, and pv arguments, which are listed in the range C4:C10.
    3. Use relative references for the start and end arguments.
    4. Fill the range C16:K16 with the formula in cell B16 to calculate the interest paid in Years 2–10 and the total interest.
25. Calculate the cumulative principal payments as follows:
    1. In cell B17, enter a formula using the CUMPRINC function to calculate the cumulative principal paid for Year 1 (payment 1 in cell B14 through payment 12 in cell B15). Use 0 as the type argument in your formula because payments are made at the end of the period.
    2. Use absolute references for the rate, nper, and pv arguments, which are listed in the range C4:C10.
    3. Use relative references for the start and end arguments.
    4. Fill the range C17:K17 with the formula in cell B17 to calculate the principal paid in Years 2–10 and the total principal.
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. In the range G4:G6, he estimates that the construction equipment will be worth $800,000 in tangible assets at the beginning of the loan, and that the useful life of these assets is 10 years with a salvage value of $128,000. Start by calculating the straight-line depreciation amounts as follows:
    1. In cell B23, enter a formula using the SLN function to calculate the straight-line depreciation for the construction equipment during the first year of operation.
    2. Use absolute references for the cost, salvage, and life arguments in the SLN formula.
    3. Fill the range C23:K23 with the formula in cell B23 to calculate the annual and cumulative straight-line depreciation in Years 2–10.
27. Calculate the declining balance depreciation amounts for the construction equipment as follows:
    1. In cell B29, enter a formula using the DB function to calculate the declining balance depreciation for the construction equipment during the first year of operation.
    2. Use Year 1 (cell B28) as the current period.
    3. Use absolute references only for the cost, salvage, and life arguments in the DB formula.
    4. Fill the range C29:K29 with the formula in cell B29 to calculate the annual and cumulative declining balance depreciation in Years 2–10.
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. Determine these amounts as follows:
    1. In cell B34, enter a formula using the SYD function to calculate the depreciation balance for the first year.
    2. Use Year 1 (cell B28) as the current period.
    3. In cell B35, enter a formula using the SYD function to calculate the depreciation balance for the last year.
    4. Use Year 10 (cell K28) as the current period.
29. Go to the Investments & Projections worksheet. Besides using a bank loan to fund their upcoming expansion, W&K Engineering is looking for investors. This worksheet should show the returns potential investors could realize if they invested a total of $400,000 in the company. Hwan knows that a desirable rate of return would be 9 percent. He also estimates the investment would pay different amounts each year (range B6:B14). Calculate the present value of the investment and the internal rate of return as follows:
    1. In cell B18, enter a formula that uses the NPV function to calculate the present value of the investment in funding the company's expansion.
    2. Use the target rate of return value (cell B17) as the rate argument.
    3. Use the payments in Years 1–10 (range B5:B14) as the returns paid to investors. (Hint: If a Formula Omits Adjacent Cell error warning appears, ignore it.)
    4. In cell B20, enter a formula that uses the IRR function to calculate the internal rate of return for the investment.
    5. Use the payments for Years 1–10 (range B5:B14) as the returns paid to 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:
    1. Add a Trendline to the Monthly Revenue Projections scatter chart.
    2. Format the trendline to use the Logarithmic option.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.