Management Science Chapter 1 and 2 notes.docx

Formulating a spreadsheet model for this problem the

This preview shows page 39 - 42 out of 44 pages.

Formulating a Spreadsheet Model for This Problem The procedure summarized at the end of Section 2.2 can be used to formulate the spreadsheet model for this problem. Each step of the procedure is repeated below, followed by a description of how it is performed here. 1. Gather the data for the problem. This has been done as presented in Table 2.2 . 2. Enter the data into data cells on a spreadsheet. The top half of Figure 2.21 shows this spreadsheet. The data cells are in columns C and D (rows 4 and 8 to 10), as well as in cells G8:G10. Note how this particular formatting of the spreadsheet has facilitated a direct transfer of the data from Table 2.2 . TABLE 2.2 Data for the Profit & Gambit Co. Advertising-Mix Problem
Image of page 39

Subscribe to view the full document.

Image of page 40
FIGURE 2.21 The spreadsheet model for the Profit & Gambit problem, including the formulas for the objective cell TotalCost (G14) and the other output cells in column E, as well as the specifications needed to set up Solver. The changing cells, AdvertisingUnits (C14:D14), show the optimal solution obtained by Solver.
Image of page 41

Subscribe to view the full document.

3. Identify the decisions to be made on the levels of activities and designate changing cells for making these decisions. In this case, the activities of concern are advertising on television and advertising in the print media, so the levels of these activities refer to the amount of advertising in these media. Therefore, the decisions to be made are Decision 1: TV = Number of units of advertising on television Decision 2: PM = Number of units of advertising in the print media The two gray cells with light borders in Figure 2.21 —C14 and D14—have been designated as the changing cells to hold these numbers: TV → cell C14PM → cell D14 with AdvertisingUnits as the range name for these cells. (See the bottom of Figure 2.21 for a list of all the range names.) These are natural locations for the changing cells, since each one is in the column for the corresponding advertising medium. To get started, an arbitrary trial solution (such as all zeroes) is entered into these cells. ( Figure 2.21 shows the optimal solution after having already applied Solver.) page 50 3. Identify the constraints on these decisions and introduce output cells as needed to specify these constraints. The three constraints imposed by management are the goals for the increased sales for the respective products, as shown in the rightmost column of Table 2.2 . These constraints are Stain remover: Total increase in sales ≥ 3% Liquid detergent: Total increase in sales ≥ 18% Powder detergent: Total increase in sales ≥ 4% 4. Unlike the Wyndor problem, we need to use ≥ signs for these constraints. 5. The second and third columns of Table 2.2 indicate that the total increases in sales from both forms of advertising are Total for stain remover = 1% of PM Total for liquid detergent = 3% of TV + 2% of PM Total for powder detergent = − 1% of TV + 4% of PM 6. Consequently, since rows 8, 9, and 10 in the spreadsheet are being used to provide information about the three products, cells E8, E9, and E10 are introduced as output cells to show the total increase in sales for the respective products. In addition, ≥ signs have been entered in column F to remind us that the increased
Image of page 42
You've reached the end of this preview.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern