Excel problem #9

profilestudent 777
np_excel2013_t9_p1a__1.xlsx
Home>Computer Science homework help>Excel problem #9

Documentation

New Perspectives Excel 2013
Tutorial 9: SAM Project 1a
Wayside Driving School
EXPLORING FINANCIAL TOOLS AND FUNCTIONS
Author: Hamzah Alghamdi
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.

Loan Analysis

LOAN SCENARIOS
WAYSIDE DRIVING SCHOOL
LOAN SCENARIOS Add 1 Location Add 2 Locations Add 3 Locations Expansion + BuyOut
Loan amount $250,000 $450,000 $1,000,000
Annual interest rate 5.00% 0.00% 5.00% 6.00%
Monthly interest rate 0.42% 0.44% 0.50%
Loan period in years 5 7 7 0.0
Loan period in months 60 84 84
Start date of loan 1/2/17 1/2/17 1/2/17 1/2/17
Monthly Payment Due ($7,500) ($10,000) ($10,000)

Amortization

LOAN SUMMARY AND AMORTIZATION SCHEDULE - Add 1 Location
WAYSIDE DRIVING SCHOOL
LOAN SUMMARY Add 1 Location
Loan amount (PV) $250,000
Annual interest rate 5.00%
Monthly interest rate (RATE) 0.42%
Loan period in years 5
Loan period in months (NPER) 60
Monthly Payment Due ($4,718)
Start date of loan 1/2/17
Cumulative Interest and Principal Payments per Year
Year 1 Year 2 Year 3 Year 4 Year 5
Months 1 13 25 37 49
12 24 36 48 60 Total
Interest ERROR:#NAME?
Principal $0
Principal Remaining $250,000 $250,000 $250,000 $250,000 $250,000
LOAN AMORTIZATION SCHEDULE
Pmt No Payment Date Beginning Balance Scheduled Payment Principal Interest Ending Balance Cumulative Interest
1 1/2/17 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
2 2/1/17 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
3 3/1/17 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
4 4/1/17 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
5 5/1/17 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
6 6/1/17 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
7 7/1/17 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
8 8/1/17 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
9 9/1/17 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
10 10/1/17 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
11 11/1/17 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
12 12/1/17 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
13 1/1/18 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
14 2/1/18 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
15 3/1/18 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
16 4/1/18 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
17 5/1/18 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
18 6/1/18 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
19 7/1/18 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
20 8/1/18 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
21 9/1/18 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
22 10/1/18 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
23 11/1/18 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
24 12/1/18 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
25 1/1/19 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
26 2/1/19 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
27 3/1/19 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
28 4/1/19 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
29 5/1/19 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
30 6/1/19 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
31 7/1/19 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
32 8/1/19 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
33 9/1/19 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
34 10/1/19 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
35 11/1/19 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
36 12/1/19 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
37 1/1/20 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
38 2/1/20 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
39 3/1/20 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
40 4/1/20 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
41 5/1/20 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
42 6/1/20 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
43 7/1/20 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
44 8/1/20 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
45 9/1/20 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
46 10/1/20 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
47 11/1/20 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
48 12/1/20 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
49 1/1/21 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
50 2/1/21 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
51 3/1/21 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
52 4/1/21 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
53 5/1/21 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
54 6/1/21 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
55 7/1/21 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
56 8/1/21 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
57 9/1/21 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
58 10/1/21 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
59 11/1/21 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0
60 12/1/21 $ 250,000.00 $ (4,717.81) $ 250,000.00 $ - 0

Depreciation

VEHICLE FLEET DEPRECIATION SCHEDULE
WAYSIDE DRIVING SCHOOL
FLEET STATISTICS
Cost of vehicles $ 125,000
Recovery period (years) 7
Salvage value $ 17,500
Straight-Line Depreciation
Year
1 2 3 4 5 6 7
Yearly Depreciation
Cumulative Depreciation $0 $0 $0 $0 $0 $0 $0
Depreciated Asset Value $125,000 $125,000 $125,000 $125,000 $125,000 $125,000 $125,000
Declining Balance Depreciation
Year
1 2 3 4 5 6 7
Yearly Depreciation
Cumulative Depreciation $0 ERROR:#VALUE!
Depreciated Asset Value $125,000 ERROR:#VALUE! $125,000 $125,000 $125,000 $125,000 $125,000

Income Statement

INCOME STATEMENT FORECAST
WAYSIDE DRIVING SCHOOL - FINANCIAL IMPACT OF OPENING 1 NEW LOCATIONS
Income 2017 2018 2019 2020 2021
Classroom Fees $ 300,000 $ 700,000
Sponsorship Fees $ 75,000 $ 85,000 $ 95,000 $ 105,000 $ 120,000
Other $ 5,000 $ 15,000
Total Revenue $ 382,017 $ 87,018 $ 97,019 $ 107,020 $ 837,021
EXPENSES
Payroll $ 112,500
Gasoline $ 46,000 $ 50,000 $ 55,000 $ 60,000 $ 62,500
Vehicle Maintenance $ 6,250 $ 9,375 $ 12,500 $ 15,625 $ 18,750
Rent & Utilities $ 45,000 $ 46,500 $ 48,000 $ 49,500 $ 51,000
Furniture and Other Office Supplies $ 32,500 $ 4,500 $ 4,500 $ 4,500 $ 4,500
Advertising $ 5,000 $ 5,000 $ 5,000 $ 5,000 $ 5,000
Total General Expenses $ 247,250 $ 115,375 $ 125,000 $ 134,625 $ 141,750
Initial Earnings $ 134,767 $ (28,357) $ (27,981) $ (27,605) $ 695,271

Rate of Return

RETURN ON INVESTMENT
WAYSIDE DRIVING SCHOOL
ADD 1 LOCATION
Payments Net Cash Flow
Startup $ (250,000) $ (250,000)
Year 1 $ 25,000 $ (225,000)
Year 2 $ 40,000 $ (185,000)
Year 3 $ 55,000 $ (130,000)
Year 4 $ 70,000 $ (60,000)
Year 5 $ 75,000 $ 15,000
Year 6 $ 75,000 $ 90,000
Desired Rate of Return 7.50%
Present Value
Net Present Value
Internal Rate of Return