Question

Mr. Milbert Marvel has the desire to open and operate a food truck. He is exploring whether such an endeavor would be profitable. Because you did so well helping Uncle Murray with his financial statements a couple of months ago, your mother has referred Mr. Marvel to you. He plans to quit his job at the local Costco and operate his food truck for eight years. At the end of the eighth year Mr. Marvel will take his profits, sell off his equipment, and then use those proceeds to open a milkshake shop in Milwaukee. 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 told Milbert Marvel that it will cost around $3,000 to prepare the truck to Marvel's specifications. Eddie also told him that the truck should be worth around $15,000 in eight years. Furthermore, an investment of an additional $1,000 would be required to purchase cleaning supplies, pot, pans, and so forth. After eight years, it is doubtful that any of this working capital would be returned to Mr. Marvel. The state sales tax rate is expected to remain at six percent for the time that Milbert Marvel will be running his food truck. He will also need to rent space in a DHEC approved prep-kitchen. This will cost him $10,085 per year plus 1.5% of sales. He expects to incur $3,500 of 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.0 miles-per-gallon, and Milbert predicts that the average cost of fuel will be $4.26 per gallon. He also expects to operate his truck five days 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.
How can I set this up?