Author: Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. New Perspectives Excel 2019 | Module 9: End of Module Project 1 Canyon Transport PERFORM FINANCIAL CALCULATIONS Zunair Waseem Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. f the file from Airport Shuttles Business Plan Annual interest rate Expenses Buses and vans 320,000 Shuttle stop construction 150,000 Miscellaneous expenses 20,000 Total expenses 5.60% Payment amount Future value Payments Loan amount $ 490,000 (pmt) Business loan (pv) $ Future value (fv) $ Years Payments / year Assets Cash 35,000 Non-cash assets 112,000 Miscellaneous assets 80,000 Long-term assets 115,000 Total assets $ 342,000 Total expenses and assets $ 832,000 $ 490,000 490,000 $ 210,000 25,000 235,000 New division investment New division expenses Initial equity Total liabilities and equity $ 235,000 (490,000) (255,000) 235,000 TOTAL FUNDING $ 725,000 Total payments (nper) 490,000 (fv) (nper) $ 490,000 $ 490,000 - $ 10 10 4 4 40 40 (pv) - $ - 10 4 4 40 Annual rate 5.60% 5.60% 5.60% Rate / quarter (rate) 1.40% 1.40% 1.40% Quarterly payments (pmt) - $ (15,000) $ (15,000) $ 5.60% 1.40% (15,000) Liabilities Long-term loan Outstanding debts Total liabilities Capital Owners Small business grant Total available capital Summary 20210412214810np_ex19_eom9_1_zunairwaseem_1 Business Plan 4/12/2021 Airport Shuttles Loan Details Loan (pv) $490,000 Annual rate Payments per year 5.60% 4 Rate per period (rate) 1.40% Years Payments (nper) 10 40 Payment (pmt) ($16,082) Cumulative Interest and Principal Payments per Year Year 1 Year 2 Year 3 Year 4 Year 5 1 5 9 13 17 4 8 12 16 20 Quarters Principal paid Interest paid Principal remaining Total $0 $0 $490,000 $490,000 $490,000 $490,000 $490,000 20210412214810np_ex19_eom9_1_zunairwaseem_1 Loan Details 4/12/2021 Airport Shuttles Buy or Lease Scenarios Shuttle Bus Current price (cost) $ 40,000 Salvage value (salvage) Salvage months (life) $ 18,000 120 Months Initial Investment Buy Scenario Service contract Sales tax rate Tax on sale Resale percent $ $ 825 3.30% 1,320 75.0% Lease Scenario Security deposit Monthly payment $ $ 4,000.00 700 Buy or Lease Annual discount rate Monthly discount rate Buy: Net present value Lease: Net present value Recommendation 7.00% 0.58% LEASE End of Contract $ Asset Value 40,000 $ Buy Scenario Lease Scenario (42,145) $ (4,000) 1 2 3 4 - (700) (700) (700) (700) 5 6 7 8 9 10 - (700) (700) (700) (700) (700) (700) 11 12 - (700) (700) 13 14 - (700) (700) 15 16 17 18 19 20 21 22 23 24 - (700) (700) (700) (700) (700) (700) (700) (700) (700) (700) 25 26 27 28 29 30 31 32 33 34 35 36 - (700) (700) (700) (700) (700) (700) (700) (700) (700) (700) (700) (700) 4,000 $ $ 20210412214810np_ex19_eom9_1_zunairwaseem_1 Buy or Lease 4/12/2021 Airport Shuttles Depreciation Long-term assets (cost) Salvage value (salvage) Life of asset (life) $ $ Straight-Line Yearly depreciation Cumulative depreciation Depreciated asset value 1 $ Declining Balance Yearly depreciation Cumulative depreciation Depreciated asset value 115,000 15,000 10 115,000 $ 1 $ 115,000 Year 3 2 115,000 $ 115,000 $ Year 3 2 $ 115,000 4 $ 115,000 115,000 5 $ 4 $ 115,000 115,000 5 $ 115,000 20210412214810np_ex19_eom9_1_zunairwaseem_1 Depreciation 4/12/2021 Profit & Loss Statement Percent cost of marketing Percent cost of R&D Tax rate 25% 7.25% 33% Income Revenue Cost of marketing Cost of R&D Gross profit $ Expenses Payroll Rent Insurance Miscellaneous Total expenses $ Earnings Initial earnings Depreciation Operating profit $ Interest expense Pretax profit Tax liability After-tax profit $ Year 1 825,000 206,250 59,813 558,938 Year 2 Year 3 Year 4 Year 1 588,000 60,000 30,000 15,000 693,000 Year 2 30,000 15,000 45,000 32,000 15,000 47,000 32,000 15,000 47,000 Year 1 (134,063) $ (134,063) Year 2 (45,000) $ (45,000) Year 3 (47,000) $ (47,000) Year 4 (47,000) $ (47,000) (134,063) (45,000) (47,000) (47,000) 835,500 (134,063) $ (45,000) $ (47,000) $ (47,000) $ 275,715 559,785 $ - Year 3 Year 4 Year 5 1,400,000 350,000 101,500 948,500 Year 5 64,000 34,000 15,000 113,000 Year 5 835,500 835,500 20210412214810np_ex19_eom9_1_zunairwaseem_1 Profit & Loss 4/12/2021 Author: Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. New Perspectives Excel 2019 | Module 8: End of Module Project 1 Four Winds Energy PERFORM WHAT-IF ANALYSES Zunair Waseem Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. f the file from Four Winds Energy Boreas Wind Turbine Income Analysis Revenue Units sold Price per unit Total revenue Variable Expenses Units produced Material cost per unit Total material cost Manufacturing cost per unit Total manufacturing cost Total variable expenses Fixed Expenses Salaries and benefits Distribution Miscellaneous Total fixed expenses Summary Total revenue Total expenses Net income Break-Even Anal $ 4.750 $1.139 5.410.250 $ 4.750 $515 2.446.250 $225 1.068.750 3.515.000 Units Sold 4.750 2.750 3.250 3.750 4.250 4.750 5.250 5.750 6.250 6.750 Net In S $ $ 833.500 535.000 310.000 1.678.500 $ 5.410.250 5.193.500 216.750 $216.750 2.750 3.250 3.750 4.250 4.750 5.250 5.750 6.250 6.750 Break-Even Analysis Revenue Expenses Net Income $5.410.250 $5.193.500 $216.750 $939 Net Income Analysis Sales Price $1.039 $1.139 $1.239 $1.329 Four Winds Energy Boreas Wind Turbine Product Mix Products Price per unit Material cost per unit Manufacturing cost per unit Boreas 12X $1.139 $515 $225 Even product mix Optimal product mix Change Boreas 24X $1.339 $650 $300 Boreas 48X $1.539 $725 $350 1.584 1.584 1.582 (1.584) (1.584) (1.582) Revenue even mix Revenue optimal mix Change $ $ 1.804.176 $ 2.120.976 $ 2.434.698 $ $ (1.804.176) (2.120.976) (2.434.698) Summary Units sold Total revenue Total material cost Total manufacturing cost Total fixed expenses Net income Even Mix 4.750 $ 6.359.850 2.992.310 1.385.300 1.678.500 $ 303.740 Optimal Mix $ 1.678.500 $ (1.678.500) Difference % Difference (4.750) (100,00%) (6.359.850) (100,00%) (2.992.310) (100,00%) (1.385.300) (100,00%) 0 0,00% (1.982.240) (652,61%) Assembly Available Internal ladder 4.477 Tower shell 3.352 Blade pitch control 1.759 Drive shaft 2.078 Fiberglass nacelle 3.760 Gear box 1.536 Yaw drive 1.473 Blades 4.505 Utility box 1.863 Solver Results Difference Used Remaining 4.238 239 3.284 68 1.698 61 2.031 47 3.566 194 1.499 37 1.408 65 4.287 218 1.752 111 New Perspectives Excel 2019 | Module 9: End of Module Project 1 Canyon Transport PERFORM FINANCIAL CALCULATIONS GETTING STARTED • Open the file NP_EX19_EOM9-1_FirstLastName_1.xlsx, available for download from the SAM website. • Save the file as NP_EX19_EOM9-1_FirstLastName_2.xlsx by changing the “1” to a “2”. o • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically. With the file NP_EX19_EOM9-1_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. o If cell B6 does not display your name, delete the file and download a new copy from the SAM website. PROJECT STEPS 1. Elena Gonzalez works in the Operations Department of Canyon Transport, a company providing delivery and shuttle services in Flagstaff, Arizona. Canyon Transport is planning to offer shuttle services to the Phoenix Sky Harbor Airport, and Elena is using an Excel workbook to analyze the financial data for these expanded services. She asks for your help in making financial calculations in the workbook. Go to the Business Plan worksheet. In the range A2:B29, Elena has already entered expenses, assets, and other information for the new airport shuttle service. Now she needs to make financial calculations in the range E4:H11. In cell E11, enter a formula with the PMT function that uses the rate per quarter (cell E10), the total payments (cell E8), and the business loan amount (cell E4) to calculate the quarterly payment amount for a 10-year loan at a 5.6 percent annual interest rate. 2. In cell F5, enter a formula with the FV function that uses the rate per quarter (cell F10), the total payments (cell F8), the quarterly payment amount (cell F11), and the principal value (cell F4) to calculate the future value of the loan assuming the quarterly payments are limited to $15,000. 3. In cell G8, enter a formula with the NPER function that uses the rate per quarter (cell G10), the quarterly payment amount (cell G11), the amount of the business loan (cell G4), and the future value of the loan (cell G5) to calculate the total number of payments required to repay the $490,000 loan with quarterly payments of $15,000. 4. In cell H4, enter a formula with the PV function that uses the rate per quarter (cell H10), the total payments (cell H8), and the quarterly payment amount (cell H11) to calculate the present value of the loan Canyon Transport can afford if the quarterly payments are $15,000 over a 10-year period. 5. Now Elena asks you to calculate the annual principal and interest payments for the airport shuttle service expansion. Go to the Loan Details worksheet. In cell B9, enter a formula using the CUMPRINC function to calculate the cumulative principal paid for Year 1 (payment 1 in cell B7 through payment 4 in cell B8). Use 0 as the type argument in your formula because payments are made at the start of the period. Use absolute references for the rate, nper, and pv arguments, which are listed in the range A3:G3. New Perspectives Excel 2019 | Module 9: End of Module Project 1 Use relative references for the start and end arguments. Fill the range C9:F9 with the formula in cell B9 to calculate the principal paid in Years 2–5 and the total principal. 6. In cell B10, enter a formula using the CUMIPMT function to calculate the cumulative interest paid on the loan for Year 1 (payment 1 in cell B7 through payment 4 in cell B8). Use 0 as the type argument. Use absolute references for the rate, nper, and pv arguments, and use relative references for the start and end arguments. Fill the range C10:F10 with the formula in cell B10 to calculate the interest paid in Years 2–5 and the total interest. 7. Go to the Buy or Lease worksheet. Elena wants to compare the costs of buying a shuttle bus with the costs of leasing one. She has entered information for both scenarios in the range A2:B19 but needs to complete the table in columns D through G to track the depreciation of the shuttle bus value. In cell E4, enter a formula that subtracts the result of the DB function from the initial asset value (cell E3) to calculate the difference between the initial value of the shuttle bus and its depreciation during the first month of use. In the DB function, use absolute references for the cost, salvage, and life arguments, which are listed in the range B3:B5. Use a relative reference for the period argument (cell D4). Fill the range E5:E39 with the formula in cell E4, filling without formatting, to calculate the depreciated value for months 2–36. 8. Elena is ready to calculate the net present value of buying the shuttle bus. If it is greater than the cost of the net present value of leasing, cell B22 displays the recommendation "BUY"; otherwise, cell B22 displays the recommendation "LEASE". Elena has already calculated the initial investment amount (cell F3) as the current price, plus the cost of a service contract, plus the tax on the sale. In cell B20, enter a formula that adds the initial investment amount of buying the shuttle bus (cell F3) to the result of the NPV function. In the NPV function, use the monthly discount rate (cell B19) as the rate of return and the Buy Scenario values for months 1–36 and the ending value (range F4:F40) as the cash flows for owning and using the shuttle bus. 9. Next, calculate the net present value of leasing the shuttle bus. Elena has already entered the security deposit amount (cell G3) as the initial investment for leasing and the monthly payments in the range G4:G39. In cell B21, enter a formula that adds the security deposit amount (cell G3) to the result of the NPV function. In the NPV function, use the monthly discount rate (cell B19) as the rate of return and the Lease Scenario values for months 1–36 and the ending value (range G4:G40) as the cash flows for leasing the shuttle bus. 10. Elena also needs to compare straight-line depreciation amounts with declining balance depreciation amounts to determine which method is more favorable to the company's finances. Go to the Depreciation worksheet. In cell B9, enter a formula using the SLN function to calculate the straight-line depreciation for the new shuttle service during its first year of operation. Use absolute references for the cost, salvage, and life arguments, which are stored in the range B3:B5. Fill the range C9:F9 with the formula in cell B9 to calculate the annual and cumulative straight-line depreciation in Years 2–5. 11. In cell B15, enter a formula using the DB function to calculate the declining balance depreciation for the new shuttle service during its first year of operation. Use Year 1 (cell B14) as the current period. Use absolute references only for the cost, salvage, and life arguments. Fill the range C15:F15 with the formula in cell B15 to calculate the annual and cumulative declining balance depreciation in Years 2–5. 12. Go to the Profit & Loss worksheet. Elena has entered most of the income and expense data on the worksheet. She estimates revenue will be $825,000 in Year 1 and $1,400,000 in Year 5 of the shuttle service. She needs to calculate revenue for Years 2– New Perspectives Excel 2019 | Module 9: End of Module Project 1 4. Revenue should increase at a constant amount from year to year. Project the revenue for Years 2–4 (cells C7:E7) using a Linear Trend interpolation. 13. Elena also needs to calculate expenses for payroll and rent for Years 2–5. She knows the starting amount for each expense, and estimates the rent in Year 5 will be $64,000. She expects the payroll expenses to increase by at least 6 percent per year and the rent to increase by a constant rate. Project the expenses for Payroll in Years 2–5 (cells C13:F13) using a Growth Trend extrapolation. Use 1.06 (a 6 percent increase) as the step value. Project the expenses for Rent in Years 2–4 (cells C14:E14) using a Growth Trend interpolation. 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. New Perspectives Excel 2019 | Module 9: End of Module Project 1 Final Figure 1: Business Plan Worksheet Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2018 Cengage Learning. All Rights Reserved. New Perspectives Excel 2019 | Module 9: End of Module Project 1 Final Figure 2: Loan Details Worksheet New Perspectives Excel 2019 | Module 9: End of Module Project 1 Final Figure 3: Buy or Lease Worksheet New Perspectives Excel 2019 | Module 9: End of Module Project 1 Final Figure 4: Depreciation Worksheet New Perspectives Excel 2019 | Module 9: End of Module Project 1 Final Figure 5: Profit & Loss Worksheet 
Purchase answer to see full attachment