a. For each of the three advertising media, draw a graph of the number of sales versus the number of advertisements by plotting the sales for the five points provided by Sid Jackowitz and then drawing a smooth curve through (or very near) these points. (Fractional advertisements are allowed by using only a portion of the available outlets.)

b. For each of the advertising media, use Excel's curve fitting method to (1) obtain a nonlinear formula for the sales graph and then (2) construct the graph. In each case, try three Excel options for the form of the graph-a polynomial of order 2 (the quadratic form), a polynomial of order 3, and the logarithmic form-and then choose the option that you feel provides the best fit.

c. Using your results from part b, write an expression for the total profit (as defined by Claire) in terms of the number of advertisements of each type.

d. Using your result from part c, revise the spreadsheet model in Figure 3.7 (available on the CD-ROM) so that it maximizes total profit instead of the total number of exposures, and then solve.

e. Use the sales tables provided by Sid Jackowitz to apply separable programming to this problem when maximizing total profit.

f. Compare your results in parts d and e with those in Figure 3.7 and then give your recommendation (with a brief explanation) for the best advertising mix. Do you feel it was worthwhile to introduce a nonlinear profit function into the model in order to refine the linear programming model used in Figure 3.7?

FIGURE 3.3

Think-Big Development Co. Capital Budgeting Program Office Shopping Building Hotel Center 45 70 50 Net Present Value (Smillions) Cumulative Cumulative Capital Capital Available Cumulative Capital Required (Smillions) Spent Now 40 80 90 25 25 End of Year 1 100 140 10 160 44.76 45 End of Year 2 190 240 160 60.58 65 11 End of Year 3 200 310 220 80 80 12 13 14 Office Shopping Total NPV Building 15 Hotel Center (Smillions) 0.00% 16.50% 16 Participation Share 13.11% 18.11 Solver Parameters Set Objective Cell: TotalNPVv To: Max Range Name Cells Cumulative H9:H12 CapitalAvailable CapitalRequired CapitalSpent ParticipationShare NetPresent Value By Changing Variable Cells: ParticipationShare Subject to the Constraints: CapitalSpent