This assignment MUST be submitted in ONE Word and ONE Excel! Q1 and Q2 MUST be submitted in Word. Question 3, 4, 5: mathematical formulation and answers for part a, c, d and e should be in Word (you can embed handwritten solutions into Word if you want). The Excel files should be submitted to show the modeling in the Excel and the solution report created using Solver. In your Excel, make sure to correctly label your sheets. Q1.Formulate and answer a True/False questions for each of the following topics: a.Mathematical formulation b.Characteristics of LP c.Graphical solution d.Solution possibilities e.Solving in Excel Q2.Formulate and answer a multiple choice questions for each of the topics provided in Question 1. Each question should have four choices. Q3.The Sonoma Apple Products Company purchases apples from local growers and makes applesauce and apple juice. It costs $0.80 to produce a jar of applesauce and $0.60 to produce a bottle of apple juice. The company has a policy that at least 20 percent but no more than 60 percent of its output must be applesauce. The company wants to meet but not exceed the demand for each product. The marketing manager estimates that the demand for applesauce is a maximum of 5,000 jars, plus an additional 3 jars for each $1 spent on advertising for applesauce. The maximum demand for apple juice is estimated to be 4,000 bottles, plus an additional 5 bottles for every $1 spent on advertising for apple juice. The company has $16,000 to spend on producing and advertising its two products. Applesauce sells for $1.75 per jar, and apple juice sells for $1.75 per bottle. Formulate a linear optimization model to help the company determine how many units of each product to produce, and how much advertising to spend on each product, in order to maximize profit. a.Formulate this problem. Make sure to use the 3 steps and clearly explain all variables, objective function and the constraints. b.Formulate this problem in Excel Solver c.Make sure to report your optimal solution and the optimal objective value. d.Identify the binding constraints e.Identify slack associated with the constraints Q4. Sheila earned an $80,000 bonu
s which she would like to invest in the stock market. She is planning to buy shares of AMI Inc. and BCM Corp. The expected annual return for AMI is $3 per share and the annual return for BCM is $5 per share. AMI costs $25 per share and BCM costs $50 per share. Sheila would like to limit the risk in her stock portfolio. The risk is 0.50 per share of AMI and 0.25 per share of BCM. She wants the total risk of her stock portfolio to be at most 700. In addition, to make sure the portfolio is diversified she wants to invest in no more than 1,000 shares of AMI. a.Formulate a linear optimization model that Sheila could use to decide how many shares of each stock to buy in order to maximize the total expected annual return of the portfolio subject to the constraints. b.Use Excel to solve the linear optimization problem. c.How many shares of each stock should Sheila purchase? d.What is the expected annual return of the portfolio? e.Which constraints are binding? Q5. You work at an advertising firm and you’re managing the account of a client. You’re helping them plan their advertising for the next month. They have a budget of $30,000 and want you to design an ad campaign that: • Includes at least 10 TV commercials • Reaches at least 50,000 people • Allocates no more than $18,000 to TV ads • Maximizes the total Exposure Quality Units The following table shows the number of people reached per ad, the cost per ad, the number of available ads, and the Exposure Quality Units (which measures the ad’s effectiveness) per ad for the five available types of media. Reach. Cost. Available. Exposure Quality Units Day time TV. 1000. $1500. 15 65Evening TV 2000 $3000 10 90Daily Paper 1500 $400 25 40Sunday Paper 2500 $1000. 4 60Radio 300 $100 30 20a.Formulate Write out the formulation of a linear optimization model to help you determine the number of each type of ad to purchase in order to maximize the total Exposure Quality Units of the campaign. b.Formulate this problem in Excel Solver and solve. c.Make sure to report your optimal solution and the optimal objective value. d.Identify the binding constraints e.Identify slack associated with the constraints