Assignments Of Class 12: Data Analysis Tools: Solver and Goal Seek
Assignment 1: Using Goal Seek
Objective: Use Goal Seek to determine the necessary sales to achieve a target profit.
Instructions:
- Create a new Excel spreadsheet.
- Set up the following data:
- In Cell A1, enter Sales and in Cell A2, enter 200 (this represents the initial sales amount).
- In Cell B1, enter Cost and in Cell B2, enter 50 (this represents the costs).
- In Cell C1, enter Profit and in Cell C2, enter the formula =A2-B2 (this calculates profit as Sales - Cost).
A | B | C |
Sales | Cost | Profit |
200 | 50 | =A2-B2 |
- Use Goal Seek to find out how much sales you need to achieve a profit of $150.
Expected Result: After running Goal Seek, Cell A2 should show the required sales amount.
Solution for Assignment 1:
- Set Up the Spreadsheet:
- The data in cells should look like this:
A | B | C |
Sales | Cost | Profit |
200 | 50 | 150 |
- Using Goal Seek:
- Go to Data > What-If Analysis > Goal Seek.
- Set the parameters:
- Set cell: C2
- To value: 150
- By changing cell: A2
- Click OK.
- Result:
- After running Goal Seek, Cell A2 will show $200 to achieve a profit of $150.
Assignment 2: Using Solver
Objective: Set up and use Solver to maximize profit while considering constraints.
Instructions:
- Open a new Excel worksheet.
- Set up the following data:
- In Cell A1, enter Product A Quantity and in Cell A2, enter 10.
- In Cell B1, enter Product B Quantity and in Cell B2, enter 20.
- In Cell C1, enter Profit Calculation and in Cell C2, enter the formula =A2*15 + B2*10 (this calculates profit based on the quantity of products A and B).
A | B | C |
Product A Quantity | Product B Quantity | Profit Calculation |
10 | 20 | =A215 + B210 |
- Add constraints:
- You can produce a maximum of 100 units of Product A and 80 units of Product B.
- Open Solver:
- Set the objective to maximize the profit in Cell C2 by changing the quantities in Cells A2 and B2.
- Add constraints for the maximum production quantities.
- Run Solver to find the optimal quantities of Products A and B that maximize profit.
Expected Result: After running Solver, Cells A2 and B2 should reflect the optimal quantities of products A and B.
Solution for Assignment 2:
- Set Up the Spreadsheet:
- The data in cells should look like this:
A | B | C |
Product A Quantity | Product B Quantity | Profit Calculation |
10 | 20 | 350 |
- Using Solver:
- Go to Data > Solver.
- Set the parameters:
- Set Objective: C2 (to maximize profit)
- By changing Variable cells: A2
- Add Constraints:
- A2 <= 100
- B2 <= 80
- Click Solve.
- Result:
- After running Solver, Cell A2 may show 100 (max units of Product A) and B2 may show 80 (max units of Product B) to maximize profit based on the constraints.
