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

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.

Unprotect a worksheet.

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:
a. 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.
b. Save, close, and run the CopyStats macro, and use Project Stats as the name of the new worksheet the macro created.

Edit and run a VBA macro.

Rename a new worksheet.

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:
a. 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.
b. 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.

Insert and position a macro button.

Edit the text in a macro button.

Assign a macro to a button.

Resize a button.

Align a macro 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.
a. 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.
b. Create an input message that uses Project Category as the title and the following text as the input message:
Enter a project category.
c. 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.

Add a data validation rule to a range.

Add an input message to a data validation rule.

Add an error alert to a data validation rule.

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.

Edit a value in a cell.

Hwan wants to summarize project information in column I. Calculate the number of projects, the total estimates, and the average estimate in each category.
a. 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.
b. 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.
c. 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.
d. Use these formulas to fill the data for the remaining categories, filling without formatting.

Create a formula using a function.

Copy a formula into a range.

Create a formula using a function.

Copy a formula into a range.

Create a formula using a function.

Copy a formula into a range.

Hwan needs to identify the number of projects that have an estimate of more than $50,000. Next to the cell labeled "Projects:", use a database function to count the number of projects with estimates of more than $50,000, using the data in the entire Projects table and counting the values in the column of estimates that are equal to the values in the range I9:I10.

Create a formula using a function.

Hwan also needs to calculate the total amount paid by client W-0338 for engineering projects. Next to the cell labeled "Total paid:", use a database function to calculate the total paid by client W-0338, using the data in the entire Projects table and totaling the values in the Paid to Date column that are equal to the values in the range I13:I14.

Create a formula using a function.

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.

Refresh Pivot Table data.

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.

Change the layout of a PivotTable.

Group row fields in a PivotTable.

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.

Add a slicer to a PivotTable.

Reposition and resize a slicer.

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.

Create a formula using a function.

Create a formula using a function.

Create a formula using a function.

Create a formula using a function.

Create a formula using a function.

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.

Add a field to the Rows area of a 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.
a. Insert a calculated field named Amt Owed that subtracts the Paid to Date field amount from the Estimate field amount.
b. 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.
c. Change the number format of the Sum of % Difference amounts to Percentage with 2 decimal points.
d. 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.

Add a calculated field to a PivotTable.

Add a calculated field to a PivotTable.

Modify the number format of a PivotTable field.

Enter text in a cell.

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).

Add a slicer to a PivotTable.

Reposition and resize a slicer.

Filter a PivotTable using a slicer.

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.

Add fields as columns in a PivotTable.

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.

Create a PivotChart.

Resize and reposition a PivotChart.

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.
a. Import data from the Support_EX19_CT_CS9-12c_Mac_Sales.csv text file. Start loading the data at row 1, and separate the columns using comma delimiters. Skip only the Projects and Notes columns, and load the data to cell A3 of the existing worksheet.
b. Format the imported data as a table with headers using the Table Style Medium 2 table style (Hint: Depending on your version of Office, the table style may be written as Green, Table Style Medium 2.)
c. Format the values of the Sales ($mil) column as Currency with 0 decimal places and the $ symbol.

Import data from a text file.

Format imported data from an external file.

Format imported data from an external file.

Format a range as a table.

Change the number format.

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 another text file.
a. Import data from the Support_EX19_CT_CS9-12c_Mac_Employees.txt file to a new worksheet. Start loading the data at row 1, and separate the columns using tab delimiters. Use Employee Data as the name of the new worksheet.
b. Format the imported data as a table with headers using the Table Style Medium 2 table style. (Hint: Depending on your version of Office, the table style may be written as Green, Table Style Medium 2.)
c. Rename the table using Employees as the name.
d. Go back to the Employees worksheet. Insert a PivotTable in cell A3 based on the data in the Employees table.

Import data to a new worksheet.

Format imported data from an external file.

Format a range as a table.

Apply a table style.

Set the name of a table.

Create a PivotTable.

Add fields as rows in a PivotTable.

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.
a. 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.
b. 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.

Add and summarize values in a PivotTable.

Modify the number format of a PivotTable field.

Insert WordArt.

Enter text in a WordArt shape.

Format WordArt.

Reposition WordArt.

