New Perspectives Excel 365

New Perspectives Excel 365 | Module 9: SAM Project A Zephyr Dynamics

PERFORM FINANCIAL CALCULATIONS

October 14, 2026 · Excel Projects Help

GETTING STARTED

1. Save the file NP_EX365_9A_FirstLastName_1.xlsx as NP_EX365_9A_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. With the file NP_EX365_9A_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Keiko Li is a financial analyst for Zephyr Dynamics, an alternative energy company in Houston, Texas. She is using an Excel workbook to analyze the financial data for a proposed product line of home wind turbines. She asks for your help in correcting errors and making financial calculations for the new product line. Go to the Loan Schedule worksheet. The company needs a loan to fund manufacturing equipment for the home wind turbines. Before calculating the principal and interest payments on the loan, Keiko asks you to correct the errors in the worksheet. Correct the first error as follows:

a. In cell H12, use the Error Checking command to identify the error in the cell.

b. Correct the error to total the values in the range C12:G12.

2. Correct the #VALUE! errors in the worksheet as follows:

a. Use Trace Precedents arrows to find the source of the #VALUE! error in cell C15.

b. Correct the formula in cell C15, which should divide the remaining principal (cell C14) by the loan amount (cell B6) to find the percentage of remaining principal.

c. Fill the range D15:G15 with the formula in cell C15 to correct the remaining #VALUE! errors.

d. Remove any remaining trace arrows.

3. Keiko asks you to determine the quarterly payment based on the loan details in the range B6:G6. Calculate the quarterly payment as follows:

a. In cell H6, enter a formula using the PMT function using the rate (cell E6), the nper (cell G6), and the pv (cell B6) provided in the Loan Details section.

b. Insert a negative sign before the PMT function to display a positive result.

4. Keiko also asks you to calculate the annual principal and interest payments for the manufacturing equipment and then complete the amortization schedule. Start by calculating the cumulative interest payments as follows:

a. In cell C12, enter a formula using the CUMIPMT function to calculate the cumulative interest paid on the loan for Year 1 (payment 1 in cell C10 through payment 4 in cell C11). Use 0 as the type argument in your formula because 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 range B6:G6.

c. Use relative references for the start and end arguments.

d. Fill the range D12:G12 with the formula in cell C12 to calculate the interest paid in Years 2–5 and the total interest.

5. Calculate the cumulative principal payments as follows:

a. In cell C13, enter a formula using the CUMPRINC function to calculate the cumulative principal paid for Year 1 (payment 1 in cell C10 through payment 4 in cell C11). Use 0 as the type argument in your formula because 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 range B6:G6.

c. Use relative references for the start and end arguments.

d. Use a negative sign before CUMPRINC to display the values as positive.

e. Fill the range D13:G13 with the formula in cell C13 to calculate the principal paid in Years 2–5 and the total principal.

6. Complete the amortization schedule as follows:

a. In cell E19, enter a formula using the IPMT function to calculate the interest payment for the first period (Period 1 in cell C19).

b. Use absolute references for the rate, nper, and pv arguments, which are listed in the range B6:G6. Use a relative reference to the per argument.

c. In cell F19, enter a formula using the PPMT function to calculate the principal payment for the first period (Period 1 in cell C19).

d. Use absolute references for the rate, nper, and pv arguments, which are listed in the range B6:G6. Use a relative reference to the per argument.

e. Fill the range E20:F38 with the formulas in the range E19:F19, filling without formatting, to calculate the interest and principal payments paid in Periods 2–20.

7. Go to the Depreciation worksheet. Keiko asks you to correct the errors on this worksheet before performing the depreciation calculations. Correct the errors as follows:

a. Use Trace Dependents arrows to determine whether the #VALUE! error in cell D12 is causing the other errors in the worksheet.

b. Use Trace Precedents arrows to find the source of the error in cell D12.

c. Correct the error so that the formula in cell D12 calculates the cumulative straight-line depreciation of the manufacturing equipment by adding the Cumulative depreciation value in Year 1 to the Annual depreciation value in Year 2.

8. Keiko wants you 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 D5:D7, she estimates that the new product line will have $434,000 in tangible assets at startup, and that the useful life of these assets is 10 years with a salvage value of $108,500. Start by calculating the straight-line depreciation amounts as follows:

