This is the third instalment in the optimisation series. In the previous three posts, we have seen how we can: (long-form) find the profit maximising position of a business, understand how we estimate models to best predict future events, and consider numerous formulae to solve for the optimised point. What we will be going through today and next week are concrete examples/templates that we can set up and utilise in Excel. Although we are using Excel, I have created a Google Sheets version for your ease.

Within the Excel ribbon, there exists a ‘Data’ tab that hosts some very strong tools for data analysis:

Specifically: Analysis Tools, Data Analysis, and Solver.

If you’ve never used Solver before, chances are that it’s not currently appearing in this Data tab. To turn on Solver, click on Tools > Excel Add-ins… > select Solver Add-In

For Windows, follow this video.

Why is Solver noteworthy?

Why are we even bothering with Solver? Solver is an amazing tool. It does all of the calculations that we did in previous posts in only a fraction of the time. What’s necessary is to understand how to construct these models in Excel.

When developing these models, this is the process that I will follow during construction:

1. Turn the problem into English,

If I can explain the problem to someone, it means that I have understood what the main issues and objectives are.

2. Turn that English into Maths, then

This is the intermediary (transformation) between the human and computer. This is where I determine whether I am maximising or minimising a convex set.

3. Turn that Maths into Code.

In some ways, this is either the Excel algorithm that is constructed, or information that is input into Solver, especially when developing an integer or linear program solution.

A Solver Example

Using a previous post, we can use this as a template for setting up Solver correctly. To refresh your memory, this was the profit maximising bike shop:

Costs associated to the bikes have been estimated to be:

• $700,000 (advertising, manufacturing, etc), and
• $110 (cost of goods sold; cost to make a bike).

Given what they sell in stores, they can expect sales to follow a similar demand curve, so we can estimate this curve to be:

Sales = 70,000 − 200P,

where P is the price of a bike.

Given that we have this range of bike and price information, what is the best bike price the business should set to maximise their profits?

We can use Excel to set up the code from the maths that we have already constructed. So far, we can enter any fixed pricing, i.e., Admin costs, cost of goods sold (COGS), and bike costs:

We can enter the Unit Sales formula:

=C3–C5*C2

Next will be the Sales in Dollars formula:

=(C3–C5*C2)*C2

Next, the costs for bikes:

=C3+C4*(C2)

Lastly, the profit formula:

=C7–C8

Now that we have finished constructing this information in Excel, we can set up Solver to find the number of bikes that need to be sold to maximise profits. There are a few ways to start Solver, but (on a Mac) we’re going to select Tools > Solver:

Having not used Solver before, you would be able to intuitively work out how to use it. Firstly, what is the objective of our problem? We’re wanting to find the maximum profit that can be created. So, the ‘Set Objective’ field will be the profit cell and we want to ‘Max’ it:

The variable cells that we want to change here will be the price of bikes. When creating this formula, you can see that the only cell that is looking to be adjusted will be the price of bikes. Therefore, we will set the variable cell to be C12:

After Solver has finished optimising, it shows that the optimal bike price is $230, which confirms our previous work in that post:

Another Example

For this example, there is an Excel provided template sheet that has this information laid out. But, to access it online, you can download it from Excel with Wayne’s download link. Here’s the file, which I have formatted a little bit.

This objective for this template is:

How can I determine the monthly product mix that maximises profitability?

So, let’s set up our Solver parameters:

Our objective function is to maximise the firm’s profit (D12). The firm is attempting to determine where its resources should be allocated to the particular products to maximise this/these product(s). This means the changing variables will need to be the number of pounds produced for each product (1 to 6); D2:I2.

Lastly, there are three constraints that need to be considered: the amount of labour (4500) and raw materials (1600) used, and the amount of demand for each product. So, we set the first two (labour and materials, D14:D15) to be equal to or less than their availability (F14:F15). Then, pounds produced for each product (D2:I2) to be equal to or less than the demand for each product (D8:I8).

Using this approach, it shows that it is best to produce only two products, 4 (613 pounds) and 5 (1064 pounds), which would yield $9,441.87.

To be sure of this accuracy, I’ll change Product 4’s pounds to 613 and 614. Watch how the profit, labour used, and raw materials used change:

Although there is still room for raw materials to be used (potentially hinting at an unused/waste/re-ordering problem to solve), the labour used, by changing the Product 4 pounds made unit by 1, will be violated.

I hope, after seeing these two examples, you can start to see the value of Excel’s Solver. If so, have a crack at the Homework below.

Homework

A computer manufacturing plant produces mice, keyboards, and video game joysticks. The per-unit profit, per-unit labour usage, monthly demand, and per-unit machine-time usage are given in the following table:

	Mice	Keyboards	Joysticks
Profit/unit	$8	$11	$9
Labour usage/unit	.2 hour	.3 hour	.24 hour
Machine time/unit	.04 hour	.055 hour	.04 hour
Monthly demand	15,000	27,000	11,000

Each month, a total of 13,000 labour hours and 3000 hours of machine time are available. How can the manufacturer maximise its monthly profit contribution from the plant?

So, ask yourself:

• What is the object?
• What cells might you need to change?
• What are the constraints?

Answer in next week’s blog.