help in Excel

profilehelp-science1
20201021185114fsa__assignment_pro_formas__canvas__.xls
Home>Mathematics homework help>help in Excel

Sheet1

Pro-forma financial statements Fall 20 ANTIA
Create pro forma financial statements from the information provided below
Income Statement Year 1
Year 0 Year 1 Year 2 Sales revenues increase 3.0%
Revenues 17,000 Gross margin is 48.5%
Cost of goods sold 9,200 SG&A decreases by 2.5%
Gross profit 7,800 $3000 of PP&E is purchased on January 1,
SG&A 4,790 New PP&E is depreciated over 10 years
Depreciation 1,700 Inventory grows at the same rate as the growth in COGS
Operating Profit 1,310 Assume that all other asset accounts grow at the same rate as sales.
Interest expense 155 Accounts Payable grow at the same rate as COGS
Income before taxes 1,155 Accrued and deferred income taxes grows at the same rate as tax expense.
Taxes @35% 404 Long-term debt increases by $1500
Net Income 751 Unless otherwise stated, liability accounts grow at the same rate as sales
Treasury Stock purchases equal $300
Dividends 188 Average interest cost of all interest bearing debt is 2.5%
Dividend payout ratio is 20%
Addition to retained earnings 563 Tax rate is 35%
Funding requirements should be financed with short-term debt
Balance Sheet
Assets
Year 0 Year 1 Year 2 Year 2
Cash and cash equivalents 640 Sales revenue decline by 1.5%
Marketable securities 28 Gross margin increases to 50%
Accounts Receivables 8,200 Inventory grows in line with COGS
Inventory 3,142 SG&A increases by 1.5%
Prepaid expen. & other assets 1,323 $800 of PP&E(net) is sold on January 1 for $800 cash. (Gross =$1000, Accumulated depreciation = $200)
Total Current Assets 13,333 Annual depreciation expense declines by $ 60
Assume that all other asset accounts change at the same rate as sales.
Plant property and equipment (gross) 7,607 Accounts Payable grow change at the same rate as COGS
Accumulated Depreciation 3,000 Long-term debt declines by $150
PP&E (net) 4,607 Accrued and deferred income taxes change at the same rate as tax expense.
Unless otherwise stated, liability accounts change at the same rate as sales.
Total Assets 17,940 Treasury Stock purchase is $200.
Average interest cost of all interest bearing debt is 2.1%
Liabilities & Shareholders' Equity Dividend payout ratio changes to 22%
Year 0 Year 1 Year 2 Tax rate is 35%
Accounts payable 3,148 200 shares of $1 par value common stock is issued for $800.
Loans & notes payable (plug) 2,423 Excess cash is used to retire short-term debt
Accrued income taxes 1,322 Do not add significant amounts to cash unless Loans & notes payable is drawn down to zero.
Total Current Liabilities 6,893
Long-term debt 2,800
Defered income taxes 195
Shareholders' Equity
Common Stock at par 860
Capital Surplus 863
Retained earnings 6,429
Less treasury stock (100)
Total equity 8,052
Total liabilities & shareholder equity 17,940
Statement of Retained Earnings Year 1 Year 2
Beginning retained earnings
+Net Income
-dividends
Ending retained earnings
Statement of Cash Flows
Year 1 Year 2
Net Income
+ Depreciation
+ (increase) decrease in A.R.
+ (increase) decrease in inventory
+ (increase) decrease in prepaid exp.
+ increase (decrease) in A.P.
+ increase (decrease) in accrued taxes
+ increase (decrease) in deferred taxes
=Cash Flow from operations
+ (increase) decrease in marketable sec.
+ (increase) decrease in PPE
=Cash Flow from investing
+ increase (decrease) in loans and notes.
+ increase (decrease) in LTD
+ increase (decrease) in common stock
- dividends
- treasury stock
=Cash flow from financing
Beginning cash
+Change in cash \
Ending cash
MJA: