Please make a financial statement for this problem
1:27 X Excel Project-Financial Statement A... V This project is designed to serve the purposes of understanding financial statement analysis and ability to use Excel as a tool to conduct analysis of financial statement. Due date July 26th, 11:59 pm. No late submission To calculate the PE ratio you need to have a market price, I would like you find a company by yourself and provide information on which company and which trading day price you are using The calculation should use Excel function, not from your calculators. Excel Project-SUM20 (1) [Protected View] - Excel (Unlicensed Product) Jim Gutierr File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do R31 X D E F B G H K M N 2019 1 2018 2 3 4 Cash flow Statement Corporation 5 Assets Cash Accounts Receivable, Net Merchandise Inventory Supplies Prepaid Rent Total Current Assets $65,000 $150,000 $135,000 $9,700 $30,000 $389,700 $80,000 $100,000 $70,000 $500 $10,000 $260,500 6 7 8 9 $5,213 10 11 Property, plant, and Equipment: Equipment Less: Accumulated Depreciation - Equipme Total Property, Plant, and Equipment Total Assets 12 13 $500,000 ($80,000) $420,000 $8.09.200 Cash from Operting activities Net Income Adjustment to reconcile Net Income to net CF from OA Depreciation Loss on sale of equip Change in current opt and liab Change in AR Change in Inventories Change in Supplies Change in Prepaid Rent Change in AP Change in unearned revenue Change in salaries payable net cash flow from operating activites $240,000 1990,000) $150,000 SALONG 14 15 16 17 18 ($10,000) $10,000 $5,218 Liabilities and Stockholders' Equity Current Liabilities: Accounts Payable Unearned Revenue Salaries Payable Federal Income Taxes Payable 19 20 $150,000 $40,000 $40,000 $10,000 $40,000 550.000 $30,000 $10,000 21 22 23 Cash flow from investing activities Cash payments on equipments Cash received from sale of equipment Net cash from investing activities 51220.000 $ 54,787 S(165,213 Total Current Liabilities $240.000 $130.000 24 25 26 27 Long Term Liabilities Note Payable Total Long-Term Liabilities Cash FlowStatement $113,700 $130,000 $130,000 Horizontal Analysis Net cash flow from financing activities Cash received from issuing common stock Less: cash paid for dividends 0 0 Vertical Analysis Ratio Analysis Ready Type here to search RE " # hp Jim Gutierrez Excel Project-SUM20 (U) Protected View] Excel (Unlicensed Product) Insert Page Layout Formulas Data Review View Tell me what you want to do Help R31 N E D F A M $113,700 0 Note Payable Total Long-Term Liabilities Total tibilities $130,000 $130,000 $370,000 27 28 29 30 O $130,000 G H K Cash received from issuing common stock Less: Cash paid for dividends Net cash flow from financing activities Total change in cash Cash at beginning balance Cash at ending balance $113,700 09.05.2003 (546,300) 31 12 33 Stockholders' Equity Common Stock $1 Par Paid in Capital in Excess of Par Retained Earnings Total Stockholders' Equity Total liability and Stockholders' Equity $170,000 $43,700 $226,000 $4.19.700 $309.709 $100,000 SO $180.500 $280,500 $110.00 2 points each cell, Please make sure you use the Excel function to fill the blank 30 37 Comparative statement of income For the Years Ended December 31, 2018 and 2017 38 39 40 2019 2018 41 43 $700.000 $524,300 $175.200 $600,000 $120.000 $180.000 42 45 46 4T Sales Cost of Goods Sold Gross Margin Operating Expenses Salaries and Wages Expense Rent Expense Depreciation Expense Total Operating Expenses Income from Operations Loss on sale of Equipment interest Expense CashFlowStatement 49 50 51 52 $47,290 $20.256 $20,000 $87,546 S6,154 55.213 $3,656 $100,000 $20.000 St0.000 $130,000 $50,000 SO $5.000 Vertical Analysis Horizontal Ratio A Type here to search o R31 D F 19 B A $809,700 $410,500 Total liability and Stockholders' Equity 35 36 37 38 Comparative Statement of Income For the Years Ended December 31, 2018 and 2017 39 2019 2018 40 41 $700,000 $524,300 $175 700 $600,000 $420,000 $180,000 43 44 45 46 47 48 49 Sales Cost of Goods Sold Gross Margin Operating Expenses: Salaries and Wages Expense Rent Expense Depreciation Expense Total Operating Expenses Income from Operations Loss on Sale of Equipment Interest Expense Increase (Decrease) in Operating Income Income before Taxes Federal Income Taxes 30% Net Income 50 51 52 53 $47,290 $20,256 $20,000 $87,546 $88,154 ($5,213) $3,656 (58.869) $79,285 $23,786 $55,499 $100,000 $20,000 $10,000 $130,000 $50,000 SO $5,000 $5.000) $45,000 $13,500 $31.500 54 55 56 57 58 59 60 Cash FlowStatement Ratio Analysis Vertical Analysis Horizontal Analysis Ready - Type here to search o SG % File Home Insert Page Lay Formulas VOL L61 C D E G B A 2019 2018 1 2 3 4 Assets Cash Accounts Receivable, Net Merchandise Inventory Prepaid Rent Total Current Assets $75,000 $150,000 $130,000 $30,000 $385,000 $97,000 $100,000 $60,000 $10,000 $267,000 5 6 7 9 10 11 12 13 Property, Plant, and Equipment: Equipment Less: Accumulated Depreciation - Equipment Total Property, Plant, and Equipment Total Assets $435,000 ($80,000) $355,000 $740.000 $208,000 ($52,400) $155,600 $422.600 14 15 16 17 18 19 20 Liabilities and Stockholders' Equity Current Liabilities: Accounts Payable Dividends Payable Unearned Revenue Salaries Payable Federal Income Taxes Payable Total Current abilities $84,000 $0 $42,000 $50,000 $10,000 $185.000 $110,150 $0 $50,000 $33,000 $10,000 $203 150 21 22 23 24 25 26 27 Long Term Liabilities Note Payable Total Long Term Liabilities Total abilities $120,000 $120.000 $306,000 0 0 $203,150 Sheet1 Type here to search > a % 2 3 4 5 6 7 8 161 D E F B G 29 30 31 Stockholders' Equity: Common Stock, $1 Par Paid-In Capital in Excess of Par Retained Earnings Total Stockholders' Equity Total liability and Stockholders' Equity $163,000 $18,000 $253,000 $434,000 $740.000 $54,300 $18,000 $147,150 $219,450 S422,600 32 33 34 35 36 37 38 39 Comparative Statement of Income For the Years Ended December 31, 2019 and 2018 2019 2018 40 4 42 $1,200,000 $650,000 $550,000 $850,000 $500,000 $350,000 45 46 Sales Cost of Goods Sold Gross Margin Operating Expenses: Salaries and Wages Expense Rent Expense Depreciation Expense Total Operating Expenses Income from Operations Gain on Sale of Equipment Interest Expense Increase (Decrease) in Operating Income Income before Taxes Federal Income Taxes 30% Net Income $125,000 $25,000 $30,000 $180,000 $370,000 $4,500 58,000 $100,000 $25,000 $18,000 $143,000 $207.000 SO $6,500 48 50 51 53 54 55 $366,500 $109.950 $256,550 $200, 500 560,150 $100.359 Sheet O Type here to search OSC ? # $ % & 2 3 4 5 6 7 8 9 W E RT Y U Cash flow statement-class demo (1) [Protected Vie File Home Insert Page Layout Formulas Data Review View Help Tell me what you want t K67 fo A B D F G 41 $1,200,000 $650,000 $550,000 E $850,000 $500,000 $350,000 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 Sales Cost of Goods Sold Gross Margin Operating Expenses: Salaries and Wages Expense Rent Expense Depreciation Expense Total Operating Expenses Income from Operations Gain on Sale of Equipment Interest Expense Increase (Decrease) in Operating Income Income before Taxes Federal Income Taxes 30% Net Income $125,000 $25,000 $30,000 $180,000 $370,000 $4,500 $8,000 $3,500) $366,500 $109,950 $256.550 $100,000 $25,000 $18,000 $143,000 $207,000 $0 $6,500 {$6.500) $200,500 $60,150 $140.350 57 58 Extra information: 1 Purchase equipment for $242,000, 5122,000 paid in cash, the rest long-term note 59 2 Sold equipment with BV is $15,000, sold for $17.100 60 61 62 63 64 3 Cash paid for equipment $ 122.000 66 Sheet1 Type here to search O E hp 1:27 X Excel Project-Financial Statement A... V This project is designed to serve the purposes of understanding financial statement analysis and ability to use Excel as a tool to conduct analysis of financial statement. Due date July 26th, 11:59 pm. No late submission To calculate the PE ratio you need to have a market price, I would like you find a company by yourself and provide information on which company and which trading day price you are using The calculation should use Excel function, not from your calculators. Excel Project-SUM20 (1) [Protected View] - Excel (Unlicensed Product) Jim Gutierr File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do R31 X D E F B G H K M N 2019 1 2018 2 3 4 Cash flow Statement Corporation 5 Assets Cash Accounts Receivable, Net Merchandise Inventory Supplies Prepaid Rent Total Current Assets $65,000 $150,000 $135,000 $9,700 $30,000 $389,700 $80,000 $100,000 $70,000 $500 $10,000 $260,500 6 7 8 9 $5,213 10 11 Property, plant, and Equipment: Equipment Less: Accumulated Depreciation - Equipme Total Property, Plant, and Equipment Total Assets 12 13 $500,000 ($80,000) $420,000 $8.09.200 Cash from Operting activities Net Income Adjustment to reconcile Net Income to net CF from OA Depreciation Loss on sale of equip Change in current opt and liab Change in AR Change in Inventories Change in Supplies Change in Prepaid Rent Change in AP Change in unearned revenue Change in salaries payable net cash flow from operating activites $240,000 1990,000) $150,000 SALONG 14 15 16 17 18 ($10,000) $10,000 $5,218 Liabilities and Stockholders' Equity Current Liabilities: Accounts Payable Unearned Revenue Salaries Payable Federal Income Taxes Payable 19 20 $150,000 $40,000 $40,000 $10,000 $40,000 550.000 $30,000 $10,000 21 22 23 Cash flow from investing activities Cash payments on equipments Cash received from sale of equipment Net cash from investing activities 51220.000 $ 54,787 S(165,213 Total Current Liabilities $240.000 $130.000 24 25 26 27 Long Term Liabilities Note Payable Total Long-Term Liabilities Cash FlowStatement $113,700 $130,000 $130,000 Horizontal Analysis Net cash flow from financing activities Cash received from issuing common stock Less: cash paid for dividends 0 0 Vertical Analysis Ratio Analysis Ready Type here to search RE " # hp Jim Gutierrez Excel Project-SUM20 (U) Protected View] Excel (Unlicensed Product) Insert Page Layout Formulas Data Review View Tell me what you want to do Help R31 N E D F A M $113,700 0 Note Payable Total Long-Term Liabilities Total tibilities $130,000 $130,000 $370,000 27 28 29 30 O $130,000 G H K Cash received from issuing common stock Less: Cash paid for dividends Net cash flow from financing activities Total change in cash Cash at beginning balance Cash at ending balance $113,700 09.05.2003 (546,300) 31 12 33 Stockholders' Equity Common Stock $1 Par Paid in Capital in Excess of Par Retained Earnings Total Stockholders' Equity Total liability and Stockholders' Equity $170,000 $43,700 $226,000 $4.19.700 $309.709 $100,000 SO $180.500 $280,500 $110.00 2 points each cell, Please make sure you use the Excel function to fill the blank 30 37 Comparative statement of income For the Years Ended December 31, 2018 and 2017 38 39 40 2019 2018 41 43 $700.000 $524,300 $175.200 $600,000 $120.000 $180.000 42 45 46 4T Sales Cost of Goods Sold Gross Margin Operating Expenses Salaries and Wages Expense Rent Expense Depreciation Expense Total Operating Expenses Income from Operations Loss on sale of Equipment interest Expense CashFlowStatement 49 50 51 52 $47,290 $20.256 $20,000 $87,546 S6,154 55.213 $3,656 $100,000 $20.000 St0.000 $130,000 $50,000 SO $5.000 Vertical Analysis Horizontal Ratio A Type here to search o R31 D F 19 B A $809,700 $410,500 Total liability and Stockholders' Equity 35 36 37 38 Comparative Statement of Income For the Years Ended December 31, 2018 and 2017 39 2019 2018 40 41 $700,000 $524,300 $175 700 $600,000 $420,000 $180,000 43 44 45 46 47 48 49 Sales Cost of Goods Sold Gross Margin Operating Expenses: Salaries and Wages Expense Rent Expense Depreciation Expense Total Operating Expenses Income from Operations Loss on Sale of Equipment Interest Expense Increase (Decrease) in Operating Income Income before Taxes Federal Income Taxes 30% Net Income 50 51 52 53 $47,290 $20,256 $20,000 $87,546 $88,154 ($5,213) $3,656 (58.869) $79,285 $23,786 $55,499 $100,000 $20,000 $10,000 $130,000 $50,000 SO $5,000 $5.000) $45,000 $13,500 $31.500 54 55 56 57 58 59 60 Cash FlowStatement Ratio Analysis Vertical Analysis Horizontal Analysis Ready - Type here to search o SG % File Home Insert Page Lay Formulas VOL L61 C D E G B A 2019 2018 1 2 3 4 Assets Cash Accounts Receivable, Net Merchandise Inventory Prepaid Rent Total Current Assets $75,000 $150,000 $130,000 $30,000 $385,000 $97,000 $100,000 $60,000 $10,000 $267,000 5 6 7 9 10 11 12 13 Property, Plant, and Equipment: Equipment Less: Accumulated Depreciation - Equipment Total Property, Plant, and Equipment Total Assets $435,000 ($80,000) $355,000 $740.000 $208,000 ($52,400) $155,600 $422.600 14 15 16 17 18 19 20 Liabilities and Stockholders' Equity Current Liabilities: Accounts Payable Dividends Payable Unearned Revenue Salaries Payable Federal Income Taxes Payable Total Current abilities $84,000 $0 $42,000 $50,000 $10,000 $185.000 $110,150 $0 $50,000 $33,000 $10,000 $203 150 21 22 23 24 25 26 27 Long Term Liabilities Note Payable Total Long Term Liabilities Total abilities $120,000 $120.000 $306,000 0 0 $203,150 Sheet1 Type here to search > a % 2 3 4 5 6 7 8 161 D E F B G 29 30 31 Stockholders' Equity: Common Stock, $1 Par Paid-In Capital in Excess of Par Retained Earnings Total Stockholders' Equity Total liability and Stockholders' Equity $163,000 $18,000 $253,000 $434,000 $740.000 $54,300 $18,000 $147,150 $219,450 S422,600 32 33 34 35 36 37 38 39 Comparative Statement of Income For the Years Ended December 31, 2019 and 2018 2019 2018 40 4 42 $1,200,000 $650,000 $550,000 $850,000 $500,000 $350,000 45 46 Sales Cost of Goods Sold Gross Margin Operating Expenses: Salaries and Wages Expense Rent Expense Depreciation Expense Total Operating Expenses Income from Operations Gain on Sale of Equipment Interest Expense Increase (Decrease) in Operating Income Income before Taxes Federal Income Taxes 30% Net Income $125,000 $25,000 $30,000 $180,000 $370,000 $4,500 58,000 $100,000 $25,000 $18,000 $143,000 $207.000 SO $6,500 48 50 51 53 54 55 $366,500 $109.950 $256,550 $200, 500 560,150 $100.359 Sheet O Type here to search OSC ? # $ % & 2 3 4 5 6 7 8 9 W E RT Y U Cash flow statement-class demo (1) [Protected Vie File Home Insert Page Layout Formulas Data Review View Help Tell me what you want t K67 fo A B D F G 41 $1,200,000 $650,000 $550,000 E $850,000 $500,000 $350,000 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 Sales Cost of Goods Sold Gross Margin Operating Expenses: Salaries and Wages Expense Rent Expense Depreciation Expense Total Operating Expenses Income from Operations Gain on Sale of Equipment Interest Expense Increase (Decrease) in Operating Income Income before Taxes Federal Income Taxes 30% Net Income $125,000 $25,000 $30,000 $180,000 $370,000 $4,500 $8,000 $3,500) $366,500 $109,950 $256.550 $100,000 $25,000 $18,000 $143,000 $207,000 $0 $6,500 {$6.500) $200,500 $60,150 $140.350 57 58 Extra information: 1 Purchase equipment for $242,000, 5122,000 paid in cash, the rest long-term note 59 2 Sold equipment with BV is $15,000, sold for $17.100 60 61 62 63 64 3 Cash paid for equipment $ 122.000 66 Sheet1 Type here to search O E hp
