Question
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
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).
- Make the companys balance sheet.
The company bought machinery for $6,000,000 which had a depreciable life of 10 years (straight line).
- 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.
- Make the first portion of the income statement (Revenue, Cost, and Gross Margin)
- 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).
- Take out interest and taxes, the tax rate is 21%. Find Net Income.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started