Please Help, I\'ve been working on various sheets for over three days now, and t
ID: 2777978 • Letter: P
Question
Please Help, I've been working on various sheets for over three days now, and this one I'm a good 12 hours in - and want to cry.. [chuckle chuckle] I am waving a white flag.
---------------------------------------------------------------------------------------------
You have been assigned to prepare pro forma Income Statements and Balance Sheet for 2015.
You will use the information listed below and the Income Statement, Balance Sheet and ratios from Part 1.
Forecast financial information for 2015:
-Sales estimates for 2015 have been broken into three likely outcomes: decrease 1%, increase 5% (most likely), increase 8%. You will need to prepare an Income Statement for each level of sales forecast. All other information provided below remains the same.
-COGS is forecast to have fixed costs of $850,000 and variable costs that are 35% of sales
-Selling expenses have fixed costs of $55000 and variable costs of 5% of sales
-G&A have fixed costs of $45000 and variable costs that are 2% of sales
-Interest is forecast to increase to $17450
-Cash & Equivalents are expected to be $125759
-Receivables will increase 1%
-Inventory will increase to $315675
-A new machine will be purchased for $300000 and Depreciation on the new machine for the year will be $15000
-Accumulated depreciation on old machines will increase $22500
-Payables are expected to increase 2%
-Notes payable will be $22500
-Accruals will be $107350
Long-Term Debt will be $425000
-The value of outstanding common stock will remain at the same levels as the prior year
-The tax rate will remain at 40%
-The company plans to pay $30500 in total dividends to common shareholders over the course of the year.
In preparing the Balance Sheet, calculate the retained earnings using the Income Statement that reflects a 5% increase in sales as that is considered the most likely given the current economic conditions.
Using the forecasted data, adjust the long-term debt to reflect any external financing requirement. Use this information to calculate the forecasted 2015 ratios.
Financial Information
PART 1 - INFORMATION:
Extra Credit (5 points): Do a projected cash flow statement for 2015. Discuss what you think this indicates for the outlook for the company from a cash basis.
Explanation / Answer
The proforma Income statement is hereby presented
Regarding the Balance Sheet, there are smoe concerns
1. The Net fixed assets should be the Total Fixed Assets - Accumulated Depreciation. Here the presentation is in a different way. The Net Fixed Assets and Total Assets are looking same which is not possible. Also, it seems that the effect of deprecaiation is not reflected in the profit. hence, the Balance Sheet is not prepared. Please check if the information provided is correct and let know if any changes are there.
Thanks
Particulars 2015.00 increase 5% decrease 1% increase 8% Sales 1887480.00 1981854 1868605 2038478 Less : COGS Fixed 850000.00 850000 850000 850000 variable 660618.00 693648.9 654011.75 713467.3 Gross Profit 376862.00 438205.1 364593.25 475010.7 less : OP expenses Fixed 55000.00 55000 55000 55000 variable 94374.00 99092.7 93430.25 101923.9 G&A Fixed 45000.00 45000 45000 45000 variable 37749.60 39637.08 37372.1 40769.56 Operating Profit 144738.40 199475.32 133790.9 232317.24 less : interest 17450.00 17450 17450 17450 Net Profit before tax 127288.40 182025.32 116340.9 214867.24 Less : tax @ 40% 50915.36 72810.128 46536.36 85946.896 Net Profit after tax 76373.04 109215.192 69804.54 128920.344 less: common stock dividend 30500.00 30500 30500 30500 To retained Earnings 45873.04 78715.192 39304.54 98420.344