University of North Texas The Mobile Vegan Financial Tools and Functions Worksheet

User Generated

Xnggrl

Business Finance

Description

Unformatted Attachment Preview

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
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Hi there👋 , the file is in the attachment.Please note that I'm uploading the file named "np_ex16_9a_anujakattel_2.xlsx" but studypool will add the question ID to the file name (will look like"(some numbers)np_ex16_9a_anujakattel_2.xlsx"), so, before uploading to your professor, you can either:1) download the .xlsx and remove the ID from the file name2) extract the content of the .zip file, which will have the .xlsx with the correct name.Both files will have the same content.

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
4.93%
0.41%
6
72
1/2/2019

Add 3 Trucks
$161,000
4.50%
0.44%
7
84
1/2/2019

($1,057)

($1,800)

($2,300)

Take Over
$500,000
5.00%
0.50%
10.1
122
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

($2,570)
($10,111)
$45,889

($2,053)
($10,628)
$35,261

($1,509)
($...


Anonymous
Just what I needed…Fantastic!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags