wutoSave OF Whited Brooks Chapter 21 Start - Read-Only - Excel Search le Home Insert Draw Page Layout Formulas Data Review View Help leshor nction A @ AutoSum Recently Financial logical Test Date & Lookup & Math & More Used Time Reference Trig Functions Function Library X Define Name Conforme Name Marger Create from Selection Defined Names Po trace Precedents grace Dependents Ar Error Remove Arrows-Evalu Formula Audi 2 ADVANCED PROBLEM 2-1 FOR SPREADSHEET APPLICATION Income sement Company A and Com Bhave taken different approaches to selling plassware Company A las decided to make glass steware that sells for a premium at $16.98 pers Company has decided to make a less expensive glassware (without that sells for $11.98 per glass. Last year Copy A sold 847,000 stemmed glasses Comey B sold 1.388,000 glasses. Given the following additional information about each fin, construct an income statement for each compoy, and see which company and the higher gross margin (revenue miam cost of goods old), higher EBIT, higher taxable income, higher net income and higher OCF b. Compay Bis thinking of upgrading the quality of its glassware Doing so will cause its cost per it to rise by 18%. but it believes it Crise its price by 254. Its sales volume will fall by 15% (85% of its current volume). However, letting the market know that it has beter-quality glawware will require a doubling of the current selling general, and administrative expenses. Using a spreadsheet redo the come statement for this scenario. Does it improve net income and operating cash flow for Company B? How does the copy now compare to Com AP 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 Matement Gires Data Beformatica La sold Reve per il Cost per unit of plassware Fixed.co Selline, peneral, and drive expenses Depreciation expense s $ S S S S Company A Company B Company (Newware) (without st) (pradiay quality) 847,000 1. 18.00 1,179,00 16.985 11.985 14.98 8.17 S 6.695 7.89 1.245,788.00 S 1.354,218.00 S 1.154,218.00 785,038,00 5 584,411.09 1.168.862.00 1.489,374.00 $ 1,137,00 S 1.137.390.00 501,030.00 S 69,000 s 69,540,00 37.595 37.546 step Teree State Parts and be Company A 6,919.990.00 Parta Company Part Company Cost of poods sold Gromargin or pro Fixed costs Selling general, and desistive expenses Depreciation expense 5 1.245,788.00 785,038.00 1.489.374.00 1.35418.00 5 554.431.00 1.137.390.00 1,354,218.00 1.168.862.00 1.117.00 $ ulerest expen Table income 2.1 Type here to search O 3 Presen F4 FS AutoSaver 2 Whed Brooks Chapter 2.1 Start Read-Only - Excel File Home Draw Page Layout Formulas Data Review Dame- Insert Function AutoSum Recently Financial Logical Test Date & Lookup Martha More Used Time Reference Function Functionary eace Precedents Show for grace Dependents for Error Creding Name Manager for Section Dames F22 D Parts and Company A 6919.990.00 Company Part Copy Inree Statement Rever Cost of poods sold Grossmagio profil Fixed costs Selling general, and administrative speses Depreciation expense ERIT $ $ S 1,245,788.00 7850.00 5 1.489 7L00S 1.354,218.00 S 584,431.00 S 1.137.10.00 1.154,218.00 1.168.862.00 1,137,890,00 Table income Tas Netice Operating cash flow (OCF) a. Given the information about each firm, contract an income statement for the two companies, and see which company had the higher rou margin revmemis cost of goods sold). Higher EBIT, highwable income, her net income, and her OCE has ERIT, table income, ad net income, but it rose margin and OCH Com is thinking of upgrading the quality of its plasware. Does it improve set income and operating cash flow for Comp ? How does the company w opare to Copy A? Company has EBIT income, net icon, ou mary, and OCT er upgrading the lawware quality Points Requirements 1. St Excel In cell F21, by using cell reference calculate the sales revene for compuy A. Vee the relevant cells from the Ghe Data section la cell 27, by using cell references, calculate the cost of goods sold for compey A. Use the relevant cells from the hea Data sectie 2 1 1 Note: The output of the expression you byped in this cell is expected as a positive member 4. In cell 723, by using cell references, calculate the grous mariser profit for compoy A se cells F21 22 5. In cell 27, by using cell references, calculate the FRIT for compy. Use cells F23, 24, 25, 26 In cellF28, by using a cell reference, enter the most of the rest expense for company A. Refer to the relevant cell from the Givea Date section 6 Note: The owner of the express you typed in this cell is expected as a positive water 2-1 . Type here to search o BI auto save Oe Whited Brooks Chapter 21 Start - Read-Only - Excel e soonchi le Home Insert Draw Page Layout Formulas Data Review View Help Detine Name sert ction A AutoSum Recently Financial Logical Text Date & Lookup & Math & More Used The Reference Functions Function Library Name Manager Crate from Selection Blace Precedents The show to grace Dependents de Corc Remove Arrows - Eva for Audi f Company B has ERIT, Exable income, net income, grow margin, OCF afterwarding the requality Points 0 1 Requirements 1. Start Excel 2 In cell F21, by using cell references, calculate the sales revenue for company A. Use the relevant cells from the Given Data section In cell 27. by using cell references, calculate the cost of goods sold for comapy A. Use the relevant cells from the Given 3. Data section Note: The output of the expression you typed in this cell is expected as a positivember 4. In cell F23, by wing cell references, calentate the gross mary or profit for company A. Use cells F21 and 122 5. In cell 727, by using cell references, calculate the EBIT for compy. Use cells 23, 24, 25, and Y26 In cell 128, by using a cell reference, enter the amount of the interest expene for company A. Refer to the relevant cell 6. from the Glves Data section 1 1 1 1 Note: The output of the expression you typed in this cell is expected as a positive member 7. In cell 729, by using cell references, calculate the table income for company A. Use cells 127 128 In cell F30, by using cell references, calculate the incomes for company A. Use cell 729 od the relevant cell from the Give Data section 1 1 1 1 1 Note: The output of the expression you typed in this cell is expected as a positive saber 9. In cell F31, by using cell references, calculate the income for company A. Use cells F29 30 10. In cell 32, by using cell references, calculate the operating anh flow (OCF) for company A. Une cells 726, 28, and 131 In cell range G21:1032, by using cell references, contract the income statements for company B. Copy the content from 11. cell range F21:32 and paste to the cell range G21:32 for copy under the situation described in part and 11211132 ia parte 12. In cells D36 36, type either bigher or lower by comparing the accounting items of the two companies under the situation described in part 13 In cell D40, type either higher 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 excit Excel Submit the workbook as directed 1 1 0 2.1 Type here to search O AI wutoSave OF Whited Brooks Chapter 21 Start - Read-Only - Excel Search le Home Insert Draw Page Layout Formulas Data Review View Help leshor nction A @ AutoSum Recently Financial logical Test Date & Lookup & Math & More Used Time Reference Trig Functions Function Library X Define Name Conforme Name Marger Create from Selection Defined Names Po trace Precedents grace Dependents Ar Error Remove Arrows-Evalu Formula Audi 2 ADVANCED PROBLEM 2-1 FOR SPREADSHEET APPLICATION Income sement Company A and Com Bhave taken different approaches to selling plassware Company A las decided to make glass steware that sells for a premium at $16.98 pers Company has decided to make a less expensive glassware (without that sells for $11.98 per glass. Last year Copy A sold 847,000 stemmed glasses Comey B sold 1.388,000 glasses. Given the following additional information about each fin, construct an income statement for each compoy, and see which company and the higher gross margin (revenue miam cost of goods old), higher EBIT, higher taxable income, higher net income and higher OCF b. Compay Bis thinking of upgrading the quality of its glassware Doing so will cause its cost per it to rise by 18%. but it believes it Crise its price by 254. Its sales volume will fall by 15% (85% of its current volume). However, letting the market know that it has beter-quality glawware will require a doubling of the current selling general, and administrative expenses. Using a spreadsheet redo the come statement for this scenario. Does it improve net income and operating cash flow for Company B? How does the copy now compare to Com AP 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 Matement Gires Data Beformatica La sold Reve per il Cost per unit of plassware Fixed.co Selline, peneral, and drive expenses Depreciation expense s $ S S S S Company A Company B Company (Newware) (without st) (pradiay quality) 847,000 1. 18.00 1,179,00 16.985 11.985 14.98 8.17 S 6.695 7.89 1.245,788.00 S 1.354,218.00 S 1.154,218.00 785,038,00 5 584,411.09 1.168.862.00 1.489,374.00 $ 1,137,00 S 1.137.390.00 501,030.00 S 69,000 s 69,540,00 37.595 37.546 step Teree State Parts and be Company A 6,919.990.00 Parta Company Part Company Cost of poods sold Gromargin or pro Fixed costs Selling general, and desistive expenses Depreciation expense 5 1.245,788.00 785,038.00 1.489.374.00 1.35418.00 5 554.431.00 1.137.390.00 1,354,218.00 1.168.862.00 1.117.00 $ ulerest expen Table income 2.1 Type here to search O 3 Presen F4 FS AutoSaver 2 Whed Brooks Chapter 2.1 Start Read-Only - Excel File Home Draw Page Layout Formulas Data Review Dame- Insert Function AutoSum Recently Financial Logical Test Date & Lookup Martha More Used Time Reference Function Functionary eace Precedents Show for grace Dependents for Error Creding Name Manager for Section Dames F22 D Parts and Company A 6919.990.00 Company Part Copy Inree Statement Rever Cost of poods sold Grossmagio profil Fixed costs Selling general, and administrative speses Depreciation expense ERIT $ $ S 1,245,788.00 7850.00 5 1.489 7L00S 1.354,218.00 S 584,431.00 S 1.137.10.00 1.154,218.00 1.168.862.00 1,137,890,00 Table income Tas Netice Operating cash flow (OCF) a. Given the information about each firm, contract an income statement for the two companies, and see which company had the higher rou margin revmemis cost of goods sold). Higher EBIT, highwable income, her net income, and her OCE has ERIT, table income, ad net income, but it rose margin and OCH Com is thinking of upgrading the quality of its plasware. Does it improve set income and operating cash flow for Comp ? How does the company w opare to Copy A? Company has EBIT income, net icon, ou mary, and OCT er upgrading the lawware quality Points Requirements 1. St Excel In cell F21, by using cell reference calculate the sales revene for compuy A. Vee the relevant cells from the Ghe Data section la cell 27, by using cell references, calculate the cost of goods sold for compey A. Use the relevant cells from the hea Data sectie 2 1 1 Note: The output of the expression you byped in this cell is expected as a positive member 4. In cell 723, by using cell references, calculate the grous mariser profit for compoy A se cells F21 22 5. In cell 27, by using cell references, calculate the FRIT for compy. Use cells F23, 24, 25, 26 In cellF28, by using a cell reference, enter the most of the rest expense for company A. Refer to the relevant cell from the Givea Date section 6 Note: The owner of the express you typed in this cell is expected as a positive water 2-1 . Type here to search o BI auto save Oe Whited Brooks Chapter 21 Start - Read-Only - Excel e soonchi le Home Insert Draw Page Layout Formulas Data Review View Help Detine Name sert ction A AutoSum Recently Financial Logical Text Date & Lookup & Math & More Used The Reference Functions Function Library Name Manager Crate from Selection Blace Precedents The show to grace Dependents de Corc Remove Arrows - Eva for Audi f Company B has ERIT, Exable income, net income, grow margin, OCF afterwarding the requality Points 0 1 Requirements 1. Start Excel 2 In cell F21, by using cell references, calculate the sales revenue for company A. Use the relevant cells from the Given Data section In cell 27. by using cell references, calculate the cost of goods sold for comapy A. Use the relevant cells from the Given 3. Data section Note: The output of the expression you typed in this cell is expected as a positivember 4. In cell F23, by wing cell references, calentate the gross mary or profit for company A. Use cells F21 and 122 5. In cell 727, by using cell references, calculate the EBIT for compy. Use cells 23, 24, 25, and Y26 In cell 128, by using a cell reference, enter the amount of the interest expene for company A. Refer to the relevant cell 6. from the Glves Data section 1 1 1 1 Note: The output of the expression you typed in this cell is expected as a positive member 7. In cell 729, by using cell references, calculate the table income for company A. Use cells 127 128 In cell F30, by using cell references, calculate the incomes for company A. Use cell 729 od the relevant cell from the Give Data section 1 1 1 1 1 Note: The output of the expression you typed in this cell is expected as a positive saber 9. In cell F31, by using cell references, calculate the income for company A. Use cells F29 30 10. In cell 32, by using cell references, calculate the operating anh flow (OCF) for company A. Une cells 726, 28, and 131 In cell range G21:1032, by using cell references, contract the income statements for company B. Copy the content from 11. cell range F21:32 and paste to the cell range G21:32 for copy under the situation described in part and 11211132 ia parte 12. In cells D36 36, type either bigher or lower by comparing the accounting items of the two companies under the situation described in part 13 In cell D40, type either higher 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 excit Excel Submit the workbook as directed 1 1 0 2.1 Type here to search O AI