Exercise 2: Analyzing Profit Potential You work for Humi-Corporation, an online retailer of home humidifiers. Your manager wants to know the profit potential of their inventory based on the iter inventory listed in Table 2-12. Table 2-12 contains the format of the desired report. The required formulas are shown in Table 2-13. Use the concepts and techniques developed in this chapter to create and format the worksheet. The company just received 67 additional desk-sized humidifiers and shipped out 48 room-sized humidifiers. Update the appropriate cells to reflect the change in inventory. Use conditional formatting to change the formatting to white font on a green background in any cell in of the Total Value that contains a value greater than 20,000.00 Create a 3-D Clustered Bar chart with the Total Cost and Total Value of each Item. Insert a 3D pie chart of the Potential Profit Table 2-12 HumiCorp Inventory Profit Potential Data and Worksheet Layout Item Units on Hand 187 Total Cost Formua A Unit Price Formulas Total Value formula Potential Profit formula Unit Cost 2 758 324.14 0 86.55 253.91 53.69 Desk Filtered home-sized Filtered room-sized Home-sized Roomszed Total Average Lowest Highest 7 118 108 7 9 Forma- Formula Formula G- Table 2-13 HumiCorp Inventory Profit Potential Formulas Formula A Units on Hand Unit Cost Formula B = Unit Costx (1/(1- 65) Fomda+Units on Hand X Unt Price Formula D=Total Value Total Cost Formula E - AVERAGE function Formula F VIN function Femme G-MAX funcion Change the name of the sheet tab to M2-Exercise 2 VOUSCICU DA chart with the Total Cost and Total Value of each Ite Insert a 3D pie chart of the Potential Profit Table 2-12 HumiCorp Inventory Profit Potential Data and Worksheet Layout Item Units on Hand Total Cost L Formula Unit Price Total Value Fomula e a Formula Potential Pro Formula Desk 187 Unit Cost 27.58 324.14 86.55 253.91 5 3.69 Filtered home-sized Filtered room-sized Home-sized Room-sized Total Average Lowest E 103 103 97 Formula E Formula Formula G Highest Table 2-13 HumiCorp Inventory Profit Potential Formulas Formula A Units on Hand x Unlt Cost Formula B = Unit Costx (1/(1-66)) Formula C - Units on Hand x Unit Price Formula D=Total Value - Total Cost Formula E AVERAGE function Formula F = MIN function Formula C = MAX funcion Exercise 2: Analyzing Profit Potential You work for Humi-Corporation, an online retailer of home humidifiers. Your manager wants to know the profit potential of their inventory based on the iter inventory listed in Table 2-12. Table 2-12 contains the format of the desired report. The required formulas are shown in Table 2-13. Use the concepts and techniques developed in this chapter to create and format the worksheet. The company just received 67 additional desk-sized humidifiers and shipped out 48 room-sized humidifiers. Update the appropriate cells to reflect the change in inventory. Use conditional formatting to change the formatting to white font on a green background in any cell in of the Total Value that contains a value greater than 20,000.00 Create a 3-D Clustered Bar chart with the Total Cost and Total Value of each Item. Insert a 3D pie chart of the Potential Profit Table 2-12 HumiCorp Inventory Profit Potential Data and Worksheet Layout Item Units on Hand 187 Total Cost Formua A Unit Price Formulas Total Value formula Potential Profit formula Unit Cost 2 758 324.14 0 86.55 253.91 53.69 Desk Filtered home-sized Filtered room-sized Home-sized Roomszed Total Average Lowest Highest 7 118 108 7 9 Forma- Formula Formula G- Table 2-13 HumiCorp Inventory Profit Potential Formulas Formula A Units on Hand Unit Cost Formula B = Unit Costx (1/(1- 65) Fomda+Units on Hand X Unt Price Formula D=Total Value Total Cost Formula E - AVERAGE function Formula F VIN function Femme G-MAX funcion Change the name of the sheet tab to M2-Exercise 2 VOUSCICU DA chart with the Total Cost and Total Value of each Ite Insert a 3D pie chart of the Potential Profit Table 2-12 HumiCorp Inventory Profit Potential Data and Worksheet Layout Item Units on Hand Total Cost L Formula Unit Price Total Value Fomula e a Formula Potential Pro Formula Desk 187 Unit Cost 27.58 324.14 86.55 253.91 5 3.69 Filtered home-sized Filtered room-sized Home-sized Room-sized Total Average Lowest E 103 103 97 Formula E Formula Formula G Highest Table 2-13 HumiCorp Inventory Profit Potential Formulas Formula A Units on Hand x Unlt Cost Formula B = Unit Costx (1/(1-66)) Formula C - Units on Hand x Unit Price Formula D=Total Value - Total Cost Formula E AVERAGE function Formula F = MIN function Formula C = MAX funcion
