New Perspectives Excel 2019 | Module 9: End of Module Project 1

Canyon Transport
PERFORM FINANCIAL CALCULATIONS

Author: Khushkumar Patel

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.
 Airport Shuttles
Business Plan
Annual interest rate 5.60%
Expenses Payment amount Future value Payments Loan amount
Buses and vans 320,000 (pmt) (fv) (nper) (pv)
Shuttle stop construction 150,000 Business loan (pv) $ 490,000 $ 490,000 $ 490,000 $ 457,037
Miscellaneous expenses 20,000 Future value (fv) $ - $ (57,483) $ - $ -
Total expenses $ 490,000 Years 10 10 10.99 10
Assets Payments / year 4 4 4 4
Cash 35,000 Total payments (nper) 40 40 43.97 40
Non-cash assets 112,000 Annual rate 5.60% 5.60% 5.60% 5.60%
Miscellaneous assets 80,000 Rate / quarter (rate) 1.40% 1.40% 1.40% 1.40%
Long-term assets 115,000 Quarterly payments (pmt) $ (16,082) $ (15,000) $ (15,000) $ (15,000)
Total assets $ 342,000
Total expenses and assets $ 832,000
Liabilities
Long-term loan 490,000
Outstanding debts -
Total liabilities $ 490,000
Capital
Owners 210,000
Small business grant 25,000
Total available capital $ 235,000

Summary
New division investment 235,000
New division expenses (490,000)
Initial equity (255,000)
Total liabilities and equity $ 235,000

TOTAL FUNDING $ 725,000

585506786.xlsx Business Plan 04/01/2022

 Airport Shuttles
Loan Details
Loan Annual rate Payments Rate per Years Payments Payment
(pv) per year period (nper) (pmt)
(rate)

$490,000 5.60% 4 1.40% 10 40 ($16,082)

Cumulative Interest and Principal Payments per Year

Year 1 Year 2 Year 3 Year 4 Year 5
1 5 9 13 17
Quarters
4 8 12 16 20 Total
Principal paid ($37,669) ($39,823) ($42,101) ($44,508) ($47,054) ($211,156)
Interest paid ($26,658) ($24,504) ($22,227) ($19,819) ($17,274) ($110,481)
Principal remaining $452,331 $412,507 $370,407 $325,898 $278,844

585506786.xlsx Loan Details 04/01/2022

 Airport Shuttles
Buy or Lease Scenarios
Shuttle Bus Months Asset Value Buy Scenario Lease Scenario
Current price (cost) $ 40,000 Initial Investment $ 40,000 $ (42,145) $ (4,000)
Salvage value (salvage) $ 18,000 1 280 - (700)
Salvage months (life) 120 2 278 - (700)
3 276 - (700)
Buy Scenario 4 274 - (700)
Service contract $ 825 5 272 - (700)
Sales tax rate 3.30% 6 270 - (700)
Tax on sale $ 1,320 7 268 - (700)
Resale percent 75.0% 8 267 - (700)
9 265 - (700)
Lease Scenario 10 263 - (700)
Security deposit $ 4,000.00 11 261 - (700)
Monthly payment $ 700 12 259 - (700)
13 257 - (700)
Buy or Lease 14 256 - (700)
Annual discount rate 7.00% 15 254 - (700)
Monthly discount rate 0.58% 16 252 - (700)
Buy: Net present value 17 250 - (700)
Lease: Net present value 18 248 - (700)
Recommendation LEASE 19 247 - (700)
20 245 - (700)
21 243 - (700)
22 242 - (700)
23 240 - (700)
24 238 - (700)
25 237 - (700)
26 235 - (700)
27 233 - (700)
28 232 - (700)
29 230 - (700)
30 228 - (700)
31 227 - (700)
32 225 - (700)
33 224 - (700)
34 222 - (700)
35 221 - (700)
36 219 - (700)
End of Contract $ 164 $ 4,000

585506786.xlsx Buy or Lease 04/01/2022

 Airport Shuttles
Depreciation

Long-term assets (cost) $ 115,000

Salvage value (salvage) $ 15,000
Life of asset (life) 10

Year
Straight-Line 1 2 3 4 5
Yearly depreciation 10,000 10,000 10,000 10,000 10,000
Cumulative depreciation 10,000 20,000 30,000 40,000 50,000
Depreciated asset value $ 105,000 $ 95,000 $ 85,000 $ 75,000 $ 65,000

Year
Declining Balance 1 2 3 4 5
Yearly depreciation 21,160 17,267 14,090 11,497 9,382
Cumulative depreciation 21,160 38,427 52,516 64,013 73,395
Depreciated asset value $ 93,840 $ 76,573 $ 62,484 $ 50,987 $ 41,605

585506786.xlsx Depreciation 04/01/2022

 Profit & Loss Statement

Percent cost of marketing 25%

Percent cost of R&D 7.25%
Tax rate 33%

Income Year 1 Year 2 Year 3 Year 4 Year 5

Revenue $ 825,000 $ 968,750 $ 1,112,500 $ 1,256,250 $ 1,400,000
Cost of marketing 206,250 242,188 278,125 314,063 350,000
Cost of R&D 59,813 70,234 80,656 91,078 101,500
Gross profit 558,938 656,328 753,719 851,109 948,500

Expenses Year 1 Year 2 Year 3 Year 4 Year 5

Payroll $ 588,000 $ 623,280 $ 660,677 $ 700,317 $ 742,336
Rent 60,000 60,976 61,968 62,976 64,000
Insurance 30,000 30,000 32,000 32,000 34,000
Miscellaneous 15,000 15,000 15,000 15,000 15,000
Total expenses 693,000 729,256 769,645 810,293 855,336

Earnings Year 1 Year 2 Year 3 Year 4 Year 5

Initial earnings $ (134,063) $ (72,928) $ (15,926) $ 40,816 $ 93,164
Depreciation 21,160 17,267 14,090 11,497 9,382
Operating profit (155,223) (90,194) (30,015) 29,319 83,782

Interest expense (26,658) (24,504) (22,227) (19,819) (17,274)

Pretax profit (128,564) (65,690) (7,789) 49,138 101,056

Tax liability - - - 16,216 33,348

After-tax profit $ (128,564) $ (65,690) $ (7,789) $ 32,923 $ 67,707

585506786.xlsx Profit & Loss 04/01/2022