Need to have an good understanding of Excel application.
Subject matter expert in Excel who can record his voice for the content, which will be used for training.
To apply: Pls reply along with the sample recording of the document shared below. ( Feel free to add fillers to make it conversational and sync in the flow)
Sample for recording:-
Concept Video Solver Add-in in Excel
Now that you have understood how to create histograms in Excel, let’s move on to the next Topic, Solver Add-in in Excel.
The Solver Add-in in Excel is used to solve complex Linear and Non Linear-Problems. To use the Solver Add-in, we first need to load it in Excel.
To enable Solver Add-In in Excel 2016,
1. Click the File Menu and choose Options.
2. Now, in the Excel Options dialogue box, select Add-Ins from the left sidebar and then click the GO button, next to Manage Excel Add-ins at the bottom.
3. In the Add-Ins available box, select the Solver Add-in check box, and then click OK.
We have enabled the Solver Add-in, and therefore,we can find the Solver command in the Analysis group on the Data tab.
Well! Look at this scenario.
An Organization is preparing a trip for 400 employees to …? And decided to go by bus. The Admin team has contacted an agency which has has 10 buses of capacity 50 seaters each and 8 buses of capacity 40 seaters. However only 9 drivers are available in a shift.
The rental cost for a large bus is $800 and for a small bus is $600. The admin team has to calculate how many buses of each type should be used at the least possible cost.
These kind of complex linear problems can be solved using Solver-Add in.
In this problem, we have No. of Buses as variables and No. of Employees and Drivers as constraints. These will act as our basis to formulate an optimal solution and calculate least possible cost.
In the cell(F12), we have used the formula to calculate Cost (i.e. multiplying each bus seater cost with number of buses)
To calculate the minimum cost, we need to perform the following steps:
1. Click the Data Tab and click Solver Add-in.
A “Solver Parameters” dialogue box will appear.
2. In the Set Objective box, enter a cell reference or name for the objective cell. Here, we select cell F3, which reflects the cost.
3. There are 3 options available.
a. If you want the value of the objective cell to be as large as possible, click Max.
b. If you want the value of the objective cell to be as small as possible, click Min.
c. If you want the objective cell to be a certain value, click Value Of, and then type the value in the box.
For our problem, we can select “Min” Radio button, as we are required to calculate the minimum cost.
4. In the By Changing Variable Cells box, enter a name or reference for each decision variable cell.we can select cell C11 for (50 seaters buses ) and cell D11 for 40 seaters buses.
5. In the Subject to the Constraints box, enter any constraints that you want to apply. To add a constraint:
a. Click Add button and a “Add Constraint” dialogue box appers.
b. Enter all the four constraints: (No. of employees “Equal to 400” , No. of drivers “ <=9” , “No. of 50 seater buses “<=10” and No. of 40 Seater buses “<=8”)
6. Select the Make Unconstrained Variables Non-Negative check box as we need result for positive variable numbers.
7. Click Solve and in the Solver Results dialog box, read the message at the top and the more detailed explanation at the bottom of this dialog box. After reading these messages, do one of the following:
To keep the final values in the decision variable cells, click Keep Solver Solution.
To restore the values of the decision variable cells at the time you clicked Solve, click Restore Original Values.
Select the Return to Solver Parameters Dialog check box if you want to modify the Solver model or re-solve as your next step. Click OK or Cancel.
Here, we will enable Keep Solver solution.