Income statement 2. Company A and Company B have taken different approaches to selling glassware. Company A has decided to make glass stemware that sells for a premium at $16.98 per glass Company B has decided to make a less expensive glassware (without stems) that sells for $11.98 per glass. Last year Company A sold 847,000 stemmed glasses; Company B sold 1,388,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 EBIT, higher taxable income, higher net income, and higher OCF. b. Company B is 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 spreadsheet, redo the income statement for this scenario. Does it improve net 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 piece of information, and then construct the income statement by using a formula or cell reference for each individual line of the statement 5 Given Data 11 12 23 34 Information Units sold Revenue per unit Cost per unit of glassware Fixed costs Selling general, and administrative expenses Depreciation expense Interest expense Tarte $ $ $ S $ $ Company A (stem ware) 847,000 16.98S 8.17 15 1,245,788.00 S 785,038,00 5 1,489,374.00 501,030.00 37.5% Company B Company B (without stems) (upgrading quality 1,388,000 1,179,800 11.98 14.98 6.695 7.89 1,354,218.00 S 1,354,218.00 584,431.00 s 1,168,862.00 1,137,890,00 1,137,890,00 698,540.00 s 698,540.00 37.5% 37.5% 15 16 Solution TE 19 Parts a and b Company A Parta Company B Part b Company B 23 22 34 Income Statement Revenue Cost of goods sold Grous margin or profit Fixed costs Selling general, and administrative expenses Depreciation expense EBIT Interest expense Taxable income Taxes Net income Operating cash flow (OCF) $ S S 1,245,788.00 785,038.00 $ 1,489,374,00 $ 1.354,218.00 $ 584,431.00 1,137,890.00 $ 1.354,218.00 1,168,862.00 1.137,890,00 2 20 3 . Given the information about each firm, construct an income statement for the two companies and see which company had the higher gross martin 2.1 og 3 Format H 1 C D G TV TUUSUI YUUN SUI), EDIT, ICE LICUIT, THIETTE TIETTICUM, NICI OCE Company B has EBIT, taxable income, and net income, but it has gross margin and OCF b. Company B is 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? Company B has EBIT, taxable income, net income, gross margin, and OCF after upgrading the glassware quality Requirements 1. Start Excel Points 0 2. 1 In cell F21, by using cell references, calculate the sales revenue for company A. Use the relevant cells from the Given Data section Tn cell F22, by using cell references, calculate the cost of goods sold for comapny A. Use the relevant cells from the Given Data 3. section 1 1 1 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, 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 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 F37 and F28. In cell F30, by using cell references, calculate the income taxes for company A. Use cell F29 and the relevant cell from the Given 6. 1 10 1 Data section 8. 1 51 1 1 50 1 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. Uso cells F26, F28, and FJI 11. In cell range G213032, by using cell references, construct the income Matements for company B. Copy the contents from cell range F21:F32 and paste to the cell range G21:32 for company Bunder the situation described in part a and H21:32 in part b. 12 In celle D36 and G36, type either higher or lower by comparing the accounting items of the two companies under the situation described in parta In cell D40, type either Wicher or lower by comparing the accounting items of the two companies under the situation described in part 14. Save the workbook. Close the workbook and then exit Excel Submit the workbook as directed 1 13. 1 0