10 Ten the E AL AoBbCcDdEt AaBb AaBbCcDc AaBbCcDdEe BodyCopy AaBbCcDdfe BT incorrectFee... MainProject Normal Styles Dic

[answerhappygod]

10 Ten The E Al Aobbccddet Aabb Aabbccdc Aabbccddee Bodycopy Aabbccddfe Bt Incorrectfee Mainproject Normal Styles Dic 1 (110.79 KiB) Viewed 61 times

10 Ten The E Al Aobbccddet Aabb Aabbccdc Aabbccddee Bodycopy Aabbccddfe Bt Incorrectfee Mainproject Normal Styles Dic 2 (35.06 KiB) Viewed 61 times

10 Ten The E Al Aobbccddet Aabb Aabbccdc Aabbccddee Bodycopy Aabbccddfe Bt Incorrectfee Mainproject Normal Styles Dic 3 (46.16 KiB) Viewed 61 times

10 Ten The E Al Aobbccddet Aabb Aabbccdc Aabbccddee Bodycopy Aabbccddfe Bt Incorrectfee Mainproject Normal Styles Dic 4 (35.33 KiB) Viewed 61 times

10 Ten The E Al Aobbccddet Aabb Aabbccdc Aabbccddee Bodycopy Aabbccddfe Bt Incorrectfee Mainproject Normal Styles Dic 5 (51.88 KiB) Viewed 61 times

10 Ten The E Al Aobbccddet Aabb Aabbccdc Aabbccddee Bodycopy Aabbccddfe Bt Incorrectfee Mainproject Normal Styles Dic 6 (51.88 KiB) Viewed 61 times

10 Ten The E Al Aobbccddet Aabb Aabbccdc Aabbccddee Bodycopy Aabbccddfe Bt Incorrectfee Mainproject Normal Styles Dic 7 (54.56 KiB) Viewed 61 times

10 Ten The E Al Aobbccddet Aabb Aabbccdc Aabbccddee Bodycopy Aabbccddfe Bt Incorrectfee Mainproject Normal Styles Dic 8 (28.5 KiB) Viewed 61 times

10 Ten The E Al Aobbccddet Aabb Aabbccdc Aabbccddee Bodycopy Aabbccddfe Bt Incorrectfee Mainproject Normal Styles Dic 9 (96.47 KiB) Viewed 61 times

10 Ten The E Al Aobbccddet Aabb Aabbccdc Aabbccddee Bodycopy Aabbccddfe Bt Incorrectfee Mainproject Normal Styles Dic 10 (96.47 KiB) Viewed 61 times

10 Ten The E Al Aobbccddet Aabb Aabbccdc Aabbccddee Bodycopy Aabbccddfe Bt Incorrectfee Mainproject Normal Styles Dic 11 (30.02 KiB) Viewed 61 times

