Answer this in excel with clear steps, Your client owns a business that produces standard and premium

 

Answer this in excel with clear steps,

Transcribed Image Text:

Your client owns a business that produces standard and premium dog foods. The standard recipe is made using chicken. The premium recipe may be made using chicken or beef. Your client asked you to recommend the number of bags of each the three types of dog food that should be sold each year: standard, premium with chicken, and premium with beef. On the chicken line, at most 8,000 bags each year may be made with chicken. On a separate line, at most 4,000 bags each year may be made with beef. All three types go through a mixing line which has an annual capacity of 10,000 bags. The annual demand for standard dog food is 8,000 bags. The annual demand for premium dog food is 5,000 bags. Assume that premium customers are willing to buy either type of premium dog food. The profit per bag are $12, $20, and $18 for standard, premium with chicken, and premium with beef respectively. A. Create a spreadsheet model in standard LP form and use Solver to determine the optimal annual production of the three types of dog foods to maximize profit. Label your tab as Q1A. B. Suppose the owner's marketing expert says that no more than 40% of bags sold may be premium. Adjust your model to account for this and re-run Solver. Label your tab as Q1B. C. Continue with the assumptions of part B in part C. Your client buys chickens from her sister-in-law. The sister-in-law would like to sell more chicken and requested the client consider a new policy of the client making exactly 10 bags containing chicken for every bag containing beef. Re-run Solver with this policy. Determine and highlight the impact of this policy on profit. Label your tab as Q1C. D. For this part of the question, assume the conditions of part B and disregard the assumption of part C. Suppose the local union of the Petfood American Workers (PAW) complains to your client about the unequal (and thus unfair!) utilization of the three lines. PAW wants each of the three lines to have equal (i.e. the same) capacity utilization. Add this request to the model and re-run Solver. In doing so, continue to assume as you did in the previous parts of this question that no dog food will be made or partially made unless it is sold. The optimal solution to Q1D may surprise you. What do you recommend your client tell PAW? Write and highlight the answer to this question in your spreadsheet. Label your tab as Q1D. Your client owns a business that produces standard and premium dog foods. The standard recipe is made using chicken. The premium recipe may be made using chicken or beef. Your client asked you to recommend the number of bags of each the three types of dog food that should be sold each year: standard, premium with chicken, and premium with beef. On the chicken line, at most 8,000 bags each year may be made with chicken. On a separate line, at most 4,000 bags each year may be made with beef. All three types go through a mixing line which has an annual capacity of 10,000 bags. The annual demand for standard dog food is 8,000 bags. The annual demand for premium dog food is 5,000 bags. Assume that premium customers are willing to buy either type of premium dog food. The profit per bag are $12, $20, and $18 for standard, premium with chicken, and premium with beef respectively. A. Create a spreadsheet model in standard LP form and use Solver to determine the optimal annual production of the three types of dog foods to maximize profit. Label your tab as Q1A. B. Suppose the owner's marketing expert says that no more than 40% of bags sold may be premium. Adjust your model to account for this and re-run Solver. Label your tab as Q1B. C. Continue with the assumptions of part B in part C. Your client buys chickens from her sister-in-law. The sister-in-law would like to sell more chicken and requested the client consider a new policy of the client making exactly 10 bags containing chicken for every bag containing beef. Re-run Solver with this policy. Determine and highlight the impact of this policy on profit. Label your tab as Q1C. D. For this part of the question, assume the conditions of part B and disregard the assumption of part C. Suppose the local union of the Petfood American Workers (PAW) complains to your client about the unequal (and thus unfair!) utilization of the three lines. PAW wants each of the three lines to have equal (i.e. the same) capacity utilization. Add this request to the model and re-run Solver. In doing so, continue to assume as you did in the previous parts of this question that no dog food will be made or partially made unless it is sold. The optimal solution to Q1D may surprise you. What do you recommend your client tell PAW? Write and highlight the answer to this question in your spreadsheet. Label your tab as Q1D.

Answer rating: 100% (QA)

Since Im unable to create an Excel spreadsheet or run Solver in ... View the full answer