Independent Project 8-6 Wilson Home Entertainment Systems calculates selling price based on cost and profit ate scenarios for the from an XML file and creating a PivotTable and PivotChart Student Learning Outcomes 8.1, 8.2, 8.3,8.4, 8.5, 8.6, 8.7, 8.8) margin. You cre- and build a tworable ata table. Other tasks include getting data Files Needed: WilsonHome- o8.xisx, Wilso onHome-08.xml, and WilsonHome-08xsd (Student dota files are available in the Library of your SiIMnet account Completed Project File Name: [your initials] Excel 8-6.xlsx Skills Covered in this Project Use the Anolysis ToolPak to generate a moving average. . Use Solver to find a solution. Use Goal Seek to backsolve a problem. Create and manage scenarios. . Create a scenario summary report . Build a two-variable data table. . Create a forecast sheet Get and transform XML data. . Create and customize a PivotTable. 1. Open the WilsonHome-08 workbook from your student data fles and save it as [your initials) Excel 8-6 2. Install Solver and the Analysis ToolPok. 3. Review pricing formulas a. Select cell F5 on the Product Pricing worksheet. Shipping and handling charges are calculated at 10% of the manufacturer's cost. b. Select cell F6. An insurance cost is calculated at 6% of the cost. c. Select cell F7. Total investment is the sum of the cost, shipping and handling. and insurance d. Select cell F9. The suggested selling price is the total investment multiplied by 1 plus the margin. These results are rounded to show zero (0) decimal places 4. Create and manage scenarios. Create a scenario named 12% with cell F8 as the changing cell but do not edit the value. This is the original data set Add another scenario named 13% and change the value Add scenarios for 15% and 17% profit margins Generate a scenario summary report for cells F4. F8, and F9 a. b. c. d. e. Edit the labels in cells C6 and C8:C10 in the Results Cells section of the summary report to display Cost Margin (C6 and C9. and Selling Price. Right-align these labels Show the 15% scenario in the Product Pricing worksheet 4 f. 5. Select cell B12 and create a reference to the selling price formula for the two-variable data table 6. Build the data table and format results as Currency with zero (0) decimal places Figure 8-113) 8-113 Completed data table for Product Pricing sheet Using Microsoft www.mhhe.comvotfice2016inpractice Independent Project 8-6 Wilson Home Entertainment Systems calculates selling price based on cost and profit ate scenarios for the from an XML file and creating a PivotTable and PivotChart Student Learning Outcomes 8.1, 8.2, 8.3,8.4, 8.5, 8.6, 8.7, 8.8) margin. You cre- and build a tworable ata table. Other tasks include getting data Files Needed: WilsonHome- o8.xisx, Wilso onHome-08.xml, and WilsonHome-08xsd (Student dota files are available in the Library of your SiIMnet account Completed Project File Name: [your initials] Excel 8-6.xlsx Skills Covered in this Project Use the Anolysis ToolPak to generate a moving average. . Use Solver to find a solution. Use Goal Seek to backsolve a problem. Create and manage scenarios. . Create a scenario summary report . Build a two-variable data table. . Create a forecast sheet Get and transform XML data. . Create and customize a PivotTable. 1. Open the WilsonHome-08 workbook from your student data fles and save it as [your initials) Excel 8-6 2. Install Solver and the Analysis ToolPok. 3. Review pricing formulas a. Select cell F5 on the Product Pricing worksheet. Shipping and handling charges are calculated at 10% of the manufacturer's cost. b. Select cell F6. An insurance cost is calculated at 6% of the cost. c. Select cell F7. Total investment is the sum of the cost, shipping and handling. and insurance d. Select cell F9. The suggested selling price is the total investment multiplied by 1 plus the margin. These results are rounded to show zero (0) decimal places 4. Create and manage scenarios. Create a scenario named 12% with cell F8 as the changing cell but do not edit the value. This is the original data set Add another scenario named 13% and change the value Add scenarios for 15% and 17% profit margins Generate a scenario summary report for cells F4. F8, and F9 a. b. c. d. e. Edit the labels in cells C6 and C8:C10 in the Results Cells section of the summary report to display Cost Margin (C6 and C9. and Selling Price. Right-align these labels Show the 15% scenario in the Product Pricing worksheet 4 f. 5. Select cell B12 and create a reference to the selling price formula for the two-variable data table 6. Build the data table and format results as Currency with zero (0) decimal places Figure 8-113) 8-113 Completed data table for Product Pricing sheet Using Microsoft www.mhhe.comvotfice2016inpractice