10 Ten the E AL AoBbCcDdEt AaBb AaBbCcDc AaBbCcDdEe BodyCopy AaBbCcDdfe BT incorrectFee... MainProject Normal Styles Dictate Pane 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.
CENGAGE 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.
A Airport Shuttles Business Plan Expenses Assets Liabilities Capital Summary CANYON 2 3 Buses and vans 4 Shuttle stop construction 5 Miscellaneous expenses 6 Total expenses 7 8 Cash 9 Non-cash assets 10 Miscellaneous assets 11 Long-term assets 12 Total assets 13 Total expenses and assets 14 15 Long-term loan 16 Outstanding debts 17 Total liabides 18 19 Owners 20 Small business grant 21 Total available capital 22 23 24 New division investment 25 New division expenses 26 Initial equity 27 Total liabilities and equity 20 29 TOTAL FUNDING 30 31 Emer Documentation Business Plan B 320,000 150,000 20,000 490,000 35,000 112,000 80,000 115,000 342,000 832,000 490,000 490,000 210,000 25,000 $ 235,000 235,000 (490,000) (255,000) 235,000 725,000 $ $ $ S $ Loan Details C D Annual interest rate Business loan (pv) Future value (fv) Years Payments/year Total payments (nper) Annual rate Rate / quarter (rate) Quarterly payments (pet) Buy or Lease Depreciation E F H 5.60% Payment amount Future value Payments Loan amount (pmt) (fv) (nper) (pv) $ 490,000 $490,000 $490,000 $ . $ 10 4 10 4 40 5.60% 1.40% Profit & Loss $ 10 4 4 40 40 5.60% 5.60% 5.60% 1.40% 1.40% 1.40% $ (15,000) $ (15,000) $ (15,000)
Airport Shuttles Loan Details Payments Rate per per year period 2 (rate) $490,000 5.60% 5 Cumulative Interest and Principal Payments per Year 6 Year 1 Year 2 Year 3 7 5 9 Quarters 8 8 12 9 Principal paid Interest paid 10 It Principal remaining $490,000 $490,000 $490,000 12 13 14 15. 16 17 18 19 20 Business Plan Loan Details 21 22 23 24 25 26 27 20 29 30 <> CANYON Ready Loan (pv) Documentation Annual rate 1.40% Years 10 Payments (nper) Buy or Lease 40 Year 4 Year S 13 17 16 20 $490,000 $490,000 G Payment (pm) ($16,082) Total H 50 $0 Depreciation Profit & Loss + K L M
A CANYON TRANSFOR Long-term assets (cost) Salvage value (salvage) Life of asset (life) 2 3 4 5 6 7 8 Straight-Line 9 Yearly depreciation 10 Cumulative depreciation 11 Depreciated asset value 12 13 14 Declining Balance 15 Yearly depreciation 16 Cumulative depreciation 17 Depreciated asset value 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Ready Documentation $ $ B C Airport Shuttles Depreciation 115,000 15,000 10 2 115,000 $ 115,000 $ 2 115,000 $ 115,000 $ Loan Details $ Business Plan Year D Year 3 3 115,000 $ 115,000 $ 115,000 $ 115,000 $ Depreciation E Buy or Lease F 5 115,000 5 115,000 Profit & Loss + G
CANYON THE Percent cost of marketing 1 2 3 Percent cost of R&D 4 Tax rate 5 6 Income 7 Revenue 8 Cost of marketing 9 Cost of R&D 10 Gross profit 11 12 Expenses 13 Payroll 14 Rent 15 Insurance 16 Miscellaneous 17 Total expenses 18 19 Earnings 20 Initial earnings 21 Depreciation 22 Operating profit 23 24 Interest expense 25 Pretax profit 26 27 Tax liability 28 After-tax profit 29 30 31 32 33 34 <> Ready Documentation $ $ $ $ Business Plan Profit & Loss Statement 25% 7.25% 33% Year I Year 2 Year 2 825,000 206,250 59,813 558,938 588,000 60,000 30,000 15,000 693,000 (134,063) $ (134,063) (26,658) (107.404) (107.404) $ Loan Details Year 1 Year I 30,000 15,000 45,000 (45,000) $ (45,000) (24.504) (20,496) (20.496) S Buy or Lease Year 2 Year 3 Year 3 32,000 15,000 47,000 (47,000) $ (47,000) (22,227) (24,773) (24.773) $ Depreciation Year 3 Year 4 Year 4 $ 32,000 15,000 47,000 (47.000) $ (47,000) (19,819) (27,181) (27,181) $ Year 4 Profit & Loss Year 5 1,400,000 350,000 101,500 948,500 Year S 64,000 34,000 15,000 113,000 835,500 835,500 (17,274) 852,774 281,415 571,358 + Year 5
a 40,000 18.000 120 825 330% 1,329 75.05 4,000.00 200 700% me R: E & D C Mire B Airport Shuttles Buy or Lease Scenarios Month www.ing $ 4 R F E 4 S 4 2 . A 18 11 12 13 14 15 14 17 In 41 20 21 n 31 24 25 24 27 O N ve in 5 V Malo A Val NUME INUM #NUM www. IN IMPE ANA INUM INGUR IM SNAPE NAME NUM #APE #NUM #UM WAR NUP IN ANUM RUM ANUN INUN #NUM ANUE SNUM T G 40,000 19.720 Pro & Lo 6 B 2P Y Buy Scenarie (414) H 7 U N $ 8 r Lease Scenario S ST (5,000 1 (oo) M (200) (roo) lood (700) 000) Good (700) cro (900) lood (700) croe om Nood om & (700) con 1000 proo (700) (90) 100 load Cout presy > Checking Vide 9 K MI M IF vite O N < . * .C 1 0 L Ce J d command
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- Focus 15 Fressibility Investigate T
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- CENGAGE 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 (cels 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. IF Foos Fo 00 % 5 T G 6 & Y H & 7 U J 00. 8 DIL FF I 9 K DD O - O 0 L 4 ZA F1D P