Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Review marketing formulas.Select cell E7 on the Marketing Analysis tab. The SUMPRODUCT formula multiplies the cost per ad (cells C7 and D7) by the number
- 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).
- Select cellE10and then cellG4to review theSUMPRODUCTformulas.
- Create a scenario for original data.Select cellsC13:D13and click theWhat-if Analysisbutton [Datatab,Data Toolsgroup].
- SelectScenario Manager. No scenarios exist in the workbook.
- ClickAdd.
- TypeOriginalin theAdd Scenariodialog box.
- Verify that theChanging cellsbox shows cellsC13:D13.
- ClickOK.
- Do not edit theScenario Valuesdialog box and clickOK.
- ClickClose.
- InstallSolverand theAnalysis ToolPak.Select theOptionscommand [Filetab].
- ClickAdd-Insin the left pane.
- ClickGonear the bottom of the window.
- Select theSolver Add-inbox.
- Select theAnalysis ToolPakbox.
- ClickOK.
- SetSolverparameters.Click theSolverbutton [Datatab,Analyzegroup].
- Click cellG4for theSet Objectivebox. This cell has aSUMPRODUCTformula.
- page E8-541
- Verify that theMaxradio button is selected to find the maximum value.
- Click theBy Changing Variable Cellsbox and select cellsC13:D13.Solverwill test values for the number of TV and magazine ads.
- Add constraints to aSolverproblem.ClickAddto the right of theSubject to the Constraintsbox.
- Select cellC13for theCell Referencebox.
- Verify that
- 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.
- ClickAdd. (If you accidentally closed the dialog box, clickAddto reopen it.)
- Select cellE7for theCell Referencebox and use
- Click theConstraintbox and type3750as the budget amount.
- Add another constraint that cellE10be greater than or equal to12. This means that the audience reach must be at least 12 million.
- When all constraints are identified, clickOKin theAdd Constraintdialog box.
- ChooseGRG Nonlinearfor theSelect a Solving Methodbox.
- Verify that theMake Unconstrained Variables Non-Negativebox is selected (Figure 8-70).8-70Solverparameters
- 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.)
- SaveSolverresults as a scenario. (IfSolverhas not returned the indicated result, start at Step f.)ClickSave Scenarioin theSolver Resultsdialog box.
- TypeMax Exposureas the scenario name.
- ClickOKto return to theSolver Resultsdialog box.
- SelectAnswerin theReportslist.
- ClickOK. The generated report is inserted, andSolverresults are shown in the worksheet.
- IfSolverhas not worked as indicated, type6in cellC13and type3in cellC15and proceed to Step 7g.
- IfSolverhas not worked as indicated, create a scenario namedMax Exposurewith cellsC13:D13as the changing cells.
- page E8-542
- Create a scenario summary report.Click theWhat-if Analysisbutton [Datatab,Data Toolsgroup] and selectScenario Manager.
- Click theSummarybutton.
- Verify that theScenario summarybutton is selected.
- Click theResult cellsbox, select cellsC13:D13, type a comma,and then select cellG4(Figure 8-71).8-71Nonadjacent cells used in the report
- ClickOKin theScenario Summarydialog box. The report displays in a new worksheet.
- Show a scenario.Click theMarketing Analysisworksheet tab.
- Click theWhat-if Analysisbutton [Datatab,Data Toolsgroup] and selectScenario Manager.
- ClickOriginalto highlight the name.
- ClickShowand clickClose.
- Create a one-variable data table.Select cellsB20:B21and fill values to reach cellB30as column input values.
- Select cellC19, one column to the right and one row above the first input value.
- Type=, click cellE7, and pressEnterto reference the budget formula.
- Select cellsB19:C30as the data table range.
- Click theWhat-If Analysisbutton [Datatab,Data Toolsgroup] and chooseData Table.
- Click theColumn input cellbox and select cellC7to indicate that the cost per TV ad will be replaced by the input values in column B.
- ClickOKto create the data table (Figure 8-72).8-72Data table results withOriginalscenario
- Click theWhat-if Analysisbutton [Datatab,Data Toolsgroup] and selectScenario Manager.
- ClickMax Exposureto highlight the name and clickShow.
- ClickClose. The data table is updated.
- UseGoal Seekto find the number of ads.Select cellE10.
- Click theWhat-if Analysisbutton [Datatab,Data Toolsgroup] and chooseGoal Seek.
- Click theTo valuebox and type15as the target.
- Click theBy changing cellbox and select cellD13to determine how many magazine ads must be run to reach 15 million people.
- ClickOK. Note the result shown in cellD13in the worksheet.
- ClickCanceland type the result value in cellG23.
- 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
- Create a two-variable data table.Click theInventoryworksheet tab. Current costs and increase percentages are shown at the right.
- Select cellK4to place the formula one row above column input values and one column left of the row values.
- Type=, click cellJ7, type*(1+, click cellJ4, and pressEnter. The closing right parenthesis was necessary.
- 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.
- Select cellsK4:N10as the data table range.
- Click theWhat-If Analysisbutton [Datatab,Data Toolsgroup] and chooseData Table.
- Select cellJ4for theRow input cellbox because the percentage values in row 4 will replace the label in cell J4.
- Click theColumn input cellbox and select cellJ7so that the costs in column K replace the label in cell J7.
- ClickOKto build the data table.
- Format cellsL5:N10asCurrencywith two decimal places.
- Format cellK4to use aWhite, Background 1font color.
- Create aPivotTablefor theInventorydata.Select cellA3and click thePivotTablebutton [Inserttab,Tablesgroup].
- Verify that theNew Worksheetbutton is selected.
- Deselect theAdd this data to the Data Modelbox. You cannot insert a calculated field in aPivotTablebuilt from the data model.
- ClickOK.
- Name the worksheet tabPivotTable 1.
- Select theProduct ID, Product, Quantity, andCostfield boxes in thePivotTable Fieldspane. Label fields are added to theRowsarea, and numeric fields are added in theValuesarea.
- Right-clickSum of Costin cellC3and selectValue Field Settings.
- Edit the name to displayOur Costin theCustom Namebox.
- ClickNumber Formatin theValue Field Settingsdialog box.
- page E8-544
- ChooseCurrencyand clickOK.
- ClickOKto close theValue Field Settingsdialog box.
- Edit the name in cellB3to showCurrent Stock(Figure 8-75).8-75InventoryPivotTable
- Insert a calculated field.Click any cell in thePivotTable.
- 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.)
- TypeTotal Valueas the name for the calculated field in theNamebox in theInsert Calculated Fielddialog box.
- Click theFormulabox and delete the zero and the space after the equals sign.
- Double-clickCostin theFieldslist.
- Type*after ='Cost'in theFormulabox to multiply.
- Double-clickQuantityin theFieldslist and clickOK.
- ClickSum of Total Valuein cellD3and click theField Settingsbutton [PivotTable Tools Analyze,Active Fieldgroup].
- ClickNumber Formatin theValue Field Settingsdialog box.
- ChooseCurrencyand clickOK.
- Change theCustom NametoInventory Value.
- ClickOK.
- Insert a slicer.Verify that any cell in thePivotTableis active.
- Click theInsert Slicerbutton [PivotTable Tools Analyzetab,Filtergroup].
- Select theProductbox and clickOK.
- With the four-pointed arrow, drag the slicer so that the top-left corner is in cellE3.
- ClickComfy Walking Shoesin the slicer (Figure 8-76).8-76Data is filtered by the slicer
- Create aPivotChart.Select a cell in thePivotTableand click thePivotChartbutton [PivotTable Tools Analyze,Toolsgroup].
- ClickPiein the list,3-D Pieas the subtype, and clickOK.
- With the four-pointed arrow, drag thePivotChartso that its top-left corner is in cellI3.
- page E8-545
- Point to the bottom-right sizing handle and drag it to reach cellS24.
- ClickRugged Hiking Bootsin the slicer (Figure 8-77).8-77FilteredPivotTableandPivotChart
- Select cellA1.
- Get and transform data from an Access database.Click theNew Querybutton [Datatab,Get & Transformgroup], selectFrom Database, and chooseFrom Microsoft Access Database.
- Navigate to the folder withDatabase-08from your student data files and click to select the file name.
- ClickImportto open theNavigatorwindow.
- SelectTblWearEverin the list and clickLoad.
- Select theD:Hcolumns headings and click theHometab.
- Click theNumber Formatarrow in theNumbergroup and selectNumberas the format.
- Click theDecrease Decimalbutton [Hometab,Numbergroup] two times.
- Name the new sheetRatings(Figure 8-78).8-78Query results are loaded without editing
- Create aPivotTablefrom the data model.Click cellA2in the query results table on theRatingssheet.
- Click theSummarize with PivotTablebutton [Table Tools Designtab,Toolsgroup].
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started