PLEASE SOLVE USING EXCEL SOLVER

CASE 3: Pentagonal Pictures, Inc. Pentagonal Pictures produces motion pictures in Holly- film and its potential box office draw is inexact at best; wood and distributes them nationwide. Currently, it is still, the studio must rely on its experts' opinions to help it considering 10 possible films; these include dramas, come- evaluate which projects to undertake. dies, and action adventures. The success of each film de- The following table lists the films currently under conpends somewhat on both the strength of the subject sideration by Pentagonal Pictures, including the projected matter and the appeal of the cast. Estimating the cost of a cost and box office gross receipts. In addition to these production costs, each movie will Pentagonal has a production budget of $100 million have a \$1 million advertising budget, which will increase to and an advertising budget of $15 million. In addition, it $3 million if the movie is to have a "big star" cast. Assume would like to adhere to the following restrictions: that the studio receives 80% of a film's gross receipts. The company would like to maximize its net profit (gross profit 1. At least half the films produced should have a rating of - production costs - advertising costs) for the year. PG or PG-13. 186 CHAPTER 3 Applications of Linear and Integer Programming Models 2. At least two comedies are to be produced. 3. If The Crasb is produced, Bombs Away will not be. your report a sensitivity analysis that considers how vary- 4. At least one drama is to be produced. ing budgets for both production costs and advertising 5. At least two films should have big-name casts. (while spending at most a total of $115million ) would af- 6. At least two PG films should be produced. fect your recommendation. Finally, discuss the effects of 7. At least one action movie with a big-name cast should Pentagonal's seven restrictions and report the effect of rebe produced. quiring only six of the seven, five of the seven, and four of the seven to hold. Prepare a report for Pentagonal Pictures that recommends which films should be produced and with which CASES 4-6 ARE ON THE CD