Marvel's Food Truck ACCT 333 Excel #5 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. oss 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. nd 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 Valuc, and Internal Rate of Return. Sheet 2: Use accelerated depreciation via appropriate the MACRS table to calculate taxable income, the annual cashflows, Net Present Value, and Internal Rate of Return. Sheet 3: This is the decision-making part of the assignment. Using the information contained in the narrative, and the calculations that you have made in the first two sheet, should Mr. Marvel undertake this endeavor? If so, which depreciation scheme should he use? Explain the reasoning behind your decision. Please use a textbox for this portion (Insert > Textbox). I recommend using an external text editor - Word, TextEdit, etc. - to compose and then copy/paste into the Excel textbox. Use a (horizontal) total cost format similar to what we used in class. You may also want review Appendix 13C if you haven't done so. Use the discount rate of ten percent for present value calculations. This assignment is worth 25 points. It must be submitted on time through Blackboard. Remember: Assistance disclosure must be made. 10-Year Class MACRS Depreciation Tables Year # 3-Year Class 33.33% 44.45% 14.81% 7.41% MACRS Property Classes S-Year Class 7-Year Class 20.00% 14.29% 32.00% 24.49% 19,20% 17.49% 11.52% 12.49% 11.52% 8.93% 5.76% 8.92% 8.93% 4.46% 10.00% 18.00% 14.40% 11.52% 9.22% 7.37% 6.55% 6.55% Marvel's Food Truck ACCT 333 Excel #5 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. oss 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. nd 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 Valuc, and Internal Rate of Return. Sheet 2: Use accelerated depreciation via appropriate the MACRS table to calculate taxable income, the annual cashflows, Net Present Value, and Internal Rate of Return. Sheet 3: This is the decision-making part of the assignment. Using the information contained in the narrative, and the calculations that you have made in the first two sheet, should Mr. Marvel undertake this endeavor? If so, which depreciation scheme should he use? Explain the reasoning behind your decision. Please use a textbox for this portion (Insert > Textbox). I recommend using an external text editor - Word, TextEdit, etc. - to compose and then copy/paste into the Excel textbox. Use a (horizontal) total cost format similar to what we used in class. You may also want review Appendix 13C if you haven't done so. Use the discount rate of ten percent for present value calculations. This assignment is worth 25 points. It must be submitted on time through Blackboard. Remember: Assistance disclosure must be made. 10-Year Class MACRS Depreciation Tables Year # 3-Year Class 33.33% 44.45% 14.81% 7.41% MACRS Property Classes S-Year Class 7-Year Class 20.00% 14.29% 32.00% 24.49% 19,20% 17.49% 11.52% 12.49% 11.52% 8.93% 5.76% 8.92% 8.93% 4.46% 10.00% 18.00% 14.40% 11.52% 9.22% 7.37% 6.55% 6.55%