Excel Workout 8 - Solver

[IlgarZarbaliyev]

Welcome to Excel Workout #8!
Difficulty Level:

This week’s challenge is designed to test your knowledge on Solver What-If Analysis Add-In.

Solver
Solver is a What-If Analysis tool in Microsoft Excel that helps you find the optimal solution for a specific problem by changing multiple variables in a spreadsheet. It is used when you have a complex problem with multiple variables and constraints and want to find the best possible solution. Solver is a tool that uses mathematical algorithms to analyze data and generate solutions based on certain criteria or constraints.

Goals
Please follow the directions given below, which include downloading the Excel worksheet required to perform the challenge tasks. Once you have completed the download, proceed to take the challenge and test your skills.

Task

• Minimize the delivery expenses by devising a plan for distributing goods from 2 warehouses to stores (D24).
• This plan should consider both the quantity of goods required by each store and the capacity of the warehouses.

Submission
Reply to this post with your formula code and solution file. Please be sure to blur or hide your formula code.

Period
This workout will be released on Thursday April 20, 2023 , and the author’s solution will be posted on Wednesday April 26, 2023 .

Challenge #8.xlsx (8.5 MB)

Good luck,

Ilgar Zarbaliyev

[borydobon]

Formula

=LET(de,C11:G12,req,C22:G22,cap,J18:J19,REDUCE(de*0,SEQUENCE(COUNT(de)),LAMBDA(u,w, LET(r,req-MMULT(SEQUENCE(,ROWS(u))^0,u),c,cap-MMULT(u,SEQUENCE(COLUMNS(u))^0), b,IF((u=0)*r*c,de*10^6+r+c),m,b=MIN(b),IF(m,MIN(IF(m,r),IF(m,c)),u)))))

Solver as in Pic

Summary

Challenge #8.xlsx (219.5 KB)

[HermioneGranger1176]

Created a report for it.

	The cost of delivering goods to stores (Euro per item)
		Store A	Store B	Store C	Store D	Store E
	Warehouse - 1	150.00 €	120.00 €	90.00 €	30.00 €	45.00 €
	Warehouse - 1	120.00 €	150.00 €	24.00 €	96.00 €	90.00 €
	Delivery routes
		Store A	Store B	Store C	Store D	Store E	Total		Capacity
	Warehouse - 1	0 pcs	240 pcs	0 pcs	960 pcs	1200 pcs	2400 pcs		2400 pcs
	Warehouse - 1	900 pcs	450 pcs	450 pcs	0 pcs	0 pcs	1800 pcs		2100 pcs
	Total	900 pcs	690 pcs	450 pcs	960 pcs	1200 pcs
	Requried, units	900 pcs	690 pcs	450 pcs	960 pcs	1200 pcs
	Total Shipping Cost		2,97,900.00 €

Challenge #8.xlsx (8.5 MB)

[HermioneGranger1176]

@borydobon , We can’t expect less from the Excel Wizard. Presenting it with a formula is amazing.

[jany.gibson]

Solver solves. Thanks!
Challenge #8.xlsx (8.5 MB)

[QuadriAtharuOlayinka]

This was definitely a challenging one for me. That’s because i have not used it before. I had to watch some tutorials on YouTube to fully grasp it. Thanks for this challenge @IlgarZarbaliyev . I learnt something new. Excel wizard @borydobon , I have seen your formula approach and it’s quite phenomenal.

Challenge #8.xlsx (8.5 MB)

[IlgarZarbaliyev]

Great to get your solution…

[Keith]

Hi @IlgarZarbaliyev,

I’ve never used Solver before but reviewing some youtube video. I think I got the right answer.
File is attached

Excel Workout Challenge #8 Keith Smith.xlsx (8.5 MB)

Thanks for the workout.

Keith

[Ondrej]

Hi @IlgarZarbaliyev ,

great workout. I did get some weird number in cell C18, but if I change format to number its 0, but I don’t know.

Solution

Cell D24:
=SUMPRODUCT(C11:G11;C18:G18)+SUMPRODUCT(C12:G12;C19:G19)

Screenshot:

[IlgarZarbaliyev]

Thank you for participating in the Excel Challenge related to Solver Add-In!
I hope you found this challenge to be a fun and engaging way to improve your Excel skills and learn more about how to work with Solver in Excel.

Here is my solution to Workout #8:

Select cell D24.
Enter the next formula:
=SUM(C11:G11C18:G18)+SUM(C12:G12C19:G19)
Click on File Tab. Then, select More, Options, Add-Ins, Manage, Excel Add-Ins and click on Go. Check Solver.

Go to Data Tab. Select Solver from Analyze Group.

• Set Objective - $D$24

• Check Min

• By changing variable cells: $C$18:$G$19

• Subject to the Constraints:

1. $C$18:$G$19
   

   

2. $C$18:$G$19
   

3. $C$18:$G$19
   

Click Solve.

Challenge #8 with Solution.xlsx (8.5 MB)

Once again, thank you for participating in this Excel Challenge, and I look forward to seeing you in future challenges and learning opportunities!

[abbi]

When i select solve i m getting error message ( objective Cell contents must be a formula.

[IlgarZarbaliyev]

That’s right.
In the cell enter your formula that will calculate delivery cost.
Then apply Solver.
P.S. Please read solution steps.

[abbi]

htis is my entry $D$24

[abbi]

still getting same error message d24 has the formula and objective cell is set to $d$24.

[IlgarZarbaliyev]

I m going to record a video on this

[sam.mckay]

Great workout. Such a unique scenario

[IlgarZarbaliyev]

Thank you, @sam.mckay .