Three Financial Statements Analysis (Pro Forma) Use Excel to create all solutions and submit an excel document for final HW submission. Use the mathematical functions within excel to make all calculations. Partial credit is only possible if functions are used in all calculable cells.

Note: For illustration purposes, all of this happened one after the other on the last day of 2020.

A company starts out by raising $7,000,000 from its equity partners and borrowing $7,000,000 at 5% indefinitely from its creditors. 30% of the total funds raised was used for working capital (other current assets besides cash).

1. Make the companys balance sheet.

The company bought machinery for $6,000,000 which had a depreciable life of 10 years (straight line).

2. Make the balance sheet.

Note: We will make an income statement projection (pro-forma) for 2021. When we have done this, we will flow what is in the income statement to the cash flow statement to the balance sheet to show that its balances

For 2021, the company generate sales of $6,000,000 and the cost of sales (aka cost of goods sold) is 50% of that.

3. Make the first portion of the income statement (Revenue, Cost, and Gross Margin)
4. We are going to assume SG&A is 0 so what you found in 3 is EBITDA (Earnings Before Interest Tax Depreciation and Amortization). That being said, take out depreciation to get EBIT(Earnings Before Interest Tax also known as Operating Income).
5. Take out interest and taxes, the tax rate is 21%. Find Net Income.