a. In cell C11, enter a formula using the SLN function to calculate the straight-line depreciation for the manufacturing equipment during its first year of operation.

b. Use absolute references for the cost, salvage, and life arguments in the SLN formula.

c. Fill the range D11:G11 with the formula in cell C11 to calculate the annual and cumulative straight-line depreciation in Years 2–5.

9. Calculate the declining balance depreciation amounts for the manufacturing equipment as follows:

a. In cell C18, enter a formula using the DB function to calculate the declining balance depreciation for the manufacturing equipment during its first year of operation.

b. Use Year 1 (cell C17) as the current period.

c. Use absolute references only for the cost, salvage, and life arguments in the DB formula.

d. Fill the range D18:G18 with the formula in cell C18 to calculate the annual and cumulative declining balance depreciation in Years 2–5.

10. Keiko also wants you to determine the depreciation balance for the first year and the last year of the useful life of the manufacturing equipment. Determine these amounts as follows:

a. In cell E22, enter a formula using the SYD function to calculate the depreciation balance for the first year.

b. Use Year 1 (cell C17) as the current period.

c. In cell E23, enter a formula using the SYD function to calculate the depreciation balance for the last year.

d. Use Year 5 (cell G17) as the current period.

11. Go to the Earnings Projections worksheet. Keiko has entered most of the income and expense data on the worksheet. She estimates the income from sales of the home wind turbines will be $1,125,000 in 2029 and $2,250,000 in 2033. She asks you to calculate the income from sales in the years 2030–2032. The sales values should increase at a constant amount from year to year. Project the income from Sales for 2030–2032 (cells D5:F5) using a Linear Trend interpolation.

12. Keiko also wants you to calculate the income from investments in the years 2030–2032. She knows the starting amount and has estimated the amount in 2033. She thinks this income will increase by a constant percentage. Project the income from Investments for 2030–2032 (cells D7:F7) using a Growth Trend interpolation.

13. Keiko asks you to calculate the payroll expenses in the years 2030–2033. She knows the payroll will be $450,000 in 2029 and will increase by at least 10 percent per year. Project the payroll expenses as follows:

a. Project the expenses for Payroll for 2030-2033 (cells D14:G14) using a Growth Trend extrapolation.

b. Use 1.1 (a 10 percent increase) as the step value.

14. The Projected Revenue line chart in the range H4:O18 shows the revenue Keiko estimates in the years 2029–2033. She wants to extend the projection into 2034. Modify the Projected Revenue line chart as follows to forecast the future trend:

a. Add a Linear Trendline to the Projected Revenue line chart.

b. Format the trendline to forecast 1 period forward.

15. The Sales Trend scatter chart in the range A20:G39 is based on monthly sales estimates listed on the Monthly Sales Projections worksheet. Keiko asks you to include a trendline for this chart that shows how sales increase quickly at first and then level off in later months. Modify the Sales Trend scatter chart as follows to include a logarithmic trendline:

a. Add a Trendline to the Sales Trend scatter chart.

b. Format the trendline to use the Logarithmic option.

16. Go to the Investment worksheet. This worksheet should show the returns potential investors could realize if they invested $365,000 in the home wind turbines. Keiko figures a desirable rate of return would be 10 percent. She estimates the investment would pay different amounts each year (range C7:C12) and wants you to calculate the present value of the investment. Calculate the present value of the investment as follows:

a. In cell C15, enter a formula that uses the NPV function to calculate the present value of the investment in the home wind turbine product line.

b. Use the desired rate of return value (cell C14) as the rate argument.

c. Use the payments in Years 1–6 (range C7:C12) as the returns paid to investors.

17. Keiko also wants you to calculate the internal rate of return on the investment. If it is 10 percent or higher, she is confident she can attract investors. Calculate the internal rate of return on the investment as follows:

a. In cell C17, enter a formula that uses the IRR function to calculate the internal rate of return for investing in the home wind turbines.

b. Use the payments for startup and Years 1–6 (range C6:C12) as the returns paid to investors.

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 website to submit your completed project.

Final Figure 1: Loan Schedule Worksheet

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 2: Depreciation Worksheet

Final Figure 3: Earnings Projections Worksheet

Final Figure 4: Monthly Sales Projections Worksheet

Final Figure 5: Investment Worksheet

Need help with this project?