USE EXCEL SOLVER!

Manisha Patel recently completed her first week of work as a summer intern at Delhi Foods.Earlier this morning, Manishas boss, the Director of Marketing, asked her to come up with arecommendation on the level of marketing expenses (advertising and promotion expenditures)for a line of frozen Indian dinners as it enters its seventh year in the marketplace. Exhibit 8.1,which Manisha received from her boss, contains an accounting summary of essential productcosts and revenues in the first six years, during which there has been some trial-and-error exper-imentation with marketing policies. For year seven, the table shows projections for the coming year based on a continuation of last years policy. This includes a new high of $38,000 in marketing expense, but Manishas boss intimated that this might be excessive.

Late last week, Manisha read an internal marketing study that had been completed at Delhi Foods. The study concluded that it is possible to represent the influence of marketing expenses on demand by means of the equation D = aM^b

where D and M represent demand and marketing expenses, respectively, and where a is called the scale factor and b the elasticity of marketing expenses.

Manisha knows from courses she has taken that this model belongs to a family of demand equations commonly used in market analysis. To determine values for the parameters a and b that apply to this product, she will have to

match this model to the observations as closely as possible.

Manisha ponders the information in the table. Costs for the coming year appear to be known; therefore, variable costs have already been estimated. Overhead and fixed production costs do not appear to be variable costs, so they dont enter into a calculation of gross margin. Instead, the gross margin is based on revenue, materials costs, and other variable costs. Using the projected figures for the coming year, Manisha expects that she will be able to compute the gross margin per unit. From there, Manisha believes she can represent profit for the line of dinners by using the gross margin per unit along with an estimate of demand to predict this years gross margin. Then she can subtract marketing expenses and fixed costs to arrive at a profit figure. She sees that marketing expenses show up in her profit calculation, but they also affect her demand estimate. If she can sort out all the relationships in a spreadsheet model, Manisha believes that she can find the optimal level to spend on marketing.

EXHIBIT 8.1 Summary of Product Costs and Revenues Year 4 Demand (cartons) 3200 3400 3500 3600 3800 4400 Revenue S(000) 62,000 63,000 66,000 75,000 86,000 98,000 105,000 Production Materials Other variable Fixed Marketing Advertising Promotion Overhead Operating margin3500 34004300(700 45008600 8500 4700 27,000 29,000 30,000 35.000 39,000 33,00035.000 1700 2200 2800 3500 2400 10,800 ,600 5900 5004700 4900 5000 5300 5600 10,300 11,700 15,000 16,200 17,800 22,000 24,000 90006000 4000 11,000 12,000 13,000 14,000 5000 6000 6000 5000 5000 5000 6000 EXHIBIT 8.1 Summary of Product Costs and Revenues Year 4 Demand (cartons) 3200 3400 3500 3600 3800 4400 Revenue S(000) 62,000 63,000 66,000 75,000 86,000 98,000 105,000 Production Materials Other variable Fixed Marketing Advertising Promotion Overhead Operating margin3500 34004300(700 45008600 8500 4700 27,000 29,000 30,000 35.000 39,000 33,00035.000 1700 2200 2800 3500 2400 10,800 ,600 5900 5004700 4900 5000 5300 5600 10,300 11,700 15,000 16,200 17,800 22,000 24,000 90006000 4000 11,000 12,000 13,000 14,000 5000 6000 6000 5000 5000 5000 6000