Question

Rogers Restaurants is looking at a project with the following forecasted sales: First-year sales quantity of 31,000 with an annual growth rate of 3.5% over the next ten years. The sales price per unit is $42.00 and will grow at 2.25% per year. The production costs are expected to be 55% of the current year’s sales price. The manufacturing equipment to aid this project will have a total cost (including installation) of $2,400,000. It will be depreciated using MACRS and has a seven-year MACRS life classification. Fixed costs are $335,000 per year. Rogers Restaurants has a tax rate of 30%.

What is the operating cash flow for this project over these ten years?

Based on the 10-year projected OCFs, if the manufacturing equipment can be sold for $80,000 at the end of the five-year project and the cost of capital is 12%. Explain whether it is worth for Rogers to invest on this manufacturing equipment. Hint: Perform the estimation using Excel spreadsheet.

Answer 1

Based on the given data, pls find below steps, workings and answers:

Operating Cash Flows from Year 0 till Year 10 are provided below. The Net Present Value of this Project for the 10 year life period is negative $ 359420.43 The IRR of this Project is 8.43%, which is lower than that of the cost of capital; Hence, it is not worth for Rogers to invest on this manufacturing equipment.

Similarly, even if the equipment is sold at year 5 for $ 80000, the project is still not viable as the cumulative discounted cash flow value as at end of year 5 is still negative of $ 1189640; Hence, the value of proceeds from the sale of equipment shall not fetch any benefit.

Year 0 Year 1 31,000 42.00 23.10 Year 2 32,085 42.95 23.62 Year 3 33,208 43.91 24.15 Year 4 34,370 44.90 24.69 Year 5 35,573 45.91 25.25 Year 6 36,818 46.94 Year 7 38,107 48.00 26.40 Year 8 39,441 49.08 26.99 Year 9 40,821 50.18 27.60 Year 10 42,250 51.31 28.22 25.82 Project Units Sold Price per unit Variable Cost Cost of Equipment Salvage Value Sales Value Depreciation % MACRS - 7 year Depreciation 24,00,000 14.29% 3,42,960 24.49% 5,87,760 17.49% 4,19,760 12.49% 2,99,760 8.93% 2,14,320 8.92% 2,14,080 8.93% 2,14,320 4.46% 1,07,040 13,02,000 13,77,890 14,58,204 15,43,199 16,33,148 17,28,341 18,29,081 19,35,694 20,48,521 21,67,924 Revenue Other Income 11,26,686 3,35,000 11,92,358 3,35,000 Variable Cost Fixed Costs Depreciation EBT Tax @ 30% PAT 7,16,100 3,35,000 3,42,960 -92,060 -27,618 -64,442 7,57,840 3,35,000 5,87,760 -3,02,709 -90,813 -2,11,897 8,02,012 3,35,000 4,19,760 -98,568 -29,570 -68,998 8,48,760 3,35,000 2,99,760 59,680 17,904 41,776 8,98,232 3,35,000 2,14,320 1,85,597 55,679 1,29,918 9,50,587 3,35,000 2,14,080 2,28,673 68,602 1,60,071 10,05,995 3,35,000 2,14,320 2,73,767 82,130 1,91,637 10,64,632 3,35,000 1,07,040 4,29,022 1,28,707 3,00,316 5,86,834 1,76,050 4,10,784 6,40,566 1,92,170 4,48,396 Cash Flows (PAT + Depreciation) Discounting Factor Discounted Cash Flow Cumulative Discounted Cash Flow -24,00,000 1 -24,00,000 -24,00,000 2,78,518 0.8929 2,48,677 -21,51,323 3,75,863 0.7972 2,99,636 -18,51,687 3,50,762 0.7118 2,49,666 -16,02,021 3,41,536 0.6355 2,17,052 -13,84,969 3,44,238 0.5674 1,95,330 -11,89,640 3,74,151 0.5066 1,89,557 -10,00,083 4,05,957 0.4523 1,83,634 -8,16,449 4,07,356 0.4039 1,64,524 -6,51,925 4,10,784 0.3606 1,48,133 -5,03,792 4,48,396 0.3220 1,44,371 -3,59,420 NPV -3,59,420.43 8.43% IRR Discounting Factor 12.00%