Please answer in excel A
Balance Sheets 2018 2019
Cash 50,000 40,000
Accounts Receivables 200,000 260,000
Inventories 450,000 500,000
Total Current Assets 700,000 800,000
Fixed Assets, Net 400,000 500,000
Total Assets 1,100,000 1,300,000
Accounts Payable 130,000 170,000
Accruals 50,000 70,000
Bank Loan 90,000 90,000
Total Current Liabilities 270,000 330,000
Long-Term Debt 300,000 400,000
Common Stock ($10 par) 300,000 300,000
Capital Surplus 50,000 50,000
Retained Earnings 180,000 220,000
Total Liab. & Equity 1,100,000 1,300,000
2018 2019
Net Sales 1,400,000 1,600,000
Cost of Goods Sold 780,000 900,000
Gross Profit 620,000 700,000
Marketing 130,000 150,000
General & Administrative 150,000 150,000
Depreciation 40,000 53,000 40,000 53,000 0
EBIT 300,000 347,000
Interest 45,000 57,000
Earnings Before Taxes 255,000 290,000
Income Taxes (40%) 102,000 116,000
Net Income 153,000 174,000
Dividends 134000
Cash Flow from Operations 2019
Operating:
Net Income 174,000
+Depreciation 53,000
-Inc. in Accts. Receivable
-Inc. in Inventories
+Inc. in Accts. Payable
+Inc. in Accruals
CF from Operations
Investing:
-Inc. in Gross Fixed Assets
Financing:
+Increase in Bank Loans
+Increase in Long-Term Debt
Cash Dividends Paid
Cash from Financing Activities
Change in Cash Balance
Cash Beg Period
Change in Cash
Cash at End period
Annual Cash (Burn) or Build
Monthly (Burn) or Build Rate
A
B
C
D
E
F
G
H
1 2 A 3 Balance Sheets 4 5 Cash 6 Accounts Receivables 7 Inventories 8 Total Current Assets 6 Fixed Assets, Net 10 Total Assets 11 12 Accounts Payable 13 Accruals 14 Bank Loan 15 Total Current Liabilities 16 Long-Term Debt 17 Common Stock ($10 par) 18 Capital Surplus 19 Retained Earnings 20 Total Liab. & Equity 21 22 23 24 25 Net Sales 26 Cost of Goods Sold 27 Gross Profit 28 Marketing 29 General & Administrative 30 Depreciation 31 EBIT 32 Interest 33 Earnings Before Taxes 34 Income Taxes (40%) 35 Net Income 36 Dividends 37 38 Cash Flow from Operations 39 Operating: 40 Net Income 41 +Depreciation 42 -Inc. in Accts. Receivable 43 -Inc. in Inventories 44 +Inc. in Accts. Payable 45 +Inc. in Accruals 46 CF from Operations 47 Investing: 48 -Inc. in Gross Fixed Assets 49 Financing: 50 +Increase in Bank Loans 51 +Increase in Long-Term Debt 52 Cash Dividends Paid 53 Cash from Financing Activities 54 55 Change in Cash Balance 56 57 Cash Beg Period 58 Change in Cash 59 Cash at End period 60 61 62 Annual Cash (Burn) or Build 63 Monthly (Burn) or Build Rate
2018
2019
50,000 200,000 450.000 700,000 400,000 1,100,000
40,000 260,000 500,000 800,000 500,000 1,300,000
130,000 50,000 90,000 270,000 300,000 300,000 50,000 180,000 1,100,000
170,000 70,000 90,000 330,000 400,000 300,000 50,000 220,000 1,300,000
2018
2019
1,400,000 780.000 620,000 130,000 150,000 40,000 300,000 45,000 255,000 102,000 153,000
1,600,000 900,000 700,000 150,000 150,000 53,000 347,000 57,000 290,000 116,000 174,000 134000
40,
2019
174,000 53,000