Reposition WordArt.

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:
a. Insert WordArt using the Fill: Green, Accent color 1; Shadow style, and type Expansion Loan as the worksheet title.
b. 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.
c. Move the WordArt to row 1 so that it spans columns D:F.

Edit a formula using a function.

Edit a formula using a function.

Copy a formula into a range.

Copy a formula into a range.

Create a formula using a function.

Copy a formula into a range.

Before performing any calculations, Hwan asks you to correct the errors in the worksheet.
a. 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.
b. 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.
c. Remove any remaining trace arrows.

Copy a formula into a range.

Create a formula using a function.

Copy a formula into a range.

Copy a formula into a range.

Now Hwan is ready to calculate the annual principal and interest payments for the expansion loan. Start by calculating the cumulative interest payments.
a. 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.
b. 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.
c. Use the formula to calculate the interest paid in Years 2–10, as well as the total interest.

Create a formula using a function.

Copy a formula into a range.

Copy a formula into a range.

Next, calculate the cumulative principal payments.
a. 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.
b. Without adjusting the formula, fill the row to calculate the principal paid in Years 2–10, as well as the total principal.

Create a formula using a function.

Copy a formula into a range.

Copy a formula into a range.

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.

Create a formula using a function.

Create a formula using a function.

Create a formula using a function.

Next, calculate the declining balance depreciation amounts for the construction equipment.
a. 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.
b. Without adjusting the formula, fill the row to calculate the annual declining balance depreciation in Years 2–10.

Create a formula using a function.

Create a formula using a function.

Create a formula using a function.

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:
a. 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.
b. 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.

Add a trendline to a chart.

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.
a. 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.)
b. 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:
a. Add a Trendline to the Monthly Revenue Projections scatter chart.
b. Format the trendline to use the Logarithmic option.

Option 1

Low Cost Option

Download this past answer in few clicks

24.99 USD

PURCHASE SOLUTION

Option 2

Custom new solution created by our subject matter experts

GET A QUOTE

rated 5 stars

Purchased 4 times

Completion Status 100%

New Perspectives Excel 2019 | Modules 9-12: SAM Critical Thinking Capstone Project 1c Hwan Sung is a financial analyst at W&K Engineering in Bismarck, North Dakota

Option 1

Low Cost Option

Download this past answer in few clicks

24.99 USD

PURCHASE SOLUTION

Option 2

Custom new solution created by our subject matter experts

GET A QUOTE

rated 5 stars

View Answer

Sitejabber (5.0)

Merchant Circle (4.8)

Trustpilot (4.6)

Study Help Me (4.9)

Related Questions

Address

Phone Number

Email Address

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.
	Unprotect a worksheet.
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: a. 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. b. Save, close, and run the CopyStats macro, and use Project Stats as the name of the new worksheet the macro created.
	Edit and run a VBA macro.
	Rename a new worksheet.
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: a. 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. b. 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.
	Insert and position a macro button.
	Edit the text in a macro button.
	Assign a macro to a button.
	Resize a button.
	Align a macro 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. a. 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. b. Create an input message that uses Project Category as the title and the following text as the input message: Enter a project category. c. 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.
	Add a data validation rule to a range.
	Add an input message to a data validation rule.
	Add an error alert to a data validation rule.
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.
	Edit a value in a cell.
Hwan wants to summarize project information in column I. Calculate the number of projects, the total estimates, and the average estimate in each category. a. 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. b. 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. c. 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. d. Use these formulas to fill the data for the remaining categories, filling without formatting.
	Create a formula using a function.
	Copy a formula into a range.
	Create a formula using a function.
	Copy a formula into a range.
	Create a formula using a function.
	Copy a formula into a range.
Hwan needs to identify the number of projects that have an estimate of more than $50,000. Next to the cell labeled "Projects:", use a database function to count the number of projects with estimates of more than $50,000, using the data in the entire Projects table and counting the values in the column of estimates that are equal to the values in the range I9:I10.
	Create a formula using a function.
