Question

TOPIC

Think about a domain you are very interested and/or experienced in and then think about aspects of it that are economic – issues of supply and demand, cost and profit, inventory management, appreciation/depreciation of value, entrepreneurial strategy, etc. What problems do you see in that domain that could benefit from an analytic excel model? Think outside the box and don't hesitate to contact me if you need help or if you want feedback from me at any time.

Domain: Beauty Supply Business

How can I implement the linear optimization model in Excel to find the number of each type of Hair Extensions to sell? How can I use Solver to find an optimal solution. Also, how can I Interpret the Solver Answer report and identify the binding constraints?

Hair Extension type and Price:

Straight: $74

Loose Wave: $84

Deep Curl: $94

Afro Kinky 4c: $104

Introduction - describe the problem you are trying to solve (e.g., What/when should I buy and sell shares in Stock X based on historical data? When should inventory be put on sale?), with some description of the topic domain (e.g., venture capitalism, clothing retail, restaurant management). Your problem should be rich enough to require a spreadsheet model and simulation or linear optimization.

Analytic Strategy - verbally describe your model/s of the problem, but also include an influence diagram and mathematical model, the assumptions your model/s make/s about the problem and the variables that are involved; also describe what data you will be using and how you will acquire it.

Findings and Implications - provide a visual model of the data and/or the model output/analytic results and a verbal explanation for what they mean to a decision maker faced with the problem you analyzed. If you had been hired by someone to consult on this problem, how you would advise them to act based on your findings.

Answer 1

The data can be acquired form the real values used by the company. for example, the availability of the raw material can de determined from the stock register or inventory register.

Once, the model is complete, it can be solved using excel. Excel generates different reports. One of them is shown below:

The binding text in from of the constrant show that the constraint's resources is fully used up and is now binding i.e. is restriciting the optimization. In the sensitivity report, each binding constraint will show a shadow price.

Shadow Price: The change to the optimal value of the objective function results from one unit increase in the right hand side of a constraint i.e. what change in objective function will occur with one unit increase of decrease in the constraint.

For example: if the shadow price corresponding to a constraint is 68.18. This means if we add one unit to the constraint's right hand side, the objective will increase by 68.18 and with 2 units it will increase by 2*68.18 = 136.36