help please!

Mr. Milbert Marvel has a 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 operate his food truck for eight years. At the end of the eighth year Mr. Marvel will sell off his equipment, and open a record store in Roanoke. After careful study, Mr. Milbert Marvel has determined that he can purchase a vehicle for $70,000. Eddie, 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, none 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 200 miles each week. The truck's consumption of fuel is 3.0 miles 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 -per-gallon, and Milbert After reviewing the cost of recipes that students in this class conducted, Milbert has selected one for his main dish that should prove popular on his food truck route. By adjusting proportions, he has gotten the variable cost down to $1.93 per serving. As an additional source of revenue, he will also offer a second item that will cost him 75c per serving. Milbert desires a 65% gross margin* on all sales, but wants his 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 deducted from the selling price.) He expects to sell 70 servings of the main dish each day, and expects that the second tem's sales will be 40% of the main dish's volume 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 a schedule of discounted annual cash flows associated for the proposed venture Use a (borizontal) total cost format similar to what we used in dass. See the review problem on page 655. Also look at Appendivx 13C) Use Excel to calculate the following the calculation of annual depreciation expense Required: Construct in Excel a schedule of discounted annual cash flows associated for the proposed venture Use a (borizontal) total cost format similar to whbat we used in dass. See the review problem on page 655. Also look at Appendix 13C Use Excel to calculate the following: a) The net present value of the investment using an interest rate (discount rate) of 10%. b) The Internal Rate of Return for the venture. c) In your spreadsheet, include a paragraph or two concerning whether Mr. Marvel should or should not operate a food truck, and then explain why (or why not) This assignment is worth 25 points. It must be submitted on time through Blackboard. Five bonus points are available ifyou create a second spreadsheet and solve this case the second time using straight-line depreciation Remember: Assistance disclosure must be made atio abl ear MACRS Pro Classes 7-Year Class 3-Year Class 33.33% 44.45% 14.81% 7.41% 5-Year Class 20.00% 32.00% 19.20% 11.52% 11.52% 57600 14.29% 24.49% 17.49% 12.49% 8.93% 8.92% 8.93% 4.46% 10-Year Class 10.00% 18.00% 14.40% 11.52% 922% 6 6.55% 6.55% Defined in this case as the difference between the selling price and the cost of ingredients. Solution to Review Problem Page 655 I. The annual net cash inflow can be computed by deducting the cash expenses from sales Sales Variable expenses Contribution margin Advertising, salaries, and other $3,200,000 1,800,000 1,400,000 fixed out-of-pocket costs Annual net cash inflow 700,000 $ 700,000 Or the annual net cash inflow can be computed by adding depreciation back to net operating income Net operating income Add: Noncash deduction for depreciation Annual net cash inflow $400,000 300,000 $700,000 2. The net present value is computed as follows Year Now (2.400.000) 3 Initial investment 4 Sales s Variable expenses 6 Fixed out-of-pocket costs 3,200,000$3.200,000 $3.200.000 $ 3.200,000 $3.200,000 (1,800,000) $(1.800,000) $ (1,800,000) $(1.800,000) (1,800,000) Total cash flows (a 8 Discount factor (12%) (b) 9 Present value of cash flows (a) x (b) (2.400.000) 10 Net present value (SUM B9 G9) 123,500 $ (2.400.000) 700,000 700,000 700,000 $ 700,000 700,000 0.567 625,100 557,900$ 498.400$445,200 396,900 0.893 0.797 0.712 0.636 ale SuM8909 )099700.0000 0 2 Note: The discount factors come from Exhibit 138-1 in Appendix 138 Or, it can also be computed as follows Years 1-5 Now (2.400,000) 3 Initial investment 4 Sales 5 Variable expenses 6 Fixed out-of-pocket costs 7Total cash flows (a) 8 Discount factor (12%) (b) 9 Present value of the cash flows (a) x (b) 10 Net present value (SUM B9:C9) 3.200,000 (1.800.000) 000) (2.400,000) S 700,000 3.605 1.000 $ (2.400.000) $123,500 $2.523,500 2 Note: The discount factor comes from Exhibit 13B-2 in Appendix 13B Yes, the project is acceptable because it has a positive net present value 3. The formula for computing the factor of the internal rate of return is Investment requined Factor of the internal rate of returnal net cash flom 24000003.429 700,000 Looking in Exhibit 13B-2in Appendix 13Bat the end of the chapter and scanning along the 5-period line, we find that a factor of 3.429 is closest to a factor of 3.433, which corresponds to a rate of return of 14% 4. The formula for the payback period is: Page 656 Investment required Payback period Annual iat cash flow $2.400,000 $700,000 2 Note: The discount factor comes from Exhibit 13B-2 in Appendix 13 1.2 Yes, the project is acceptable because it has a positive net present value 3. The formula for computing the factor of the internal rate of return is Factor of the internal rate of returnvetm required Annaal net cash flom $2.400,000 -3.429 $700,000 .- Looking in Exhibit 13B-2 in Appendix 13Bat the end of the chapter and scanning along the 5-period line, we find that a factor of 3.429 is closest to a factor of 3.433, which corresponds to a rate of return of 14% 4. The formula for the payback period is Page 656 Investment required Annual net cash low S2,400,000 $700,000 Payback period- 3.4 years (rounded) 5. The formula for the simple rate of return is: Simple rate of return Annual incremental net operating income Initial investment 400,00 $2,400,000 = 16.7 % Mr. Milbert Marvel has a 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 operate his food truck for eight years. At the end of the eighth year Mr. Marvel will sell off his equipment, and open a record store in Roanoke. After careful study, Mr. Milbert Marvel has determined that he can purchase a vehicle for $70,000. Eddie, 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, none 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 200 miles each week. The truck's consumption of fuel is 3.0 miles 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 -per-gallon, and Milbert After reviewing the cost of recipes that students in this class conducted, Milbert has selected one for his main dish that should prove popular on his food truck route. By adjusting proportions, he has gotten the variable cost down to $1.93 per serving. As an additional source of revenue, he will also offer a second item that will cost him 75c per serving. Milbert desires a 65% gross margin* on all sales, but wants his 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 deducted from the selling price.) He expects to sell 70 servings of the main dish each day, and expects that the second tem's sales will be 40% of the main dish's volume 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 a schedule of discounted annual cash flows associated for the proposed venture Use a (borizontal) total cost format similar to what we used in dass. See the review problem on page 655. Also look at Appendivx 13C) Use Excel to calculate the following the calculation of annual depreciation expense Required: Construct in Excel a schedule of discounted annual cash flows associated for the proposed venture Use a (borizontal) total cost format similar to whbat we used in dass. See the review problem on page 655. Also look at Appendix 13C Use Excel to calculate the following: a) The net present value of the investment using an interest rate (discount rate) of 10%. b) The Internal Rate of Return for the venture. c) In your spreadsheet, include a paragraph or two concerning whether Mr. Marvel should or should not operate a food truck, and then explain why (or why not) This assignment is worth 25 points. It must be submitted on time through Blackboard. Five bonus points are available ifyou create a second spreadsheet and solve this case the second time using straight-line depreciation Remember: Assistance disclosure must be made atio abl ear MACRS Pro Classes 7-Year Class 3-Year Class 33.33% 44.45% 14.81% 7.41% 5-Year Class 20.00% 32.00% 19.20% 11.52% 11.52% 57600 14.29% 24.49% 17.49% 12.49% 8.93% 8.92% 8.93% 4.46% 10-Year Class 10.00% 18.00% 14.40% 11.52% 922% 6 6.55% 6.55% Defined in this case as the difference between the selling price and the cost of ingredients. Solution to Review Problem Page 655 I. The annual net cash inflow can be computed by deducting the cash expenses from sales Sales Variable expenses Contribution margin Advertising, salaries, and other $3,200,000 1,800,000 1,400,000 fixed out-of-pocket costs Annual net cash inflow 700,000 $ 700,000 Or the annual net cash inflow can be computed by adding depreciation back to net operating income Net operating income Add: Noncash deduction for depreciation Annual net cash inflow $400,000 300,000 $700,000 2. The net present value is computed as follows Year Now (2.400.000) 3 Initial investment 4 Sales s Variable expenses 6 Fixed out-of-pocket costs 3,200,000$3.200,000 $3.200.000 $ 3.200,000 $3.200,000 (1,800,000) $(1.800,000) $ (1,800,000) $(1.800,000) (1,800,000) Total cash flows (a 8 Discount factor (12%) (b) 9 Present value of cash flows (a) x (b) (2.400.000) 10 Net present value (SUM B9 G9) 123,500 $ (2.400.000) 700,000 700,000 700,000 $ 700,000 700,000 0.567 625,100 557,900$ 498.400$445,200 396,900 0.893 0.797 0.712 0.636 ale SuM8909 )099700.0000 0 2 Note: The discount factors come from Exhibit 138-1 in Appendix 138 Or, it can also be computed as follows Years 1-5 Now (2.400,000) 3 Initial investment 4 Sales 5 Variable expenses 6 Fixed out-of-pocket costs 7Total cash flows (a) 8 Discount factor (12%) (b) 9 Present value of the cash flows (a) x (b) 10 Net present value (SUM B9:C9) 3.200,000 (1.800.000) 000) (2.400,000) S 700,000 3.605 1.000 $ (2.400.000) $123,500 $2.523,500 2 Note: The discount factor comes from Exhibit 13B-2 in Appendix 13B Yes, the project is acceptable because it has a positive net present value 3. The formula for computing the factor of the internal rate of return is Investment requined Factor of the internal rate of returnal net cash flom 24000003.429 700,000 Looking in Exhibit 13B-2in Appendix 13Bat the end of the chapter and scanning along the 5-period line, we find that a factor of 3.429 is closest to a factor of 3.433, which corresponds to a rate of return of 14% 4. The formula for the payback period is: Page 656 Investment required Payback period Annual iat cash flow $2.400,000 $700,000 2 Note: The discount factor comes from Exhibit 13B-2 in Appendix 13 1.2 Yes, the project is acceptable because it has a positive net present value 3. The formula for computing the factor of the internal rate of return is Factor of the internal rate of returnvetm required Annaal net cash flom $2.400,000 -3.429 $700,000 .- Looking in Exhibit 13B-2 in Appendix 13Bat the end of the chapter and scanning along the 5-period line, we find that a factor of 3.429 is closest to a factor of 3.433, which corresponds to a rate of return of 14% 4. The formula for the payback period is Page 656 Investment required Annual net cash low S2,400,000 $700,000 Payback period- 3.4 years (rounded) 5. The formula for the simple rate of return is: Simple rate of return Annual incremental net operating income Initial investment 400,00 $2,400,000 = 16.7 %