New Perspectives Excel 2019 | Modules 9-12: SAM Critical Thinking Capstone Project 1c

W&K Engineering

ANALYZE DATA AND WORK WITH FINANCIAL TOOLS

GETTING STARTED

Open the fileNP_EX19_CT_CS9-12c_FirstLastName_1.xlsm, available for download from the SAM website.
Files downloaded from the SAM website are safe and do not contain viruses, but due to a recent Microsoft policy update, macros in downloaded files are disabled by default. To complete this project, you will need to enable macros in the file. To enable macros on this file:

For PC: Open Windows File Explorer and go to the folder where you saved the file. Right-click the file and choose Properties from the context menu. At the bottom of the General tab, select the Unblock checkbox and select Apply, and then click OK.

For Mac: If a dialog box about macros appears, click Enable Macros.

Save the file as NP_EX19_CT_CS9-12c_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_CT_CS9-12c_Employees.accdb

Support_EX19_CT_CS9-12c_Sales.csv

With the file NP_EX19_CT_CS9-12c_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.

When downloading Access support files, your browser may display a warning message similar to “This type of file can harm your computer.” SAM Project files are always considered safe, so you can disregard this message.
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.
The engineering projects table should show projects from multiple states. Clear the filter from the table.
Instead of seeing the data sorted by estimate, Hwan wants to see the table with the oldest projects first. Sort the table.
Hwan created a macro named CopyStats to copy the project statistics in columns I to L, 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. In the CopyStats macro VBA code, correct the range specified in the first line of code after the comments.
2. Save, close, and run the CopyStats macro, and use Project Stats as the name of the new worksheet the macro created.
Return to the Projects worksheet. Hwan also created a macro named AddRow that inserts a new blank row in the Projects table. 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 the cell to the left of the Print Stats button. Edit the text to display Add Project on the new button, and assign the AddRow macro to the new button.
2. Change the height of the new button to 0.3″ and the width to 1″. Align the new button to the left side of the cell, and then top align it with the Print Stats button.
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.
1. Select the data in the Categories column, and create a data validation rule that accepts only List values from the categories in column I. Use a range reference instead of writing the categories themselves.
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.
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.
Circle invalid data in the Categories column, and change the invalid entry to Site development to correct the error.
Hwan wants to summarize project information in column I. Calculate the number of projects, the total estimates, and the average estimate in each category.
1. Next to the cell labeled “Bridge”, use a function to count the number of Bridge projects in the Projects table. Use a cell reference to the cell labeled Bridge.
2. In the appropriate cell, use a function to total the estimates for Bridge projects in the Projects table. Use a cell reference to the cell labeled Bridge.
3. In the appropriate cell, use a function to average the estimates for Bridge projects in the Projects table. Use a cell reference to the cell labeled Bridge.
4. Use these formulas to fill the data for the remaining categories, filling without formatting.
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.
Change the report layout to show the PivotTable in Outline Form. Group the Project Start values by Months.
Hwan occasionally would like to focus on the project data according to the start date.
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.
Return to the Projects worksheet. In the section labeled “Projects by State”, Hwan wants to display the grand totals of the project estimate per state. He can retrieve this information from the PivotTable on the Projects by State worksheet. Display the estimates for each states projects.
In the appropriate cell, retrieve the grand total estimate amount for projects in Montana from the PivotTable on the Projects by State worksheet. Retrieve the data for the remaining states to complete the section.
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.
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 from Sum of Amt Owed to Amount Owed and change the column heading from Sum of % Difference to % Owed to better identify the column headings.
Hwan needs to identify the location of each project.
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.
Based on the PivotTable on the Projects by Category worksheet, create a 100% Stacked Column PivotChart. Move and resize the PivotChart so that it covers the range A11:G25.
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_CT_CS9-12c_Sales.csv text file. Edit the query to remove the Projects and Notes columns, and close and load the query data to a table in cell A3 of the Monthly Sales worksheet.
2. Change the table style to Green, Table Style Medium 2 to coordinate with the rest of the workbook.
3. Format the values of the Sales ($mil) column as Currency with 0 decimal places and the $ symbol.
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_CT_CS9-12c_Employees.accdb database. Import the Employees and Offices tables, and only create a connection to the data and add the data to the Data Model.
2. In cell A3 of the Employees worksheet, use Power Pivot to insert a PivotTable based on the data in the Employees and Offices tables.
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 to provide this information.
1. In the new PivotTable, display the OfficeCity field from the Offices table as the row headings, the Title field from the Employees table as the first Values field, and the YearsEmployed field from the Employees table as the second Values field.
2. Change the summary function of the Sum of YearsEmployed field to calculate the average number of years, and then change the number format to use the Number number format with one decimal place.
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:
1. Insert WordArt using the Fill: Green, Accent color 1; Shadow style, and type Expansion Loan as the worksheet title.
2. Change the text fill of the WordArt to Black, Text 1, Lighter 25% (2nd column, 4th row in the Theme Colors palette), and the font size to 24 point.
3. Move the WordArt to row 1 so that it spans columns D:F.
Before performing any calculations, Hwan asks you to correct the errors in the worksheet.
1. To the right of the Annual Principal and Cumulative Interest Payments section, use the Error Checking command to identify the error in the cell. Correct the error to total the interest values.
2. Use Trace Precedents arrows to find the source of the #DIV/0! error in the Remaining % for year 1. Correct the formula, which should divide the remaining principal by the loan amount to find the percentage of remaining principal. Fill the rest of the row with the formula you just created without formatting to correct the remaining #DIV/0! errors.
3. Remove any remaining trace arrows.
Now Hwan is ready to calculate the annual principal and interest payments for the expansion loan. Start by calculating the cumulative interest payments.
1. For the Year 1 payment, use a function to calculate the cumulative interest paid on the loan from month 1 through month 12. Use cell references to refer to the starting month and the ending month, and assume that 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 loan conditions section. Use relative references for the start and end arguments.
3. Use the formula to calculate the interest paid in Years 2–10, as well as the total interest.
Next, calculate the cumulative principal payments.
1. For the Year 1 principal, use a function to calculate the cumulative principal paid from month 1 through month 12. Use cell references to refer to the arguments in the loan conditions section, as well as the starting month and the ending month, and assume that payments are made at the end of the period.
2. Without adjusting the formula, fill the row to calculate the principal paid in Years 2–10, as well as the total principal.
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 column G, 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.
For the annual depreciation for Year 1, use a function to calculate the straight-line depreciation for the construction equipment during the first year of operation. Without adjusting the formula, fill the row to calculate the annual straight-line depreciation in Years 2–10.
Next, calculate the declining balance depreciation amounts for the construction equipment.
1. For the annual depreciation for Year 1, use a function to calculate the declining balance depreciation for the construction equipment during the first year of operation. Use a cell reference to Year 1 as the current period.
2. Without adjusting the formula, fill the row to calculate the annual declining balance depreciation in Years 2–10.
Hwan also wants to determine the sum-of-years depreciation balance for the first year and the last year of the useful life of the construction equipment. Determine these amounts as follows:
1. For the yearly allowance for the first year, use a function to calculate the sum-of-years depreciation balance, using a cell reference to Year 1 of the declining balance section as the current period.
2. For the yearly allowance for the last year, use a function to calculate the sum-of-years depreciation balance, using a cell reference to Year 10 of the declining balance as the current period.
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, as shown in the repayment schedule section. Calculate the net present value of the investment and the internal rate of return.
1. Next to the cell labeled “Present value”, use a function to calculate the net present value of the investment in funding the company’s expansion. Use a cell reference to the target rate of return value as the rate argument, as well as the payments in Years 1–10 as the returns paid to investors. (Hint: If a Formula Omits Adjacent Cell error warning appears, ignore it.)
2. Next, in the appropriate cell, use a function to calculate the internal rate of return for the investment. Use a cell reference to the payments for Years 1–10 as the returns paid to investors.
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.