MS5107 Celtic Candles Inc. manufactures decorative candles and has contracted with a national retailer to supply: Business Modelling and Analytics Assignment, UOG, Ireland

Question 1: Optimisation modeling

Task: production optimization:
Celtic Candles Inc. manufactures decorative candles and has contracted with a national retailer to supply a set of special holiday candles to its 8,500 stores. These include large jars, small jars, large pillars, small pillars, and a package of votive candles. In negotiating the contract for the display in each store, the manufacturer and the retailer agreed that at least 2 feet would be dedicated to both large jars and large pillars (combined), at least 1.5 feet to both small jars and small pillar (combined), and at least 1 foot to the votive candles packages. More jars than pillars must be provided to the retailer. The manufacturer has obtained 200,000 pounds of wax, 250,000 feet of wick, and 100,000 ounces of holiday
fragrance. The amount of materials, display size required for each product, and unit profit are shown in the following table:

Management of Celtic Candles wants you to help answer the question: how many of each product should be made to maximize the profit?

To answer the question, formulate and solve a linear optimization model using MS Excel Solver. Prepare a written report to the management containing details of the model formulation, Solver dialog box, generated reports, and their interpretation in a business context.