Hwan also needs to calculate the total amount paid by client W-0338 for engineering projects. Next to the cell labeled "Total paid:", use a database function to calculate the total paid by client W-0338, using the data in the entire Projects table and totaling the values in the Paid to Date column that are equal to the values in the range I13:I14.
	Create a formula using a function.
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.
	Refresh Pivot Table data.
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.
	Change the layout of a PivotTable.
	Group row fields in a PivotTable.
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.
	Add a slicer to a PivotTable.
	Reposition and resize a slicer.
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.
	Create a formula using a function.
	Create a formula using a function.
	Create a formula using a function.
	Create a formula using a function.
	Create a formula using a function.
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.
	Add a field to the Rows area of a 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. a. Insert a calculated field named Amt Owed that subtracts the Paid to Date field amount from the Estimate field amount. b. 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. c. Change the number format of the Sum of % Difference amounts to Percentage with 2 decimal points. d. 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.
	Add a calculated field to a PivotTable.
	Add a calculated field to a PivotTable.
	Modify the number format of a PivotTable field.
	Enter text in a cell.
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).
	Add a slicer to a PivotTable.
	Reposition and resize a slicer.
	Filter a PivotTable using a slicer.
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.
	Add fields as columns in a PivotTable.
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.
	Create a PivotChart.
	Resize and reposition a PivotChart.
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. a. Import data from the Support_EX19_CT_CS9-12c_Mac_Sales.csv text file. Start loading the data at row 1, and separate the columns using comma delimiters. Skip only the Projects and Notes columns, and load the data to cell A3 of the existing worksheet. b. Format the imported data as a table with headers using the Table Style Medium 2 table style (Hint: Depending on your version of Office, the table style may be written as Green, Table Style Medium 2.) c. Format the values of the Sales ($mil) column as Currency with 0 decimal places and the $ symbol.
	Import data from a text file.
	Format imported data from an external file.
	Format imported data from an external file.
	Format a range as a table.
	Change the number format.
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 another text file. a. Import data from the Support_EX19_CT_CS9-12c_Mac_Employees.txt file to a new worksheet. Start loading the data at row 1, and separate the columns using tab delimiters. Use Employee Data as the name of the new worksheet. b. Format the imported data as a table with headers using the Table Style Medium 2 table style. (Hint: Depending on your version of Office, the table style may be written as Green, Table Style Medium 2.) c. Rename the table using Employees as the name. d. Go back to the Employees worksheet. Insert a PivotTable in cell A3 based on the data in the Employees table.
	Import data to a new worksheet.
	Format imported data from an external file.
	Format a range as a table.
	Apply a table style.
	Set the name of a table.
	Create a PivotTable.
	Add fields as rows in a PivotTable.
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. a. 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. b. 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.		Add and summarize values in a PivotTable.
	Modify the number format of a PivotTable field.
	Insert WordArt.
	Enter text in a WordArt shape.
	Format WordArt.
	Reposition WordArt.
	Reposition WordArt.
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: a. Insert WordArt using the Fill: Green, Accent color 1; Shadow style, and type Expansion Loan as the worksheet title. b. 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. c. Move the WordArt to row 1 so that it spans columns D:F.		Edit a formula using a function.
	Edit a formula using a function.
	Copy a formula into a range.
	Copy a formula into a range.
	Create a formula using a function.
	Copy a formula into a range.
Before performing any calculations, Hwan asks you to correct the errors in the worksheet. a. 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. b. 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. c. Remove any remaining trace arrows.		Copy a formula into a range.
	Create a formula using a function.
	Copy a formula into a range.
	Copy a formula into a range.
Now Hwan is ready to calculate the annual principal and interest payments for the expansion loan. Start by calculating the cumulative interest payments. a. 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. b. 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. c. Use the formula to calculate the interest paid in Years 2–10, as well as the total interest.		Create a formula using a function.
	Copy a formula into a range.
	Copy a formula into a range.
Next, calculate the cumulative principal payments. a. 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. b. Without adjusting the formula, fill the row to calculate the principal paid in Years 2–10, as well as the total principal.		Create a formula using a function.
	Copy a formula into a range.
	Copy a formula into a range.
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.		Create a formula using a function.
	Create a formula using a function.
	Create a formula using a function.
Next, calculate the declining balance depreciation amounts for the construction equipment. a. 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. b. Without adjusting the formula, fill the row to calculate the annual declining balance depreciation in Years 2–10.		Create a formula using a function.
	Create a formula using a function.
	Create a formula using a function.
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: a. 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. b. 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.		Add a trendline to a chart.


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. a. 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.) b. 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: a. Add a Trendline to the Monthly Revenue Projections scatter chart. b. Format the trendline to use the Logarithmic option.