Luke has startup costs that will be $200,000 in equipment that is depreciable on a 7-year MACRS schedule. Lukes plan is to start and operate the business for 6 years at the end of which time he expects the business to be worth at least $380,000 You expect Luke to have initial working capital needs of $15,000, but these needs will remain proportionate to sales (they will grow at the same rate as sales grow). You expect sales in the first year to be $250,000 and that sales will grow by 20% per year. You project annual fixed operating expenses of $95,000 in the first year. These fixed expenses will grow by 10% per year. Your annual variable operating expenses are as follows: cost of goods sold will be 40% of sales, marketing, general, and administrative expenses are expected to be 10% of sales, and all other operating expenses are expected to be 5% of sales.

You expect Luke to pay taxes of 21%. Assume your required return is 15%.

Please estimate the total cash flows for this opportunity using Excel (please show formulas and cell references) (if there is any issues with the previous statement, feel free to correct)

\begin{tabular}{|l|r|r|r|r|r|r|r|} \hline \multicolumn{1}{|c|}{ Description } & \multicolumn{5}{|c|}{ Years } \\ \cline { 2 - 6 } & & 1 & 2 & 3 & 4 & 5 & 6 \\ \hline Sales & & 250000 & 30000 & 360000 & 432000 & 518400 & 622080 \\ \hline Less: Variable cost ( 55\% of sales) & & 137500 & 165000 & 198000 & 237600 & 285120 & 342144 \\ \hline Contribution & & 112500 & 135000 & 162000 & 194400 & 233280 & 279936 \\ \hline Less: Fixed cost (Increases 10\% a year) & & 95000 & 104500 & 114950 & 126445 & 139089.5 & 152998.45 \\ \hline Less: Depreciation (MACRS system) & & 28580 & 48980 & 34980 & 24980 & 17860 & 17840 \\ \hline Income before tax & & 11080 & 18480 & 12070 & 42975 & 76330.5 & 109097.55 \\ \hline Less: Tax ( 21\%) & & 0 & 0 & 2534.7 & 9024.75 & 16029.405 & 22910.4855 \\ \hline Income after tax & & 11080 & 18480 & 9535.3 & 33950.25 & 60301.095 & 86187.0645 \\ \hline Add: Depreciation & & 28580 & 48980 & 34980 & 24980 & 17860 & 17840 \\ \hline Less: Working capital & 15000 & 18000 & 21600 & 25920 & 31104 & 37324.8 & 44789.76 \\ \hline Add: Recovered working capital & & 15000 & 18000 & 21600 & 25920 & 31104 & 37324.8 \\ \hline Cost & 200000 & & & & & \\ \hline \end{tabular}