File View Home Insert Page Layout Formulas Data Review Cut Calibri - 12 - === E Copy * Format Painter B I U - - - A- === Wra E Mer Paste Clipboard Font Alignment C4 : X V fix 1 Project 2 2 Statement of cash flow and ratio analysis 4 Objectives: 5 Demonstrate an understanding of properly formatted financial statements. 6 Demonstrate proficiency in using Excel formulas. Please carefully read the steps below and follow the instruction to complete 8 the project 9 Before you start the project, please download the excel file on your computer hard drive, and save into a location that you will remember, renamed as "ACCG211_excel2_yournname". "Your name" should be 10 replaced with your first name and last name with no space. 11 12 Part 1: Statement of Cash flow 1. Review concept of statement of cash flow and classification of operating activity, investment activity and financing activity. (see tutorial posted in 13 D2L). 14 2. Additional information: 15 1) There is equipment purchase cost of $70,000 by cash during year. 16 2) There is one equipment sale, orginal cost was $20,000. 17 3) There is new common stock issue during year. 18 4) Dividend decleared and are all paid during the year. 19 3. Set up a blank work sheet named as "worksheet". 1) calculate the accumulative depreciation from the sale of the 20 equipment 21 2) calcualte the cash provided from the sale of the equipment 22 3 ) calculate the total proceeds from the stock issurance 23 4) calcualte the dividend paid for the year 4. Set up the Statement of cash flow using Indirect mothod on a separate tab named as "Cashflow". (see reference material). Please use the standard format for a statement of cash flow by seperating each activity and reconcile 24 the cash balance. 5. Numbers should be linked from the original FS, or from your calculation worksheet by a formular. The calculations for all subtotals and totals should 25 be formula based, where possible use the summation feature of Excel. 26 27 Part 2: Ratio analysis 28 1. See "Ratio"tab. 2. Use "current asset" as the example to complete the reminder ratio calculation. Make sure all number should be either linked from FS, or a formular calculated. Number should be formated to "accounting" with no $ 29 sign, and no decimal. 30 For the year of 2020 2 Current ratio: Current assets Divided by: Current liabilities 3 228,300 28,100 6 Current ratio (1 decimal place) 8.1 Acid-test ratio: 9 10 11 12 13 Acid-test ratio (1 decimal place) 14 15 16 Accounts receivable turnover ratio: 17 18 19 21 Accounts receivable turnover ratio (1 decimal place) 22 23 24 Inventory turnover ratio: 26 28 Inventory turnover ratio (1 decimal place) 29 30 31 Days' sales uncollected: 32 33 34 35 36 Days' sales uncollected (1 decimal place) 37 38 Days' sales in inventory: 42 Days' sales in inventory (1 decimal place) 43 44 45 Total asset turnover: 47 48 49 Total asset turnover (1 decimal place) Instruction Cash Flow Worksheet Ratio FS B C D E F G H VON UN- Startup Company Comparative Balance Sheet As of December 31, 2019 and December 31, 2020 2020 2019 9 Assets 10 Cash 11 Accounts receivable 12 Inventory 13 Prepaid insurance 14 Prepaid rent 15 Total Current Assets 75,000 20,300 130,000 2,000 1,000 228,300 48,400 15,000 120,000 900 900 185,200 17 Propety, Plant, and Equipment: 18 Equipment 19 Less: Accumulated depreciation 20 Total Property, Plant, and Equipment 21 22 Total Assets 128,000 141,000) 87,000 78,000 134,000) 44,000 315,300 229,200 24 Liabilities and Equity: 25 Accounts payable 26 Wages payable 27 Income taxes payable 28 Total current liabilities 19,900 6,000 2,200 28,100 16,600 5,000 2,100 23,700 29 30 Long-term Liabilities: 31 Notes Payable 50,000 87,000 33 Total Liabilities 78,100 110,700 53,900 50,000 35 Equity: 36 Common stock, $1 par value 37 Paid-in-capital in excess of par value, common 38 Retained earnings 39 Total equity 40 41 Total Liabilities and Equity 95,900 237,200 38,500 118,500 315,300 315,300 229,200 229,200 Startup Company Income Statement For the period ended December 31, 2020 49 Net sales 50 Cost of goods sold 51 Gross Profit 800,000 400,000 400,000 52 53 Operating expenses: 54 Wages expense 55 Rent expense 56 Advertising expense 57 Repairs & maintenance expense 58 Depreciation expense 59 Miscellaneous expense 60 Total operating expenses 120,000 12,000 10,000 10,000 25,000 10,000 187,000 61 213,000 62 Operating Profit 63 Less: Miscellaneous Expenses 64 Interest Expense 65 Gair(Loss) on sale of equipment 66 Pre-tax profit 67 Income tax expense 68 Net income (20,000) 3,000 196,000 68,600 127,400 69 > Instruction Cash Flow Worksheet Ratio FS Road