1. Review marketing formulas.Select cellE7on theMarketing Analysistab. TheSUMPRODUCTformula multiplies the cost per ad (cells C7 and D7) by the number of ads (cells C13 and D13).
2. Select cellE10and then cellG4to review theSUMPRODUCTformulas.
3. Create a scenario for original data.Select cellsC13:D13and click theWhat-if Analysisbutton [Datatab,Data Toolsgroup].
4. SelectScenario Manager. No scenarios exist in the workbook.
5. ClickAdd.
6. TypeOriginalin theAdd Scenariodialog box.
7. Verify that theChanging cellsbox shows cellsC13:D13.
8. ClickOK.
9. Do not edit theScenario Valuesdialog box and clickOK.
10. ClickClose.
11. InstallSolverand theAnalysis ToolPak.Select theOptionscommand [Filetab].
12. ClickAdd-Insin the left pane.
13. ClickGonear the bottom of the window.
14. Select theSolver Add-inbox.
15. Select theAnalysis ToolPakbox.
16. ClickOK.
17. SetSolverparameters.Click theSolverbutton [Datatab,Analyzegroup].
18. Click cellG4for theSet Objectivebox. This cell has aSUMPRODUCTformula.
19. page E8-541
20. Verify that theMaxradio button is selected to find the maximum value.
21. Click theBy Changing Variable Cellsbox and select cellsC13:D13.Solverwill test values for the number of TV and magazine ads.
22. Add constraints to aSolverproblem.ClickAddto the right of theSubject to the Constraintsbox.
23. Select cellC13for theCell Referencebox.
24. Verify that
25. Click theConstraintbox and select cellC15. This constraint is that the number of TV ads (cell C13) be less than or equal to the value in cell C15.
26. ClickAdd. (If you accidentally closed the dialog box, clickAddto reopen it.)
27. Select cellE7for theCell Referencebox and use
28. Click theConstraintbox and type3750as the budget amount.
29. Add another constraint that cellE10be greater than or equal to12. This means that the audience reach must be at least 12 million.
30. When all constraints are identified, clickOKin theAdd Constraintdialog box.
31. ChooseGRG Nonlinearfor theSelect a Solving Methodbox.
32. Verify that theMake Unconstrained Variables Non-Negativebox is selected (Figure 8-70).8-70Solverparameters
33. ClickSolve. A solution displays in the worksheet, and theSolver Resultsdialog box is open. The maximum total exposure is 14,500. (IfSolverdid not return that value, uninstallSolver, exit Excel, restart Excel, and installSolveragain. Then try Steps 5-6 again. If the error persists, go to Step 7f.)
34. SaveSolverresults as a scenario. (IfSolverhas not returned the indicated result, start at Step f.)ClickSave Scenarioin theSolver Resultsdialog box.
35. TypeMax Exposureas the scenario name.
36. ClickOKto return to theSolver Resultsdialog box.
37. SelectAnswerin theReportslist.
38. ClickOK. The generated report is inserted, andSolverresults are shown in the worksheet.
39. IfSolverhas not worked as indicated, type6in cellC13and type3in cellC15and proceed to Step 7g.
40. IfSolverhas not worked as indicated, create a scenario namedMax Exposurewith cellsC13:D13as the changing cells.
41. page E8-542
42. Create a scenario summary report.Click theWhat-if Analysisbutton [Datatab,Data Toolsgroup] and selectScenario Manager.
43. Click theSummarybutton.
44. Verify that theScenario summarybutton is selected.
45. Click theResult cellsbox, select cellsC13:D13, type a comma,and then select cellG4(Figure 8-71).8-71Nonadjacent cells used in the report
46. ClickOKin theScenario Summarydialog box. The report displays in a new worksheet.
47. Show a scenario.Click theMarketing Analysisworksheet tab.
48. Click theWhat-if Analysisbutton [Datatab,Data Toolsgroup] and selectScenario Manager.
49. ClickOriginalto highlight the name.
50. ClickShowand clickClose.
51. Create a one-variable data table.Select cellsB20:B21and fill values to reach cellB30as column input values.
52. Select cellC19, one column to the right and one row above the first input value.
53. Type=, click cellE7, and pressEnterto reference the budget formula.
54. Select cellsB19:C30as the data table range.
55. Click theWhat-If Analysisbutton [Datatab,Data Toolsgroup] and chooseData Table.
56. Click theColumn input cellbox and select cellC7to indicate that the cost per TV ad will be replaced by the input values in column B.
57. ClickOKto create the data table (Figure 8-72).8-72Data table results withOriginalscenario
58. Click theWhat-if Analysisbutton [Datatab,Data Toolsgroup] and selectScenario Manager.
59. ClickMax Exposureto highlight the name and clickShow.
60. ClickClose. The data table is updated.
61. UseGoal Seekto find the number of ads.Select cellE10.
62. Click theWhat-if Analysisbutton [Datatab,Data Toolsgroup] and chooseGoal Seek.
63. Click theTo valuebox and type15as the target.
64. Click theBy changing cellbox and select cellD13to determine how many magazine ads must be run to reach 15 million people.
65. ClickOK. Note the result shown in cellD13in the worksheet.
66. ClickCanceland type the result value in cellG23.
67. Select cellE10and useGoal Seekto change cellC13to calculate how many TV ads must be run to reach 15 million people. Do not keep the results, but type the resulting value in cellG26(Figure 8-73).8-73Completed Marketing Analysis sheet
68. Create a two-variable data table.Click theInventoryworksheet tab. Current costs and increase percentages are shown at the right.
69. Select cellK4to place the formula one row above column input values and one column left of the row values.
70. Type=, click cellJ7, type*(1+, click cellJ4, and pressEnter. The closing right parenthesis was necessary.
71. ClickYesin the message box to accept the correction with closing right parenthesis. The formula multiplies the cost times 1 plus the percentage increase. Because cellsJ4andJ7contain labels,the result is a standard error message (Figure 8-74).8-74The result is an error in the placeholder formula.
72. Select cellsK4:N10as the data table range.
73. Click theWhat-If Analysisbutton [Datatab,Data Toolsgroup] and chooseData Table.
74. Select cellJ4for theRow input cellbox because the percentage values in row 4 will replace the label in cell J4.
75. Click theColumn input cellbox and select cellJ7so that the costs in column K replace the label in cell J7.
76. ClickOKto build the data table.
77. Format cellsL5:N10asCurrencywith two decimal places.
78. Format cellK4to use aWhite, Background 1font color.
79. Create aPivotTablefor theInventorydata.Select cellA3and click thePivotTablebutton [Inserttab,Tablesgroup].
80. Verify that theNew Worksheetbutton is selected.
81. Deselect theAdd this data to the Data Modelbox. You cannot insert a calculated field in aPivotTablebuilt from the data model.
82. ClickOK.
83. Name the worksheet tabPivotTable 1.
84. Select theProduct ID, Product, Quantity, andCostfield boxes in thePivotTable Fieldspane. Label fields are added to theRowsarea, and numeric fields are added in theValuesarea.
85. Right-clickSum of Costin cellC3and selectValue Field Settings.
86. Edit the name to displayOur Costin theCustom Namebox.
87. ClickNumber Formatin theValue Field Settingsdialog box.
88. page E8-544
89. ChooseCurrencyand clickOK.
90. ClickOKto close theValue Field Settingsdialog box.
91. Edit the name in cellB3to showCurrent Stock(Figure 8-75).8-75InventoryPivotTable
92. Insert a calculated field.Click any cell in thePivotTable.
93. Click theFields, Items, and Setsbutton [PivotTable Tools Analyzetab,Calculationsgroup] and selectCalculated Field. (IfCalculated Fieldis unavailable, delete thePivotTable 1sheet, and return to step 13. Be sure to deselect theAdd this data to the Data Modelbox.)
94. TypeTotal Valueas the name for the calculated field in theNamebox in theInsert Calculated Fielddialog box.
95. Click theFormulabox and delete the zero and the space after the equals sign.
96. Double-clickCostin theFieldslist.
97. Type*after ='Cost'in theFormulabox to multiply.
98. Double-clickQuantityin theFieldslist and clickOK.
99. ClickSum of Total Valuein cellD3and click theField Settingsbutton [PivotTable Tools Analyze,Active Fieldgroup].
100. ClickNumber Formatin theValue Field Settingsdialog box.
101. ChooseCurrencyand clickOK.
102. Change theCustom NametoInventory Value.
103. ClickOK.
104. Insert a slicer.Verify that any cell in thePivotTableis active.
105. Click theInsert Slicerbutton [PivotTable Tools Analyzetab,Filtergroup].
106. Select theProductbox and clickOK.
107. With the four-pointed arrow, drag the slicer so that the top-left corner is in cellE3.
108. ClickComfy Walking Shoesin the slicer (Figure 8-76).8-76Data is filtered by the slicer
109. Create aPivotChart.Select a cell in thePivotTableand click thePivotChartbutton [PivotTable Tools Analyze,Toolsgroup].
110. ClickPiein the list,3-D Pieas the subtype, and clickOK.
111. With the four-pointed arrow, drag thePivotChartso that its top-left corner is in cellI3.
112. page E8-545
113. Point to the bottom-right sizing handle and drag it to reach cellS24.
114. ClickRugged Hiking Bootsin the slicer (Figure 8-77).8-77FilteredPivotTableandPivotChart
115. Select cellA1.
116. Get and transform data from an Access database.Click theNew Querybutton [Datatab,Get & Transformgroup], selectFrom Database, and chooseFrom Microsoft Access Database.
117. Navigate to the folder withDatabase-08from your student data files and click to select the file name.
118. ClickImportto open theNavigatorwindow.
119. SelectTblWearEverin the list and clickLoad.
120. Select theD:Hcolumns headings and click theHometab.
121. Click theNumber Formatarrow in theNumbergroup and selectNumberas the format.
122. Click theDecrease Decimalbutton [Hometab,Numbergroup] two times.
123. Name the new sheetRatings(Figure 8-78).8-78Query results are loaded without editing
124. Create aPivotTablefrom the data model.Click cellA2in the query results table on theRatingssheet.
125. Click theSummarize with PivotTablebutton [Table Tools Designtab,Toolsgroup].

Wear-Ever Shoes Marketing Budget Analysis Exposures per Ad TV 2000 Magazine 850 Cost per Ad Ad budget $500 $250 Audience Reached per Ad (in millions) Audience reached 1.85 0.5 Number of Ads Maximum TV New TV AD Costs $500 $525 TV 1 = Total Available Budget $3,750 Desired Reach 12 Magazine 1 Budget Goal Seek How many magazine ads to reach 15 million? 15 How many TV ads to reach 15 million? Wear-Ever Inventory, First Quarter Product ID WE001 WE002 WE003 WE004 WE005 WE006 WE007 WE008 WE009 WE010 WE011 WE012 WE013 WE014 WE015 WE016 WE017 WE018 WE019 WE020 WE021 WE022 WE023 WE024 WE025 WE026 Men's or Women' Color Size Quantity s Cost Retail Product Rugged Hiking BootsBrown Rugged Hiking BootsBrown Rugged Hiking BootsBlack Rugged Hiking BootsBlack Rugged Hiking BootsBlack Rugged Hiking BootsBlack Rugged Hiking BootsBrown Rugged Hiking BootsBrown Comfy Walking Shoe Brown Comfy Walking Shoe Black Comfy Walking Shoe Navy Comfy Walking Shoe Black Comfy Walking Shoe Taupe Comfy Walking Shoe Brown Lazy Flip-Flops Pink Lazy Flip-Flops Pink Lazy Flip-Flops Pink Lazy Flip-Flops White Lazy Flip-Flops White Lazy Flip-Flops White Lazy Flip-Flops Brown Lazy Flip-Flops Brown Lazy Flip-Flops Brown Lazy Flip-Flops Brown Seriously Tall Boots Black Seriously Tall Boots Black 8 10 9.5 10.5 7.5 8 9 8.5 8.5 9 7.5 8 7.5 8 6 7 8 6 7 8 8 9 10 11 6 6.5 5 3 6 4 4 2 3 2 4 1 4 1 2 3 4 0 2 3 1 2 2 4 2 2 0 0 M M M M W W W W M M W W W W W W W W W W M M M M W W $46.50 $46.50 $46.50 $46.50 $53.50 $53.50 $53.50 $53.50 $47.50 $47.50 $47.50 $47.50 $47.50 $47.50 $7.50 $7.50 $7.50 $7.50 $7.50 $7.50 $7.50 $7.50 $7.50 $7.50 $42.50 $42.50 $90.00 $90.00 $90.00 $90.00 $98.00 $98.00 $98.00 $98.00 $65.00 $65.00 $65.00 $65.00 $65.00 $65.00 $14.00 $14.00 $14.00 $14.00 $14.00 $14.00 $14.00 $14.00 $14.00 $14.00 $80.00 $80.00 Increase % Current Costs WE027 WE028 WE029 WE030 WE031 WE032 WE033 WE034 WE035 WE036 Seriously Tall Boots Black Seriously Tall Boots Black Seriously Tall Boots Black Seriously Tall Boots Black Glide Running ShoesWhite Glide Running ShoesWhite Glide Running ShoesWhite Glide Running ShoesBlack Glide Running ShoesBlack Glide Running ShoesBlack 7 7.5 8 8.5 8 9 10 8 9 10 1 0 2 1 6 6 6 2 3 1 W W W W M M M M M M $42.50 $42.50 $42.50 $42.50 $48.00 $48.00 $48.00 $48.00 $48.00 $48.00 $80.00 $80.00 $80.00 $80.00 $75.00 $75.00 $75.00 $75.00 $75.00 $75.00 #VALUE! $7.50 $42.50 $46.50 $47.50 $48.00 $53.50 10% 8.25 46.75 51.15 52.25 52.8 58.85 15% 8.625 48.875 53.475 54.625 55.2 61.525 These are new costs at each percentage increase. 20% 9 51 55.8 57 57.6 64.2 WearEver Shoes Glide Running Shoes Quarter Pairs Sold 3/31/2015 125 6/30/2015 145 9/30/2015 250 12/31/2015 115 3/31/2016 105 6/30/2016 175 9/30/2016 235 12/31/2016 200 3/31/2017 185 6/30/2017 200 9/30/2017 225 12/31/2017 275