Question

Mr. Milbert Marvel has the desire to open and operate a food truck. He is exploring whe pod truck. He is exploring whether such an endeavor would be Because you did so well helping Uncle Murray with his financial statements a couple o mother has referred Mr. Marvel to you. He plans to quit his job at the local Costco and years. At the end of the eighth year Mr. Marvel will take his profits, sell off his equipment quit his job at the local Costco and operate his food truck for eight to open a milkshake shop in Milwaukee. After careful study, Mr. Milbert Mary e his profits, sell off his equipment, and then use those proceeds waukee. After careful study. Mr. Milbert Marvel has determined that he can purchase a vehicle for $70,000. Eddie Edwards, the owner of the shop that will outfit the truck, has to cost around $3,000 to prepare the truck to Marvel's specifications. Eddie also shop that will outfit the truck, has told Milbert Marvel that it will around $15,000 in eight years. e the truck to Marvel's specifications. Eddie also told him that the truck should be worth Furthermore, an investment of an additional $1,000 would be required to purchase dice itional $1,000 would be required to purchase cleaning supplies, pot, pans, anu su turn. Alter eight years, it is doubtful that any of this working capital would be returned to sales tax rate is expected to remain at six percent for the time that Milbert Marvel will be run also need to rent space in a DHEC approved prep-kitchen. This will cost him $10,0 or the time that Milbert Marvel will be running his food truck. He will DHEC approved prep-kitchen. This will cost him $10,085 per year plus 1.5% of sales. He vor cleaning costs per year, and expects insurance will be $12,000 each year. Maintenance will be $5,000 per year. Milbert expects to drive 250 miles each week. The truck's consumption of fuel is 3.0m and Milbert predicts that the average cost of fuel will be $4.26 per gallon. He also expects to operate his truck each week. Because he wants to have some time off to visit the beach each summer, he will run the truck for fifty weeks each year. After reviewing his list of favorite recipes, Milbert Marvel has selected one for his main dish that should prove popular on his food truck route. By adjusting proportions, he has gotten the ingredient cost down to 974 per serving. Milbert desires a 75% gross margin (defined in this case as the difference between the selling price and the cost of ingredients), but wants his per unit selling price to be in whole dollars (use the “round” function) to make things easier when making change for cash customers. Any sales tax will not be tacked on to the selling price, but will be a percentage of annual sales revenue. For the first year he expects to sell 100 servings each day that he runs his truck. Optimistically, Marvel expects sales volume to increase ten percent each year. The combination of state and federal income tax rates is 30% and is expected to remain at that level for the full eight years. The IRS classifies the food truck as being in the 5-year property class, and the capital equipment will be depreciated using the IRS modified accelerated cost recovery system (MACRS). The MACRS tables are found below for the calculation of annual depreciation expense. Required: Construct in Excel the following schedules of discounted annual cash flows associated for the proposed venture making certain that any non-input cell references another cell or is a calculated amount. Your solution must be completely dynamic. • Sheet 1: Use straight-line depreciation when calculating taxable income, the annual cashflows, Net Present Value, and Internal Rate of Return. Ansi goloulote touble income the annual

I need Sheet 1 at bottom of page answered in Excel.

Thanks!

Time?

8 periods
50 weeks

Answer 1

Input and Assumptions:

Miles per Week (A)	250
Operating Days per Week (O)	5
Operating weeks per year (B)	50
Total Miles per Year (TM = A*B)	12,500
Mileage (Miles / Gallon) (M)	3
Fuel Consumption (Gallons / Mile) (FC = 1/M)	0.33
Cost of Fuel ($ / Gallon) (D)	$            4.26
Total Cost of Fuel per Year (TM*FC*D)	$       17,750
No. of Serving / Day in Year 1 (S)	100
Tota No. of Serving in Year 1 (SS = S*O*B)	25,000
Ingredient Cost per Serving (I)	$            0.97
Total Ingredient Cost in Year 1 (I * SS)	$       24,250
Gross Margin Desired (G)	75%
Selling Price per Serving in Year 1 (Rounded) (I/(1-G))	$            4.00
Increase in Sales Volume y-o-y	10%
Sales Tax (ST)	6%
Income Tax	30%
SLM Depreciation Rate (5 Years)	20.0%
Discount Rate (Assumed)	5.0%
Resale Value of Truck	$       15,000

Solution:

Year	0	1	2	3	4	5	6	7	8
Vehicle Cost	$         -70,000
Vehicle Fittings Cost	$           -3,000
Working Capital	$           -1,000
No of Servings		25,000	27,500	30,250	33,275	36,603	40,263	44,289	48,718
Gross Sales		$   1,00,000	$1,10,000	$ 1,21,000	$ 1,33,100	$ 1,46,410	$ 1,61,051	$ 1,77,156	$ 1,94,872
Sales Tax (GS * ST)		$         6,000	$      6,600	$       7,260	$       7,986	$       8,785	$       9,663	$     10,629	$     11,692
Net Sales		$       94,000	$1,03,400	$ 1,13,740	$ 1,25,114	$ 1,37,625	$ 1,51,388	$ 1,66,527	$ 1,83,179
Ingredient Cost		$       24,250	$    26,675	$     29,343	$     32,277	$     35,504	$     39,055	$     42,960	$     47,256
Rent		$       11,585	$    11,735	$     11,900	$     12,082	$     12,281	$     12,501	$     12,742	$     13,008
Cleaning Cost		$         3,500	$      3,500	$       3,500	$       3,500	$       3,500	$       3,500	$       3,500	$       3,500
Insurance		$       12,000	$    12,000	$     12,000	$     12,000	$     12,000	$     12,000	$     12,000	$     12,000
Maintenance Cost		$         5,000	$      5,000	$       5,000	$       5,000	$       5,000	$       5,000	$       5,000	$       5,000
Fuel Cost		$       17,750	$    17,750	$     17,750	$     17,750	$     17,750	$     17,750	$     17,750	$     17,750
Depreciation		$       14,000	$    14,000	$     14,000	$     14,000	$     14,000	$               -	$               -	$               -
Profit Before Tax		$         5,915	$    12,740	$     20,248	$     28,506	$     37,590	$     61,582	$     72,574	$     84,665
Tax		$         1,775	$      3,822	$       6,074	$       8,552	$     11,277	$     18,475	$     21,772	$     25,399
Profit After Tax		$         4,141	$      8,918	$     14,173	$     19,954	$     26,313	$     43,108	$     50,802	$     59,265
Resale Value of Truck (Post Tax)									$     10,500
Cash Flow	$         -74,000	$         4,141	$      8,918	$     14,173	$     19,954	$     26,313	$     43,108	$     50,802	$     69,765
NPV	$       1,02,800
IRR	23%

Unable to enclose excel sheet. Please ensure that you link every input in solution to Inputs & Assumptions for a dynamic sheet.