question archive 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.01 Bought3

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_**, available for download from the SAM website.*FirstLastName*_1.xlsm - Save the file as
**NP_EX19_CS9-12a_**by changing the “1” to a “2”.*FirstLastName*_2.xlsm- 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.

- If you do not see the
- 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_**still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.*FirstLastName*_2.xlsm- If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

**PROJECT STEPS**

- 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. - 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:
- Open the
**CopyStats**macro in the Visual Basic Editor. The macro should select and copy the range I3:L22. - 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. - Save and close the CopyStats macro.
- Run the CopyStats macro.
- Use
**Project Stats**as the name of the new worksheet the macro created.

- Open the
- 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:- Insert a
**Button (Form Control)**button in cell I1 to the left of the Print Stats button. - Assign the
**AddRow**macro to the new button. - Change the height of the new button to
**0.3"**and the width to**1"**. - 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.
- Edit the text to display
**Add Project**on the new button.

- Insert a
- 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:
- In the range C4:C37, create a data validation rule that accepts only
**List**values from the range**I4:I7**. - Create an input message that uses
**Project Category**as the title and the following text as the input message:

**Enter a project category.** - 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.**

- In the range C4:C37, create a data validation rule that accepts only
- 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:
- In the range C4:C37, circle invalid data.
- Change the invalid entry to
**Site development**to correct the error.

- 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:
- 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**. - Fill the range J5:J7 with the formula in cell J4, filling without formatting.
- 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]**). - Fill the range K5:K7 with the formula in cell K4, filling without formatting.
- 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]**). - Fill the range L5:L7 with the formula in cell L4, filling without formatting.

- In cell J4, enter a formula using the
- 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**. - 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**. - 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. - Reduce the clutter in the PivotTable by modifying it as follows:
- Change the report layout to show the PivotTable in
**Outline****Form**. - Group the Project Start values by
**Months**.

- Change the report layout to show the PivotTable in
- 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:- Insert a Timeline Slicer based on the
**Project Start**field. - Move and resize the Timeline Slicer so it covers the range A34:F40.
- Adjust the Timeline Slicer to view all months.

- Insert a Timeline Slicer based on the
- 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:- 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. - 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. - 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. - 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. - 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.

- In cell J18, insert a formula using the
- 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. - 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:
- Insert a calculated field named
**Amt Owed**that subtracts the Paid to Date field amount from the Estimate field amount. - 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. - Change the number format of the Sum of % Difference amounts to
**Percentage**with**2**decimal points. - 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.

- Insert a calculated field named
- Hwan needs to identify the location of each project. Add a slicer to the PivotTable as follows:
- Add a slicer based on the State field.
- Move and resize the slicer so it covers the range G3:I11.
- Use the slicer to filter the PivotTable to show only projects in Nebraska (NE).

- 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. - Hwan asks for another way to compare the total estimates for each project category and state. Create a PivotChart as follows:
- Based on the PivotTable on the
*Projects by Category*worksheet, create a**100% Stacked Column**PivotChart. - Move and resize the PivotChart so that its upper-left corner is in cell A11 and its lower-right corner is in cell G25.

- Based on the PivotTable on the
- 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:- Create a new query that imports data from the
**Support_EX19_CS9-12a_Sales.csv**text file. - Edit the query to remove the Projects and Notes columns.
- Close and load the query data to a table in cell A3 of the existing worksheet.
- Change the table style to
**Green, Table Style Medium 2**to coordinate with the rest of the workbook. - Format the values of the Sales ($mil) column as
**Currency**with**0**decimal places and the**$**symbol.

- Create a new query that imports data from the
- 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:- Create a new query that imports data from the
**Support_EX19_CS9-12a_Employees.accdb**database. - Select the
**Employees**and**Offices**tables for the import. - Only create a connection to the data and add the data to the Data Model.
- In cell A3 of the
*Employees*worksheet, use Power Pivot to insert a PivotTable based on the data in the Employees and Offices tables.

- Create a new query that imports data from the
- 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:- In the new PivotTable, display the
**OfficeCity**field from the Offices table as the row headings. - Display the
**Title**field from the Employees table as the first Values field. - Display the
**YearsEmployed**field from the Employees table as the second Values field. - Change the summary function of the Sum of YearsEmployed field to calculate the average number of years.
- Change the number format of the Average of YearsEmployed field to use the Number number format with
**one**decimal place.

- In the new PivotTable, display the
- 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.
- 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:- Insert WordArt using the
**Fill: Green, Accent color 1; Shadow**style. - Type
**Expansion Loan**as the worksheet title. - Change the text fill of the WordArt to
**Black, Text 1, Lighter 25%**. - Change the font size to
**24 point**. - Move the WordArt to row 1 so that it spans columns D:F.

- Insert WordArt using the
- Before performing any calculations, Hwan asks you to correct the errors in the worksheet.
- In cell L16, use the
**Error Checking**command to identify the error in the cell. - Correct the error to total the values in the range B16:K16.
- Use Trace Precedents arrows to find the source of the #DIV/0! error in cell B19.
- 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. - Fill the range C19:K19 with the formula in cell B19 without formatting to correct the remaining #DIV/0! errors.
- Remove any remaining trace arrows.

- In cell L16, use the
- 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:
- 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. - Use absolute references for the rate, nper, and pv arguments, which are listed in the range C4:C10.
- Use relative references for the start and end arguments.
- Fill the range C16:K16 with the formula in cell B16 to calculate the interest paid in Years 2–10 and the total interest.

- In cell B16, enter a formula using the
- Calculate the cumulative principal payments as follows:
- 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. - Use absolute references for the rate, nper, and pv arguments, which are listed in the range C4:C10.
- Use relative references for the start and end arguments.
- Fill the range C17:K17 with the formula in cell B17 to calculate the principal paid in Years 2–10 and the total principal.

- In cell B17, enter a formula using the
- 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:
- 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. - Use absolute references for the cost, salvage, and life arguments in the SLN formula.
- Fill the range C23:K23 with the formula in cell B23 to calculate the annual and cumulative straight-line depreciation in Years 2–10.

- In cell B23, enter a formula using the
- Calculate the declining balance depreciation amounts for the construction equipment as follows:
- 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. - Use Year 1 (cell
**B28**) as the current period. - Use absolute references only for the cost, salvage, and life arguments in the DB formula.
- Fill the range C29:K29 with the formula in cell B29 to calculate the annual and cumulative declining balance depreciation in Years 2–10.

- In cell B29, enter a formula using the
- 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:
- In cell B34, enter a formula using the
**SYD**function to calculate the depreciation balance for the first year. - Use Year 1 (cell
**B28**) as the current period. - In cell B35, enter a formula using the
**SYD**function to calculate the depreciation balance for the last year. - Use Year 10 (cell
**K28**) as the current period.

- In cell B34, enter a formula using the
- 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:- 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. - Use the target rate of return value (cell
**B17**) as the rate argument. - 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.) - In cell B20, enter a formula that uses the
**IRR**function to calculate the internal rate of return for the investment. - Use the payments for Years 1–10 (range
**B5:B14**) as the returns paid to investors.

- In cell B18, enter a formula that uses the
- 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:- Add a
**Trendline**to the Monthly Revenue Projections scatter chart. - Format the trendline to use the
**Logarithmic**option.

- Add a

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.

Purchased 3 times