New Perspectives Excel 365 | Module 9: SAM Project B StarPower Transportation

GETTING STARTED

1. Save the file NP_EX365_9B_FirstLastName_1.xlsx as NP_EX365_9B_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_9B_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. Paloma Calvo is a financial analyst for StarPower Transportation, an electric vehicle company in Philadelphia, Pennsylvania. She is using an Excel workbook to analyze the financial data for a proposed line of electric buses. She asks for your help in correcting errors and making financial calculations for the new product line. Go to the Loan Analysis worksheet. The company needs a loan to fund prototypes for the new models of electric buses. Before calculating the principal and interest payments on the loan, Paloma asks you to correct the errors in the worksheet. Correct the first error as follows:

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

b. Correct the error to total the values in the range C13:G13.

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

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

c. Fill the range D16:G16 with the formula in cell C16 to correct the remaining #VALUE! errors.

d. Remove any remaining trace arrows.

3. Paloma asks you to determine the quarterly payment based on the loan details in the range B7:G7. Calculate the quarterly payment as follows:

a. In cell H7, enter a formula using the PMT function using the rate (cell E7), the nper (cell G7), and the pv (cell B7) provided in the Loan Details section.

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

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

a. In cell C13, enter a formula using the CUMIPMT function to calculate the cumulative interest paid on the loan for Year 1 (payment 1 in cell C11 through payment 4 in cell C12). 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 B7:G7.

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

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

5. Calculate the cumulative principal payments as follows:

a. In cell C14, enter a formula using the CUMPRINC function to calculate the cumulative principal paid for Year 1 (payment 1 in cell C11 through payment 4 in cell C12). 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 B7:G7.

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 D14:G14 with the formula in cell C14 to calculate the principal paid in Years 2–5 and the total principal.

6. Complete the amortization schedule as follows:

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

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

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

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

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

7. Go to the Depreciation worksheet. Paloma 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 D13 is causing the other errors in the worksheet.

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

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

8. Paloma 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 D6:D8, she estimates that the new product line will have $1,780,000 in tangible assets at startup, and that the useful life of these assets is 10 years with a salvage value of $445,000. Start by calculating the straight-line depreciation amounts as follows:

a. In cell C12, enter a formula using the SLN function to calculate the straight-line depreciation for the prototypes during their first year of operation.

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

c. Fill the range D12:G12 with the formula in cell C12 to calculate the annual and cumulative straight-line depreciation in Years 2–5.

9. Calculate the declining balance depreciation amounts for the electric bus prototypes as follows:

a. In cell C19, enter a formula using the DB function to calculate the declining balance depreciation for the prototypes during their first year of operation.

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

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

d. Fill the range D19:G19 with the formula in cell C19 to calculate the annual and cumulative declining balance depreciation in Years 2–5.

10. Paloma also wants you to determine the depreciation balance for the first year and the last year of the useful life of the electric bus prototypes. Determine these amounts as follows:

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

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

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

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

11. Go to the Projections worksheet. Paloma has entered most of the income and expense data on the worksheet. She estimates the income from sales of the electric buses will be $4,125,000 the first year and $6,100,000 in the fifth year. She asks you to calculate the income from sales in the years 2–4. The sales values should increase at a constant amount from year to year. Project the income from sales for years 2–4 (cells D6:F6) using a Linear Trend interpolation.

12. Paloma also wants you to calculate the income from investments in the years 1–5. She knows the starting amount and has estimated the amount in year 5. She thinks this income will increase by a constant percentage. Project the income from investments for years 2–4 (cells D8:F8) using a Growth Trend interpolation.

13. Paloma asks you to calculate the payroll expenses for years 2–5. She knows the payroll will be $1,750,000 in year 1 and will increase by at least 10 percent per year. Project the payroll expenses as follows:

a. Project the expenses for payroll for years 2–5 (cells D15:G15) using a Growth Trend extrapolation.

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

14. The Projected Income line chart in the range H5:O19 shows the income Paloma estimates in the years 1–5. She wants to extend the projection into year 6. Modify the Projected Income line chart as follows to forecast the future trend:

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

b. Format the trendline to forecast 1 period forward.

15. The Sales Trend scatter chart in the range A21:G40 is based on monthly sales estimates listed on the Monthly Sales Projections worksheet. Paloma 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 ROI worksheet. This worksheet should show the returns potential investors could realize if they invested $1,460,000 in the electric buses. Paloma figures a desirable rate of return would be 9.5 percent. She estimates the investment would pay different amounts each year (range C8:C13) and wants you to calculate the present value of the investment. Calculate the present value of the investment as follows:

a. In cell C16, enter a formula that uses the NPV function to calculate the present value of the investment in the electric bus product line.

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

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

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

a. In cell C18, enter a formula that uses the IRR function to calculate the internal rate of return for investing in the electric buses.

b. Use the payments for startup and Years 1–6 (range C7:C13) 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 Analysis Worksheet

Final Figure 2: Depreciation Worksheet

Final Figure 3: Projections Worksheet

Final Figure 4: Monthly Sales Projections Worksheet

Final Figure 5: ROI Worksheet