Q2-2

Forecasting Pro Forma Financial Statements Prepare a pro forma income statement and balance sheet for Webb Enterprises, where revenues are expected to grow by 20% in 2015 ~ 2017. Make the following assumptions in making your forecast.

• The income statement expenses are a constant percentage of revenues except for interest, which is charged at 5% of previous interest-bearing borrowings, and taxes, which equal 40% of earnings before taxes.
• The cash and marketable securities balance remain equal to $500, and the remaining current asset accounts increase in proportion to revenues.
• Net property, plant, and equipment increase in proportion to the increase in revenues.
• Depreciation will be estimated as 1% of sales.
• Accounts payable increases in proportion to firm revenues.
• Dividend of 400 are paid and will remain the same.
• Owners equity increases by the amount of firm net income minus dividend.
• Long-term debt remains unchanged, and short-term debt changes in an amount that balances the balance sheet.

1. Replicate the forecast for 2015 with excel formula.
2. Check the formula against the results for 2016.
3. Extend your forecasting to 2017.
4. Forecasting Firm FCF for 2017.

The data for 2014 is inlcuded below. You can also get the data from the template.

current 2014 Income Statement Sales Cost of Goods Sold Gross Profit Operating Expenses Depreciation Earnings before interest and taxes Interest Expense Earnings before Taxes Taxes Net Income 30,000.00 10.000.00 20,000.00 8,000.00 300.00 11,700.00 900.00 10,800.00 400.00 10,400.00 Dividend Addition to retained earnings 400.00 10,000.00 Balance Sheet (Pro-forma) 2014 Asset Cash and Marketable Securities Accounts Receivable Inventories 500.00 6,000.00 9,500.00 16,000.00 17.000.00 33,000.00 Current assets Net Property Plant & Equipment Total assets Working on the financing Total liab + Owner's equity = total asset Account payable Total equity long-term debt total short-term debt additional short-term borrowing cumulated additional short-term borrowing 33,000.00 7,200.00 2,000.00 7,000.00 16,800.00 Liabilities and Stockholder's equity Accounts Payable Short-term Debt Current liabilities Long-term Debt Total liabilities Total owners' equity Total Liabilities and Owners' Equity 7,200.00 16.800.00 24,000.00 7.000.00 31,000.00 2.000.00 33,000.00 plan and assumption operation plan 2015 Projected growth rate in sales Tax rate interest rate 0.400 0.050 relevant ratios based on current year cogs/ sales ratio operating expenses / sales cash / sales account receivable / sales inventory / sales net PPE / sales account payable / sales 2015 Current 0.571 0.200 0.029 0.286 0.271 0.286 0.143 Tracking the borrowing 2015 total interst bearning liabilities previous interest bearing liabilities 48,000.00 36,000.00 Income Statement Sales Cost of Goods Sold Gross Profit Operating Expenses Depreciation Earnings before interest and taxes Interest Expense Earnings before Taxes Taxes Net Income current 2015 70,000.00 40.000.00 20,000.00 14,000.00 4.000.00 2,000.00 1,800.00 200.00 800.00 1,400.00 Dividend Addition to retained earnings 400.00 1,000.00 Balance Sheet Cash and Marketable Securities Accounts Receivable Inventories Current assets Net Property Plant & Equipment Total assets 2015 2,000.00 20,000.00 19,000.00 41.000.00 20.000.00 61,000.00 Accounts Payable Short-term Debt Additional short-term debt needed Current liabilities Long-term Debt Total liabilities Total owners' equity Total Liabilities and Owners' Equity 10,000.00 20.000.00 0.00 28,000.00 20.000.00 48,000.00 13.000.00 61,000.00 A B D E F F G 1 02-2 2 plan and assumption Replicate Solve 3 4 operation plan 2015 2016 2017 5 6 a-1 7a-2 Projected growth rate in sales Tax rate interest rate 0.200 0.400 0.050 0.200 0.400 0.050 NA NA NA