New Perspectives Excel 2016 | Module 9: SAM Project 1a
The Mobile Vegan
FINANCIAL TOOLS AND FUNCTIONS
GETTING STARTED
•
Open the file NP_EX16_9a_FirstLastName_1.xlsx, available for download
from the SAM website.
•
Save the file as NP_EX16_9a_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_EX16_9a_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.
You are a part-time manager for The Mobile Vegan, a food truck catering to
vegan diners in Portland, Oregon. Jessica Rosen, the owner of the business,
wants to expand by adding other food trucks in Portland and the surrounding
area. Whether she purchases new trucks or takes over an existing food truck
business with an established clientele, she needs a loan to cover the cost of the
trucks. She has asked for your help updating the workbook she created to
analyze the loan information and forecast sales.
Switch to the Loan Scenarios worksheet, and then calculate the monthly
payment for the Add 1 Truck scenario as follows:
2.
3.
a.
In cell D11, enter a formula using the PMT function to calculate the
monthly payment for a loan.
b.
Use the inputs listed under the Add 1 Truck loan scenario in cells D5, D7,
and D9. (Hint: The result will be displayed as a negative number to
reflect the negative cash flow of a loan payment.)
Calculate the monthly interest rate for the Add 2 Trucks scenario as follows:
a.
In cell E7, enter a formula using the RATE function to calculate the
monthly interest rate for a loan.
b.
Use the inputs listed under the Add 2 Trucks loan scenario in cells E9,
E11, and E5. (Hint: Assume the present value of the loan is the loan
amount shown in cell E5.)
Calculate the loan amount for the Add 3 Trucks scenario as follows:
New Perspectives Excel 2016 | Module 9: SAM Project 1a
4.
5.
6.
a.
In cell F5, enter a formula using the PV function to calculate the loan
amount.
b.
Use the inputs listed under the Add 3 Trucks loan scenario in cells F7, F9,
and F11.
Calculate the number of months Jessica needs to pay back a loan for an
existing food truck business as follows:
a.
In cell G9, enter a formula using the NPER function to calculate how
many months it would take to pay back a $500,000 loan.
b.
Use the inputs listed under the Take Over loan scenario in cells G7, G11,
and G5.
Switch to the Amortization worksheet. Calculate the cumulative interest for a
loan for one food truck as follows:
a.
In cell C17, enter a formula using the CUMIPMT function to calculate the
cumulative interest paid on the loan after the first year (payment 1 in cell
C15 through payment 12 in cell C16) when the payments are made at
the end of the period. Use 0 as the type argument in your formula.
b.
Use absolute references for the rate, nper, and pv arguments.
c.
Use relative references for the start and end arguments.
d.
Copy the formula from cell C17 to the range D17:G17 to calculate the
interest paid in Years 2–5.
Calculate the cumulative principal for a loan for one food truck as follows:
a.
In cell C18, enter a formula using the CUMPRINC function to calculate
the cumulative principal paid in the first year (payment 1 in cell C15
through payment 12 in cell C16) when the payments are made at the end
of the period. Use 0 as the type argument in your formula.
b.
Use absolute references for the rate, nper, and pv arguments.
c.
Use relative references for the start and end arguments.
d.
Copy the formula from cell C18 to the range D18:G18 to calculate the
principal paid in Years 2–5.
7.
In cell H18, use the Error Checking command to identify the error in the cell,
and then correct the error. (Hint: The formula in the cell should calculate the
total the values in C18:G18 using the SUM function.)
8.
Calculate the principal amounts in the loan amortization schedule as follows:
a.
In cell E23, enter a formula using the PPMT function to determine the
amount of the first loan payment devoted to principal.
b.
Use absolute references only for the rate, nper, and pv arguments.
c.
Use cell A23 as the current period. (Hint: The period is based on a
monthly payment schedule.)
d.
Copy the formula from cell E23 to the range E24:E82 to calculate the
principal paid in Periods 2–60.
New Perspectives Excel 2016 | Module 9: SAM Project 1a
9.
10.
11.
12.
13.
Calculate the interest amounts in the loan amortization schedule as follows:
a.
In cell F23, enter a formula using the IPMT function to determine the
amount of the first loan payment devoted to interest.
b.
Use absolute references only for the rate, nper, and pv arguments.
c.
Use cell A23 as the current period. (Hint: The period is based on a
monthly payment schedule.)
d.
Copy the formula from cell F23 to range F24:F82 to calculate the principal
paid in Periods 2–60.
Switch to the Depreciation worksheet. Calculate the annual straight-line
depreciation for one new food truck (the most likely scenario) as follows:
a.
In cell C11, enter a formula using the SLN function to calculate the
straight-line depreciation for the truck during its first year of service.
b.
Use the values in cells D5, D6, and D7 for the arguments.
c.
Use absolute references for the cost, salvage, and life arguments in the
SLN formula.
d.
Copy the formula from cell C11 to the range D11:I11 to calculate the
yearly straight-line depreciation in Years 2–7.
Calculate the annual declining balance depreciation for one new food truck as
follows:
a.
In cell C18, enter a formula using the DB function to calculate the
declining balance depreciation for the new food truck during its first year
of service.
b.
Use the values in cells D5, D6, and D7 for the arguments.
c.
Use the value in cell C17 as the current period.
d.
Use absolute references only for the cost, salvage, and life arguments in
the DB formula.
e.
Copy the formula from cell C18 to the range D18:I18 to calculate the
yearly declining balance depreciation in Years 2–7.
Correct the errors on the Depreciation worksheet as follows:
a.
Determine the error in cell D19 by using the Trace Precedent and Trace
Dependent arrows. Correct the error so that the formula in cell D19
calculates the cumulative depreciation of the vehicle fleet by adding the
Cumulative Depreciation value in Year 1 to the Yearly Depreciation value
in Year 2.
b.
Copy the corrected formula in cell D19 to the range E19:I19, and then
remove any arrows from the worksheet.
Switch to the Income Statement worksheet. For the Add 1 Truck scenario,
project the income from Food sales for 2020-2022 (cells D5:F5) using a Growth
Trend interpolation. (Hint: Select the range C5:G5 before filling this series with
values.)
New Perspectives Excel 2016 | Module 9: SAM Project 1a
14.
Project the income from Catering for 2020-2022 (cells D7:F7) using a Linear
Trend interpolation. (Hint: Select the range C7:G7 before filling this series with
values.)
15.
Project expenses as follows:
16.
a.
Project the expenses for Payroll for 2020-2023 (cells D13:G13) using a
Growth trend extrapolation.
b.
Use a step value of 1.07. (Hint: When extrapolating values, the Trend
check box in the Series Dialog Box should not be checked.)
c.
Do not set a stop value for the series. (Hint: Select the range C13:G13
before filling this series with values.)
Switch to the Investment worksheet. Calculate the present value of an
investment in one new food truck as follows:
a.
In cell C15, enter a formula that uses the NPV function to calculate the
Present Value of the Add 1 Truck investment.
b.
Use the value in cell C14 as the desired rate of return.
c.
Use the range C7:C12 as the returns paid to investors. (Hint: If a
Formula Omits Adjacent Cell error warning appears, ignore it.)
17.
In cell C16, enter a formula without using a function that calculates the Net
Present Value by adding the Present Value of the Add 1 Truck investment
(calculated in cell C15) to the value of the initial investment (in cell C6).
18.
Calculate the internal rate of return as follows:
a.
In cell C17, enter a formula that uses the IRR function to calculate the
internal rate of return of the Add 1 Truck investment.
b.
Use the range C6:C12 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 SAM
website to submit your completed project.
New Perspectives Excel 2016 | Module 9: SAM Project 1a
Final Figure 1: Loan Scenarios Worksheet
New Perspectives Excel 2016 | Module 9: SAM Project 1a
Final Figure 2: Amortization Worksheet
Final Figure 3: Depreciation Worksheet
New Perspectives Excel 2016 | Module 9: SAM Project 1a
Final Figure 4: Income Statement Worksheet
Final Figure 5: Investment Worksheet
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
website.
New Perspectives Excel 2016 | Module 9: SAM Project 1a
The Mobile Vegan
FINANCIAL TOOLS AND FUNCTIONS
Anuja Kattel
edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM
website.
The Mobile Vegan Expansion Plan
Loan Scenarios
Conditions
Loan amount
Annual interest rate
Monthly interest rate
Loan period in years
Loan period in months
Start date of loan
Monthly Payment Due
Add 1 Truck
$56,000
4.50%
0.42%
5
60
1/2/2019
Add 2 Trucks
$112,000
0.00%
Add 3 Trucks
6
72
1/2/2019
4.50%
0.44%
7
84
1/2/2019
($1,800)
($2,300)
Take Over
$500,000
5.00%
0.50%
0.0
1/2/2019
($5,500)
The Mobile Vegan Expansion Plan
Amortization Schedule
Conditions
Loan amount (pv)
Annual interest rate
Monthly interest rate (rate)
Loan period in years
Loan period in months (nper)
Monthly Payment Due
Start date of loan
Add 1 Truck
$56,000
4.50%
0.42%
5
60
($1,057)
1/2/2019
Months
Cumulative Interest and Principal Payments per Year
Year 1
Year 2
Interest
Principal
Principal Remaining
Year 3
Year 4
1
13
25
37
12
24
36
48
$56,000
$56,000
$56,000
$56,000
Loan Amortization Schedule
Period
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Date
1/2/2019
2/1/2019
3/1/2019
4/1/2019
5/1/2019
6/1/2019
7/1/2019
8/1/2019
9/1/2019
10/1/2019
11/1/2019
12/1/2019
1/1/2020
2/1/2020
3/1/2020
4/1/2020
5/1/2020
6/1/2020
7/1/2020
8/1/2020
9/1/2020
10/1/2020
11/1/2020
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Beginning
Balance
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
Payment
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
Principal
Interest
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
12/1/2020
1/1/2021
2/1/2021
3/1/2021
4/1/2021
5/1/2021
6/1/2021
7/1/2021
8/1/2021
9/1/2021
10/1/2021
11/1/2021
12/1/2021
1/1/2022
2/1/2022
3/1/2022
4/1/2022
5/1/2022
6/1/2022
7/1/2022
8/1/2022
9/1/2022
10/1/2022
11/1/2022
12/1/2022
1/1/2023
2/1/2023
3/1/2023
4/1/2023
5/1/2023
6/1/2023
7/1/2023
8/1/2023
9/1/2023
10/1/2023
11/1/2023
12/1/2023
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
(1,056.79)
Year 5
49
60
Total
$0
#NAME?
$56,000
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Ending
Balance
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
Cumulative
Interest
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
-
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
56,000.00
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
-
The Mobile Vegan Expansion Plan
Depreciation
Food Truck - Fully Equipped
Long-term assets (cost)
Salvage value (salvage)
Life of asset (life)
Straight-Line Depreciation
Year
Yearly Depreciation
Cumulative Depreciation
Depreciated Asset Value
Declining Balance Depreciation
Year
Yearly Depreciation
Cumulative Depreciation
Depreciated Asset Value
$
$
56,000
8,750
7
1
2
3
4
5
6
7
$0
$56,000
$0
$56,000
$0
$56,000
$0
$56,000
$0
$56,000
$0
$56,000
$0
$56,000
1
2
3
4
5
6
7
$56,000
$56,000
$56,000
$56,000
$56,000
$0
$56,000
#VALUE!
#VALUE!
The Mobile Vegan Expansion Plan
Income Statement - Add 1 Truck
Income
Food sales
Beverage sales
Catering
Total Revenue
Expenses
Food cost of goods
Beverage cost of goods
Payroll
Maintenance
Insurance
Advertising
Total General Expenses
Initial Earnings
$
$
2019
96,000
48,000
90,000
236,019
2020
2021
2022
$
58,000
70,000
88,000
$
60,020
$
72,021
$
90,022
$
$
50,000
27,500
$
52,000
28,500
$
55,000
30,000
$
$
48,000
24,000
120,000
1,500
2,400
12,000
207,900
$
28,119
$
$
$
2,000
2,400
12,000
93,900
$
(33,880) $
2,000
2,400
12,000
96,900
$
(24,879) $
2,500
2,600
12,000
102,100
2023
280,000
115,000
220,000
617,023
58,000
32,000
$
2,500
2,600
12,000
107,100
(12,078) $
509,923
The Mobile Vegan Expansion Plan
Investment
Investor Repayment Schedule - Add 1 Truck Scenario
Payments
Net Cash Flow
$
(25,000) $
(25,000)
Startup
2,500
(22,500)
Year 1
4,000
(18,500)
Year 2
5,500
(13,000)
Year 3
6,800
(6,000)
Year 4
7,250
1,500
Year 5
7,500
9,000
Year 6
Desired Rate of Return
Present Value
Net Present Value
Internal Rate of Return
7.25%
Purchase answer to see full
attachment