Consider the following simplified financial statements for the Wesney Corporation (assuming no income taxes):
Income Statement
Balance Sheet
Sales
$ 39,600
Assets
$ 23,800
Debt
$ 6,800
Costs
31,800
Equity
17,000
Net income
$ 7,800
Total
$ 23,800
Total
$ 23,800
The company has predicted a sales increase of 20 percent. Assume the company pays out half of net income in the form of a cash dividend. Costs and assets vary with sales, but debt and equity do not.
Prepare the pro forma statements. (Input all amounts as positive values. Do not round intermediate calculations and round your answers to the nearest whole number, e.g., 32.)
How can I create An excel spreadsheet to help me do this problem for school?
Hello Leslie!
Thanks for your question. According to your dataset, I have created the calculation for Pro Forma year 1. You can check the image below, and the Excel file is attached to the reply.
I have followed the below steps:
Sales for Year 1 (Pro Forma Year 1):
Calculate the 20% increase in sales for Year 1: Sales for Year 1 = Previous Year Sales + (Previous Year Sales * 20%)
Costs for Year 1 (Pro Forma Year 1):
Calculate the 20% increase in costs for Year 1: Costs for Year 1 = Previous Year Costs + (Previous Year Costs * 20%)
Net Income for Year 1 (Pro Forma Year 1):
Net Income for Year 1 = Sales for Year 1 - Costs for Year 1
Assets for Year 1 (Pro Forma Year 1):
Assuming that assets vary with sales, calculate the change in assets:
Assets for Year 1 = Previous Year Assets + Change in Assets for the previous year
Debt and Equity for Year 1 (Pro Forma Year 1):
Debt and equity remain unchanged from the previous year.
Dividends for Year 1 (Pro Forma Year 1):
Calculate dividends as half of the Net Income for Year 1: Dividends for Year 1 = Net Income for Year 1 / 2
If you have any further queries, please inform me in the reply.
I still cant figure out the Equity and total debt and equity
And what the external financing needed. I entered the answers on the this excel and they were incorrect.
What am i doing wrong?
Hello Leslie!
The basic balance sheet equation is: Assets = Liabilities + Equity
When incorporating net income and dividends, you need to consider their effects on equity: Assets = Liabilities + (Equity + Net Income - Dividends)
You must make both side of same value else the balance sheet equation won't be satisfied.
Here, you may see that the left and right side value is not same. So, to make it equal you will need external financing.
The difference between the right side and the left side is $80. So, you have to put $80 as external financing.
Now, the balance sheet equation is correct. Try this. I hope your problem will be solved.
The most recent financial statements for Camryn, Incorporated, are shown here (assuming no income taxes):
Income Statement
Balance Sheet
Sales
$ 8,400
Assets
$ 14,000
Debt
$ 6,000
Costs
6,390
Equity
8,000
Net income
$ 2,010
Total
$ 14,000
Total
$ 14,000
Assets and costs are proportional to sales. Debt and equity are not. No dividends are paid. Next year’s sales are projected to be $9,996. What is the external financing needed? (Do not round intermediate calculations and round your answer to 2 decimal places, e.g., 32.16.)
Hello Leslie,
Here is the solution to your problem. I have increased the cost and asset value proportionately with the increase in sales.
Then, I have added the net income with debt and equity, and the sum of debt, equity, and net income is $16,392, but the asset is $16660. so I have to add external financing of $268 to balance the equation.
