Question: Can you help me with this one using google sheets or Excel please The Sonoma Apple Products Company purchases apples from local growers and makes

Can you help me with this one using google sheets or Excel please

Can you help me with this one using google sheetsCan you help me with this one using google sheets

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. (Hint - Start with building a data table). (a) Formulate a linear optimization model (objective function & constraints) 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. Use the following to label the decision variables: Let S be the number of jars of applesauce to produce J be the number of bottles of apple juice to produce T be the amount to spend on advertising applesauce U be the amount to spend on advertising apple juice (b) Enter the model in Excel and use the Solver to find the optimal solution. After solving, create the answer & sensitivity report. Turn in your Excel file or screen shots pasted on paper (pdf or doc) (c) How many jars of applesauce and bottles of apple juice should they produce? (d) How much should they spend on advertising for applesauce and apple juice? (e) What will their profit be

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related General Management Questions!