1 Create formulas wherever possible in the income statement. Overwrite numbers with formulas as necessary. Try to maintain the formatting exactly. 2 Create formulas wherever possible in the balance sheet. Overwrite numbers with formulas as necessary. Try to maintain the formatting exactly. 3 Create a common-size income statement for 2019 and 2020. This statement should remain on a separate worksheet with all formulas linked directly to the income statement. Remember to follow Best Practices for Spreadsheet Models as outlined in Chapter 1. 4 You will find a simple outline of a Statement of Cash Flows. You will need to complete the statement for 2020 by inserting/deleting rows for your line items as necessary. Do not enter any numbers directly on this worksheet. All formulas should be linked directly to the source on previous worksheets. 5 Remember to check your net change in cash on the SCF against the net change in cash on the balance sheet. D A B 1 Rodney the Ram Recreations Co. 2 Income Statements 3 For the Years 2019 and 2020 4 2020 5 Sales $369,300 6 Cost of Goods 285,400 7 Gross Profit 83,900 8 Depreciation 26,820 9 Selling & Admin. Expense 23,340 10 Other Operating Expense 1,080 11 Net Operating Income 32,660 12 Interest Expense 7,685 13 Earnings Before Taxes 24,975 14 Taxes 9,990 15 Net Income $14,985 2019 $354,000 281,800 72,200 26,360 21,820 1,080 22,940 7,505 15,435 6,174 $9,261 16 19 17 Notes: 18 Tax Rate Shares 20 Earnings per Share 21 22 40.00% 52,100 $0.29 40.00% 52,100 $0.18 D D 2019 $10,300 $550 $42,100 $48,490 $101,440 $352,600 $51,200 $301,400 $402,840 A B 2 Balance Sheet 3 For the Year Ended December 31, 2020 4 2020 5 Assets 6 Cash $14,714 7 Marketable Securities $1,841 & Accounts Receivable $41,090 9 Inventory $46,910 10 Total Current Assets $104,555 11 Gross Fixed Assets $388,000 12 Accumulated Depreciation $78,020 13 Net Plant & Equipment $309,980 14 Total Assets $414,535 15 16 Liabilities & Owner's Equity 17 Accounts Payable $35,200 18 Accrued Expenses $2,850 19 Total Current Liabilities $38,050 20 Long-term Debt $152,700 21 Total Liabilities $190,750 22 Common Stock ($1.00 par) $52,100 23 Additional Paid-in-Capital $121,500 24 Retained Earnings $50,185 25 Total Owner's Equity $223,785 26 Total Liab. & Owner's Equity $414,535 27 28 $32,700 $2.740 $35,440 $ 158,600 $194,040 $52,100 $121,500 $35,200 $208,800 $402,840 C D E G K M 1 Name Box ney the Ram Recreations Co. 2 Statement of Cash Flows 3 For the Year Ended December 31, 2020 4 Cash Flows from Operations 5 6 7 8 Total Cash Flows from Operations 9 Cash Flows from Investing 10 Direction of Change Increase Direction of Change Decrease Order of Subtraction Type of + 11 Item Asset Liability or Equity Older - Newer Newer - Older + 12 13 Total Cash Flows from Investing 14 Cash Flows from Financing 15 16 17 18 Total Cash Flows from Financing 19 Net Change in Cash Balance 20 21 22 Check answer against Balance Sheet 23 Beginning Cash From Balance Sheet 24 Ending Cash From Balance Sheet 25 Net Change in Cash Balance 26