Reference no: EM13865064
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
|
Equivalent units related to conversion costs
: Arizaga Corporation manufactures canoes in two departments, Fabrication and Waterproofing. In the Fabrication Department, fiberglass panels are attached to a canoe- shaped aluminum frame. What are the Fabrication Department's equivalent units related..
|
|
Moisture content of bentonite
: Analyze a drier that reduces the moisture content of bentonite from 11% (bone dry basis) to 4% bdb. Assume the plant processes 100 tons/day is a gas fired rotary kiln (8 ft ID x 120 ft long).
|
|
Predetermined overhead rate based on direct labor-hours
: Sawyer Manufacturing Corporation uses a predetermined overhead rate based on direct labor-hours to apply manufacturing overhead to jobs. Last year, the Corporation worked 30,000 actual direct labor-hours and incurred $348,000 of actual manufacturing ..
|
|
Verilogprogram for a sticky-counter state machine
: Write an ABEL, VHDL, or Verilogprogram for a sticky-counter state machine with eight states, S0 -S7,that are coded into three bits in binary counting order. Besides CLOCK, the machine should have two inputs, RESET and ENABLE, and one output, DONE...
|
|
Calculate the total expenses for each year
: Calculate the total revenue generated and calculate the total expenses for each year.
|
|
Beginning with pretax income from continuing operations
: On December 31, 2016, the end of the fiscal year, California Microtech Corporation completed the sale of its semiconductor business for $7 million. The business segment qualifies as a component of the entity according to GAAP. Prepare the lower porti..
|
|
Formulate the null and alternative hypotheses
: Formulate the null and alternative hypotheses
|
|
Critically evaluate the investment and the stocks
: Estimate key risk-return items, such as dividend yield, capital gains yield, stock returns and standard deviation of stock returns. Critically evaluate the investment and the stocks.
|
|
Concept and detailed design of a kit
: The concept and detailed design of a kit for each car is estimated to take 30 hours of the owner's time (his current salary is $2,300 per fortnight).
|