FILL IN WITH CELL FORMULA, not just answer
Income statement a. Company And Company B bave taken datferent approaches to selling glisswure. Company A has decided to make glass stenware that sells for a premium at $16.98 per glass. Company has decided to make a less expensive glassware (without stems that sells for $11.95 per glass. Last year Company A sold 547.000 stemmed glasses Company sold 1,348,000 glasses. Given the following additional information about each firm, construct an income statement for each company, and see which company had the higher gross margin revenue minus cost of goods sold), higher tarr, higher taxable incaric, ligher net income, and higher OCE b. Company Bis thinking of upgrading the quality of its glassware Doing so will cause its cost per unit to rise by 18% but it believes it can raise its price by 25% Its sales volume will fall by 15%(85% of its current volume). However, letting the market know that it has better quality glassware will require a doubling of the current selling.general, and administrative expenses. Using a speedsheet, redo the income statement for this scenario. Does it improve ne income and operating cash flow for Company B! How does the company now compare to Company A? Required: In the spreadsheet, use a separate cell for each pece of information, and the construct the income statement by using a formula or cell redecence for each individual line of the statement. Given Data Company A istemware) Company B without stems) 10 11 12 1a 14 15 16 17 Information Inits sald Revenue per unit Cost per unit of glassware Fixed costs Sellin, general, and administrative AS Depressione pense Intussen Tax rate 1847000 16.98 8.17 1245 THN 785038 1489374 501010 0.375 1.3&RNO 11.98 6.69 1354215 584431 1137890 698540 10.375 Company B (upgrdiaz qunlity) -G90.85 -G10*1.25 -G111.18 1 -C12 G2 -G14 -GIS 1-G16 Solution Part 1A 19 20 21 22 *** Parts and Company A Parta Company B Company B -HI2 Income Statement Revenue Cost sold Or good Ciros mergin ar profil Fireless Selling general.zod administrative expS Depreciation expense EST Interest expense Taxable income Taxes Net income Upening cash flow OCH -FI2 -F13 -F14 GI2 G13 314 34 25 20 27 2A 39 30 91 32 13 -1114 1. Given the information sout each fin, construct an income statement for the two companies, and see which company had the higher gross mergin (revenue minus cast of goods sold), higher EBIT, ligler taxable income, higher net income, and higherOCF. 35 16 27 Company Bhas FRIT, lacable income, and net income, but it has gross marginand CF b. Company Bis thinking of upgrading the quality of its glassware. Does it improve net income and operating cash flow for Company B! How does the company now compare to Company A? 38 39 40 Company has LEBIT, taxable income, net income, mos mergin, and 11 4 42 43 Points Requirements 1. Start Excel. 44 0 2. 45 In cell F21, by using cell references, calculate the sales revenue for company A. Use the relevant cells from the Given Data section. 1 In cell F22, by using cell references, calculate the cost of goods sold for comapny A. Use the relevant cells from the Given Data section. 3. 46 1 47 Note: The output of the expression you typed in this cell is expected as a positive number. 4. In cell F23, by using cell references, calculate the gross margin or profit for company A. Use cells F21 and F22. 5. In cell F27, by using cell references, calculate the EBIT for company A. Use cells F23, F24, F25, and F26. 1 48 1 In cell F28, by using a cell reference, enter the amount of the interest expense for company A. Refer to the relevant cell from the Given Data section. 6. 49 1 Note: The output of the expression you typed in this cell is expected as a positive number. 7. In cell F29, by using cell references, calculate the taxable income for company A. Use cells F27 and F28. 50 1 In cell F30, by using cell references, calculate the income taxes for company A. Use cell F29 and the relevant cell from the Given Data section. 8. 51 1 52 Note: The output of the expression you typed in this cell is expected as a positive number. 9. In cell F31, by using cell references, calculate the net income for company A. Use cells F29 and F30. 10. In cell F32, by using cell references, calculate the operating cash flow (OCF) for company A. Use cells F26, F28, and F31. 1 53 1 11. 54 In cell range G21:H32, by using cell references, construct the income statements for company B. Copy the contents from cell range F21:F32 and paste to the cell range G21:G32 for company B under the situation described in part a and H21:H32 in part b. 1 12. 55 In cells D36 and G36, type either higher or lower by comparing the accounting items of the two companies under the situation described in part a. 1 56 1 13. In cell D40, type either higher or lower by comparing the accounting items of the two companies under the situation described in part b. 14. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 57 0 58 59 60 61 62 63 64 Income statement a. Company And Company B bave taken datferent approaches to selling glisswure. Company A has decided to make glass stenware that sells for a premium at $16.98 per glass. Company has decided to make a less expensive glassware (without stems that sells for $11.95 per glass. Last year Company A sold 547.000 stemmed glasses Company sold 1,348,000 glasses. Given the following additional information about each firm, construct an income statement for each company, and see which company had the higher gross margin revenue minus cost of goods sold), higher tarr, higher taxable incaric, ligher net income, and higher OCE b. Company Bis thinking of upgrading the quality of its glassware Doing so will cause its cost per unit to rise by 18% but it believes it can raise its price by 25% Its sales volume will fall by 15%(85% of its current volume). However, letting the market know that it has better quality glassware will require a doubling of the current selling.general, and administrative expenses. Using a speedsheet, redo the income statement for this scenario. Does it improve ne income and operating cash flow for Company B! How does the company now compare to Company A? Required: In the spreadsheet, use a separate cell for each pece of information, and the construct the income statement by using a formula or cell redecence for each individual line of the statement. Given Data Company A istemware) Company B without stems) 10 11 12 1a 14 15 16 17 Information Inits sald Revenue per unit Cost per unit of glassware Fixed costs Sellin, general, and administrative AS Depressione pense Intussen Tax rate 1847000 16.98 8.17 1245 THN 785038 1489374 501010 0.375 1.3&RNO 11.98 6.69 1354215 584431 1137890 698540 10.375 Company B (upgrdiaz qunlity) -G90.85 -G10*1.25 -G111.18 1 -C12 G2 -G14 -GIS 1-G16 Solution Part 1A 19 20 21 22 *** Parts and Company A Parta Company B Company B -HI2 Income Statement Revenue Cost sold Or good Ciros mergin ar profil Fireless Selling general.zod administrative expS Depreciation expense EST Interest expense Taxable income Taxes Net income Upening cash flow OCH -FI2 -F13 -F14 GI2 G13 314 34 25 20 27 2A 39 30 91 32 13 -1114 1. Given the information sout each fin, construct an income statement for the two companies, and see which company had the higher gross mergin (revenue minus cast of goods sold), higher EBIT, ligler taxable income, higher net income, and higherOCF. 35 16 27 Company Bhas FRIT, lacable income, and net income, but it has gross marginand CF b. Company Bis thinking of upgrading the quality of its glassware. Does it improve net income and operating cash flow for Company B! How does the company now compare to Company A? 38 39 40 Company has LEBIT, taxable income, net income, mos mergin, and 11 4 42 43 Points Requirements 1. Start Excel. 44 0 2. 45 In cell F21, by using cell references, calculate the sales revenue for company A. Use the relevant cells from the Given Data section. 1 In cell F22, by using cell references, calculate the cost of goods sold for comapny A. Use the relevant cells from the Given Data section. 3. 46 1 47 Note: The output of the expression you typed in this cell is expected as a positive number. 4. In cell F23, by using cell references, calculate the gross margin or profit for company A. Use cells F21 and F22. 5. In cell F27, by using cell references, calculate the EBIT for company A. Use cells F23, F24, F25, and F26. 1 48 1 In cell F28, by using a cell reference, enter the amount of the interest expense for company A. Refer to the relevant cell from the Given Data section. 6. 49 1 Note: The output of the expression you typed in this cell is expected as a positive number. 7. In cell F29, by using cell references, calculate the taxable income for company A. Use cells F27 and F28. 50 1 In cell F30, by using cell references, calculate the income taxes for company A. Use cell F29 and the relevant cell from the Given Data section. 8. 51 1 52 Note: The output of the expression you typed in this cell is expected as a positive number. 9. In cell F31, by using cell references, calculate the net income for company A. Use cells F29 and F30. 10. In cell F32, by using cell references, calculate the operating cash flow (OCF) for company A. Use cells F26, F28, and F31. 1 53 1 11. 54 In cell range G21:H32, by using cell references, construct the income statements for company B. Copy the contents from cell range F21:F32 and paste to the cell range G21:G32 for company B under the situation described in part a and H21:H32 in part b. 1 12. 55 In cells D36 and G36, type either higher or lower by comparing the accounting items of the two companies under the situation described in part a. 1 56 1 13. In cell D40, type either higher or lower by comparing the accounting items of the two companies under the situation described in part b. 14. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 57 0 58 59 60 61 62 63 64
