Your boss is really happy with your dashboard! đ
He has now asked you to work with him on a regional strategy, with the aim of achieving $150,000 of sales in Bulgaria in 2021. You are going to use analysis tools to help you.
Meet a Specific Target Using Goal Seek
Do you remember math lessons at school? Did you sometimes struggle to solve math problems?
Well, thereâs no need to panic! This time, Excel is here to help! đ
We need to start by setting out the problem.
What we know:
The average garment price is $8.20.
Packaging expenses are fixed at $623.
How many garments do we need to sell to achieve $150,000 in sales?
When entering this data into an Excel table, the calculation of the result is based on: result = number of sales x average garment price â fixed expenses.
Youâre going to use the Goal Seek feature to help Excel find the value of the yellow cell, which equates to the target number of sales.
Excel uses this tool to test out all possible values of a cell, to enable another cell to achieve a specific value.
In our example:
In the Data tab, click on âWhat-If Analysisâ , then on the âGoal Seekâ row.
In the âSet cellâ field, select the cell which contains the result formula, i.e., B5.
Enter the value that you want to obtain, i.e., 150000.
In âBy changing cell,â input the number of sales required (i.e., B4).
Click OK to prompt Excel to find the ideal value of B4, so that B5 can achieve its target value.
Click âOKâ if you want to keep Excelâs value. Excel will populate cell B4. Otherwise, click Cancel.
In this problem with just one unknown component, Excel has only taken a few seconds to find the solution! đ
Solve Complex Problems Using Solver
If your problem includes more than one unknown, donât panic! Solver is your friend!
Whatâs Solver?
Like Goal Seek, Solver is an analysis tool, but more in-depth. With Solver, your target can be:
an exact value (e.g., $50,000 sales target).
a minimum value (e.g., minimum $50,000 sales target).
a maximum value (e.g., what is the maximum number of sales that I can achieve with these criteria?).
Whatâs more, Solver lets you add limits (or âConstraintsâ as Excel calls them) to the problem, for example:
The production facility can only produce up to 1000 garments per month.
Raw materials for one garment cost between $5 and $10, depending on the supplier.
Excel can manage up to 200 cumulative constraints! đ
Install the Solver Add-in
Excel does not install Solver by default. It is a free add-in which you simply activate.
To activate it:
Click on the âFileâ tab, then âOptionsâ at the bottom of the screen.
Click the âAdd-insâ menu, then âGo...â at the bottom of the screen.
The list of potential add-ins displays.
Check the box next to Solver Add-in, and click OK.
And there you have it! Solver is installed. đ
Use Solver
Letâs go back to our problem. We want to achieve $150,000 of sales in Bulgaria in 2021.
You need to take into account the fact that the garments are priced differently in your sales projection. Moreover, you can only produce a certain number of garments per year, within the limits of your production facility.
Therefore:
A garment in the âTopâ category is priced at an average of $10, and its maximum production is 6000 units.
A garment in the âBottomâ category is priced at an average of $7, and its maximum production is 6000 units.
A garment in the âTop and Bottomâ category is priced at an average of $15, and its maximum production is 4500 units.
The fixed packaging expenses for each clothing category are $623.
Incorporate all of this data into an Excel table, while keeping your target in mind.
Download this file to access the table.
You can solve this problem, which has multiples conditions, using Solver:
Go to the âDataâ tab, âAnalyzeâ group, and click the button.
The Solver window includes three fields:
Set Objective, in this case, cell E6. And underneath, the value to be achieved: 150000.
The changing cells are the yellow cells ( Â
B5:B5
 ), which Solver will use to test out different combinations until it obtains the desired result.The Constraints, which are the limits of our problem:
Top sales should be 6000 units or under.
Bottom sales should be 6000 units or under.
Top and Bottom sales should be 4500 units or under.
And you also need to tell Solver that values in the yellow cells must be whole numbers. After all, you canât make 0.3 tops! đ
In the Set Objective, select the cell which contains the calculation of the total, i.e., $E$6.
Check the âValue Ofâ box and add the value to be obtained, in this case, 150000.
Click âAddâ to set up the first constraint, which will tell Excel that Top sales (cell B5) should not exceed 6000 units (cell B4).
Click âAdd,â and create two further constraints for sales of âBottomâ and âTop and Bottomâ garments.
Click Add to create a constraint to only use whole numbers for sales. Add the data shown below:
Click âAdd,â and create two further constraints for sales of âTopâ and âTop and Bottomâ garments.
Finally, click OK and the Solver window should look like this:
Click on âSolve.â
Solver tells you that it has found a solution. Click OK to keep Solverâs solution.
Youâll see that the yellow cells have now been populated, and the total is indeed $150,000! đ
To obtain this figure while taking the constraints into account, Solver advises sales of 5526 âTopâ garments, 5667 âBottomâ garments, and 3796 âTop and Bottomâ garments.
Test Different Data Scenarios
Your boss and his colleagues are not sure how to price the different garments. Each of them has their own opinion on the average price for each garment category, and your boss asks you which price combination would be the most profitable for the business.
Here are the different price proposals:
Your bossâs proposal: $9 for tops, $8 for bottoms, and $14 for tops and bottoms
Timâs proposal: $10 for tops, $7 for bottoms, and $15 for tops and bottoms
Robâs proposal: $11 for tops, $8 for bottoms, and $13 for tops and bottoms
Excel can help you to create scenarios so you can compare the proposals! đ
For this step, Iâd advise you to download the scenarios file so that you can follow the demonstration below.
Prepare Your Scenario Analysis
Start by preparing the formula table which calculates the total number of sales:
Create Your Scenarios
Create Your Bossâs Scenario
Click on the âDataâ tab, âWhat-If Analysis,â and then âScenario Manager...â
Click âAdd...â
Name the first scenario âBossâs opinion.â
Select the changing cells, in this case the yellow cells (
B2:D2
).Click OK.
The changing cells window displays. Enter the first proposal.
Click OK.
Create Timâs Scenario
Repeat the above steps for the second scenario, with the average prices of $10 for tops, $7 for bottoms, and $15 for tops and bottoms.
Create Robâs Scenario
Repeat the above steps for the third scenario, with the average prices of $11 for tops, $8 for bottoms, and $13 for tops and bottoms.
Compare the Different Scenarios
Click on the âSummary...â button.
Select the cell containing the result formula (E5), then click on OK.
Ta-da! Excel creates a new tab, and completes its own comparison of the three proposals:
Over to You!
Download this file and do the following:
Use the Goal Seek feature to find the answer to problem 1 (green cell). The target is $75,000.
In problem 2, use Solver to achieve the $70,000 sales target, by playing around with the purchase price, the sales price, and the number of sales (orange cells).Â
Your constraints are:
The purchase price needs to be between $4 and $6.
The sales price needs to be between $22 and $26.
The minimum margin should be $20 or more.
Answer Key
Look at this file and watch the video below to check your work.
Letâs Recap!
Excel includes a range of analysis tools which you can use to solve complex problems.
You can use the Goal Seek tool to resolve problems with one unknown component.
You can use Solver to resolve complex problems with multiple constraints.
You can also use scenarios to compare different data sets.
Well done! Youâve just helped your boss to choose the best strategy! đ„ł Come with me to the next chapter to learn how to process large quantities of data.