For this problem, please include explanations of variables, objective function and constraints for the solution. A screenshot of how to solve it in excel would also be appreciated.
Case : Delhi Foods Manisha Patel recently completed her first week of work as a summer intern at Delhi Foods . Earlier this morning . Manisha's boss , the director of marketing , asked her to come up with a recommendation 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 product costs and revenues in the first 6 years . during which there has been some trial - and - error exper - Imentation with marketing policies . For year ] . the table shows projections for the coming year EXHIBIT 8.1 Summary of Product Costs and Revenues Year 2 6 7 Demand ( cartons ) 3. 200 3. 400 3.50.0 3. 60.0 3. 800 4. 400 4. 700 Revenue $ ( 090 ) 62. 000 63. 0.00 66. 00 0 75. 0.00 86. 000 98.000 105, 000 Production Materials 27. 000 29. 000 30. 00 0 35. 000 39. 000 33. 000 35, 000 Other variables 1 . 700 2.200 2.80 0 3.50.0 2, 400 10. 800 1 1 , 60.0 Fixed 4. 500 4. 700 4. 90.0 5. 0.00 5. 30.0 5. 600 5. 90.0 Marketing Advertising* 10. 300 11 . 700 15. 0.0 0 16. 20.0 17. 800 22. 000 24, 0.00 Promotion 9.000 6.0.00 4. 0.0 0 1 1. 000 12,000 13.000 14, 0:00 Overhead 6. 0.00 6. 0.00 5. 0.0 0 5. 0.00 5. 0.00 5.0.00 6.000 Operating margin 3.500 3. 40.0 4. 30.0 ( 70 0 ) 4. 500 8. 600 8.50.0based on a continuation of last year's policy. This includes a new high of $33,000 in marketing expense, but Manisha's 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 inuence of marketing expenses on demand by means of the equation o=aMt where D and M represent demand and marketing expenses, respectively. and where a is called the sralefarmr 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 me 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 xed production costs do not appear to be variable costs, so they don't enter into a calculation of gross margin. Instead. the gross margin is based on revenue, materials costs, and other variable costs. Using the projected gures 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 prot for the line of dinners by using the gross margin per unit along with an estimate of demand to predict this year's gross margin. Then she can subtract marketing expenses and xed costs to arrive at a prot gure. She sees that marketing expenses show up in her prot 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 nd the optimal level to spend on marketing
