USE EXCEL TO SHOW FORMULAS

The following sheet presents the balance sheet and value drivers of Little India, a company that operates Indian food restaurant

Additional model assumptions are as follows:

• The FCF evaluation is for a 5-year period. In addition, a terminal value should be determined
• using the long-term FCF growth rate.
• The debt principal repayments are $200,000 each year.
• Cash is a plug in the model.
• The depreciation rate is 5% of the average fixed assets at cost.
• The net fixed assets have a 15% growth rate.
• Interest payed on debt and interest earned on cash are based on the average balances.

Make a pro forma model including a DCF valuation to determine the company value and its estimated share value.

\begin{tabular}{|l|r|} \hline Value drivers: & \\ \hline Sales growth & 25% \\ \hline Current assets/Sales & 10% \\ \hline Current liabilities/Sales & 30% \\ \hline Net fixed assets & 15% \\ \hline Costs of goods sold/Sales & 35% \\ \hline Depreciation rate & 5% \\ \hline Interest rate on debt & 8.00% \\ \hline Interest earned on cash balances & 3.00% \\ \hline Tax rate & 40% \\ \hline Dividend payout ratio & 20% \\ \hline Sales & 1,100,000 \\ \hline Weighted average cost of capital & 12% \\ \hline Long-term FCF growth rate & 3% \\ \hline & \\ \hline Balance sheet & 370,000 \\ \hline Cash & 110,000 \\ \hline Current assets & \\ \hline Fixed assets & 2,000,000 \\ \hline At cost & (500,000) \\ \hline Depreciation & 1,500,000 \\ \hline \multicolumn{1}{|c|}{ Net fixed assets } & 1,980,000 \\ \hline Total assets & \\ \hline & 330,000 \\ \hline Current liabilities & 1,000,000 \\ \hline Debt & 500,000 \\ \hline Stock (500,000 shares, issued at $1 each) & 150,000 \\ \hline Accumulated retained earnings & 1,980,000 \\ \hline Total liabilities and equity & \\ \hline \end{tabular}