FROSTY DELIVERY TRUCK
Mark Lang is considering the purchase of an ice delivery truck that he would use before and after work to supplement his income. His uncle runs a large ice delivery business, Frosty Ice Delivery, and has offered to sell Mark oneice delivery truck as well as an existing ice delivery route to get started. Mark wants to make a 5-year commitment to this part-timebusiness opportunity, but it requires a substantial initial investment to purchase the truck as well as ongoing fuel, insurance, and maintenance costs. Mark's uncle has provided some historical data that Mark can use to evaluate the opportunity, but he wants your help in calculating the return from this investment. He already created a worksheet containingthe relevant financial data.
The Ice Truck worksheet contains a projected income statement and a cash flow statement. Mark entered the initial conditions of the investment. The cost of the ice truck is $25,000. For tax purposes, Mark plans to depreciate the truck'svalue completely over the 5-year period. At the end of the 5-year period, he believes he could sell the truck for $5,000. Mark assumes a 34% tax rate on income.
You need to complete the worksheet by adding the formulasto project the value of the ice truck delivery opportunity over the 5-year period. Use the same techniques and formulas as employed for the class real estate case study.
INSTRUCTIONS
1. Open Frosty Delivery Truck File.xlsx save the file as FrostyDTYourFirstLastName.xlsx before you move to the next step.
2. In the Ice Truck worksheet, enter the following information to calculate the total revenue generated:
a) In the range C10:G10, enter the yearly Ice Sales income, starting from a Year 1 value of $30,000 and assuming that the income increases following a linear trend to a Year 5value of $44,444.
b) Incell G11, enter a reference to the sales value of the ice delivery truck already entered in cell B8.
c) In the range C12:G12, calculate the total revenue generated by ice sales and sale ofthe truck from Year 1 through Year 5.
3. Enter the following information to calculate the total expenses for each year:
a) In the range C15:G15, insert the annual ice costs, starting from a Year 1 value of $5,000 and assuming that the expense will increase following a linear trend to a Year 5 value of $5,999.
b) In the range C16:G16, insert the annual fuelcosts, starting from a Year 1 value of $5,500 and assuming that the expensewill increase following a linear trend to a Year 5 value of $6,499.
c) In the range C17:G17, insert the annual insurance costs, starting from a Year 1 value of $650 and assuming that the expense will increase following a linear trend to a Year 5 value of $799.
d) In the range C18:G18, insert the annual maintenance costs, starting from a Year 1 value of $2,000 and assuming that the expense will increase following a linear trend to a Year 5 value of $3,349.
e) In the range B19:G19,calculate the totalexpenses (initial and each of the five years).
4. In the range C21:G21, calculate the initial earnings estimate by subtracting the totalannual expenses from the total annual revenue.
5. In the range C22:G22, calculate the annual depreciation of the ice delivery truck from Year 1 toYear 5. Assume a straight-line depreciation and use the ‘Cost of Truck' value for the initial cost, ‘Lifetime of Investment' value for useful life, and ‘Salvage Value' as thesalvage value. These values are listed at the top of the worksheet.
6. In the range C23:G23, calculate the taxable income by subtracting the yearly depreciationfrom the yearly initial earnings estimate.
7. In the range C25:G25, calculate the tax due on the taxable income for each of thenext fiveyears by multiplying the taxable income by the tax rate in cell B7.
8. In the range C27:G27, calculate the net income for each of the fiveyears of the ice delivery business.The net income is equal to the taxable income minus the total tax due.
(Note:The cash flow schedule at the bottom of the worksheet calculates the yearly cashreceipts that Mark expects to receive from delivering ice. Mark has already enteredall of the formulas to generate the cash flow schedule. The range B35:G35containsthe cumulative cash flow from the ice truck delivery businessforthe next fiveyears.)
9. In cell G4, enter 8% because Markhopes that his investment will have at least an 8% rateof return.
10. In cell G5, calculate the net present value (NPV) of the ice truck business using the cash flow valuesin the cell range C33:G33 as the yearly returns, andusing the value in cell B4 as the initial cost of the investment in the business. Assume that the initialexpenditure on the ice truck occurs immediately.
11. In cell G6, calculate the internal rate of return on the investment using the net cashflow values from the range B33:G33.
Attachment:- FROSTY DELIVERY TRUCK.xlsx