[Solved] Excel

[Fay]

What would the objective be to minimize the cost, and also what would be the equation for the constraints? Create a model for and solve using Solver.

 

[Yousuf Shovon]

What would the objective be to minimize the cost, and also what would be the equation for the constraints?

Hello Fay,
Thanks for sharing your experience with us. I understand you want to find the objectives, and equation of the constraints to minimize cost using Solver in Excel.
Please, read our article Excel Solver to Minimize Cost with Examples to gain a general idea of how Solver works. Here, we have shown how the Solver adjusts variables in decision variable cells to satisfy constraint cell limits and find the minimum cost in the objective cell.

Now, I will explain with an example you have provided me via conversation. Let's discuss your given problem here briefly:
"Create a recycled waste processing model and solve using Solver.

A regional authority runs twelve recycling centres (A to L) accepting a variety of materials, many of which can be separated at the centres and transported to specialist sites for further processing. Items which don’t fit into any specific category are deposited in skips labelled “General Waste”. General Waste is transported from recycling centres to regional hubs ( P, Q, and R), where there are facilities to enable a preliminary separation of material, before transportation to a specialist facility (labelled S) for final processing."
I have processed the data you have provided to create the model and it turned out like this:

Now, let's set our objectives and constraints in Excel:
We need the coefficients to be the transportation costs per tone in B15: D15 as given given below. The yellow marked region (B16: D16) will be left blank for now. Let's enter a formula in B17 that represents our minimum cost:
=SUMPRODUCT(B15: D15, B16: D16)

As expected, the function returns 0.
Now, let's set up our constraints based on the given sorting costs per ton and hub capacity. (See the below picture)

Let's get to our final calculations before accessing Solver. In B14, use the below SUMPRODUCT function and copy the formula cell upto B27 using Auto Filter tool.
=SUMPRODUCT(B19: D19,$B$16:$D$16)

Similarly, enter the below formula in C24 and drag the AutoFilter tool down to C27:
=F19

As a result, we have completed our data processing for the Solver add-in. Click the Solver button and fill out the options carefully: (see the provided article for these steps if necessary)

There it is! The output shows you need to transport 357.67, 409.6 & 288.45 tonnes of waste materials each time to get the minimum cost of £1326.29.

Tips: If you want to find the maximum cost using Solver in Excel, go to this forum link:
Ticket Pricing
I am attaching the final workbook here. Cheers!