Excel Grader 6 – Linear Programming v18.0
Project Description:
Developed by Randy Jenne for PC and Mac
Steps to Perform:
Step |
Instructions |
Points Possible |
1 |
Download and open the file named e06_grader.xlsx. |
0 |
2 |
On the Budget sheet beginning in cell E3, complete the series of substitution values ranging from 200 to 500 at increments of 20 students vertically down column E. A good technique is to type 200 into E3, then type 220 into E4, then select cells E3 and E4 and drag the fill handle down to E18. |
3 |
3 |
Enter references to the total income, total expenses, and balance formulas in that order for a one-variable data table in Cell F2, Cell G2, and Cell H2 respectively. For example, F2 would be =C20 |
9 |
4 |
Create a one-variable data table for the range E2:H18 using the number of attendees as the Column input cell. Hint: The value in cell H18 will be -1382.5. Format the results (F3:H18) with Accounting Number Format with no decimal places. |
8 |
5 |
Use Goal Seek to achieve a Balance of $0 by changing the ticket price per person. |
6 |
6 |
Copy the number of attendees values from the one-variable data table (in cells E3:E18), and then paste the values starting in cell E22. |
3 |
7 |
Type $50 in cell F21 and $60 in cell G21 and using the same technique used earlier, complete the series of substitution values from $50 to $100 at $10 increments. |
2 |
8 |
Enter the reference to the balance formula (=C34) in the cell E21, then complete the two variable data table. HINT: The critical row, 21, contains variations on ticket prices, so the cell containing ticket price is your row input cell, and the critical column, E, contains variations on number of attendees, so the cell containing number of attendees is your column input cell. |
12 |
9 |
Using the Fill Color tool, manually apply a Red, Accent 2, Lighter 60% fill color to the three cells closest to break-even without creating a deficit. This will help to indicate the ticket prices and attendance necessary for the dance to make a profit. Hint: Look for the first occurrence of a positive number in each column (and some columns don't have any). This is the last step in the Budget worksheet. |
4 |
10 |
Click on the Solver worksheet. Load the Solver add-in if it is not already loaded. |
0 |
11 |
On the Solver sheet, set the Solver objective to calculate the highest balance possible. |
6 |
12 |
On the Solver sheet, set a constraint so that the number of attendees entered in the Input Section of the workbook does not exceed the specified limitation in cell B12. |
4 |
13 |
On the Solver sheet, set constraints so that the ticket price per person entered in the Input Section meets the requirements set in the range A14:B15. HINT: This will require two separate constraints. |
8 |
14 |
On the Solver sheet, set an appropriate integer constraint. HINT: Half a person cannot attend a party. |
4 |
15 |
Solve the problem and keep the solver solution. The balance should change to about $11,000. |
4 |
16 |
Click on the Linear Programming sheet. You will fill in this sheet and then use Solver to find the best solution. |
4 |
17 |
In F8 use SUMPRODUCT to multiply (and sum) Units by Unit Cost. |
3 |
18 |
In F9 use SUMPRODUCT to multiply (and sum) Units by Volume. |
3 |
19 |
In H5 sum the Units of Margaritas, Beer, and Scotch. |
2 |
20 |
Fill in the limits (red area) using the following information: |
4 |
21 |
Bring up Solver and set the Solver objective to maximize your alcohol consumption (Total Drinks). |
4 |
22 |
Add constraints to ensure that the Totals in F8 and F9 are controlled by the Limits in H8 and H9. |
6 |
23 |
Since you can't order half a beer, for example, add three more constraints to make sure Units are all integers. |
4 |
24 |
Solve the problem and keep the solver solution. Your Totals should be close to, but not exceed, your Limits. HINT: Six drinks. |
3 |
25 |
Save your work. Close the workbook and then exit Excel. Submit the workbook as directed. |
0 |
Total Points |
106 |
Excel Grader 6 – Linear Programming v18.0
Project Description:
Developed by Randy Jenne for PC and Mac
Steps to Perform:
Step |
Instructions |
Points Possible |
1 |
Download and open the file named e06_grader.xlsx. |
0 |
2 |
On the Budget sheet beginning in cell E3, complete the series of substitution values ranging from 200 to 500 at increments of 20 students vertically down column E. A good technique is to type 200 into E3, then type 220 into E4, then select cells E3 and E4 and drag the fill handle down to E18. |
3 |
3 |
Enter references to the total income, total expenses, and balance formulas in that order for a one-variable data table in Cell F2, Cell G2, and Cell H2 respectively. For example, F2 would be =C20 |
9 |
4 |
Create a one-variable data table for the range E2:H18 using the number of attendees as the Column input cell. Hint: The value in cell H18 will be -1382.5. Format the results (F3:H18) with Accounting Number Format with no decimal places. |
8 |
5 |
Use Goal Seek to achieve a Balance of $0 by changing the ticket price per person. |
6 |
6 |
Copy the number of attendees values from the one-variable data table (in cells E3:E18), and then paste the values starting in cell E22. |
3 |
7 |
Type $50 in cell F21 and $60 in cell G21 and using the same technique used earlier, complete the series of substitution values from $50 to $100 at $10 increments. |
2 |
8 |
Enter the reference to the balance formula (=C34) in the cell E21, then complete the two variable data table. HINT: The critical row, 21, contains variations on ticket prices, so the cell containing ticket price is your row input cell, and the critical column, E, contains variations on number of attendees, so the cell containing number of attendees is your column input cell. |
12 |
9 |
Using the Fill Color tool, manually apply a Red, Accent 2, Lighter 60% fill color to the three cells closest to break-even without creating a deficit. This will help to indicate the ticket prices and attendance necessary for the dance to make a profit. Hint: Look for the first occurrence of a positive number in each column (and some columns don't have any). This is the last step in the Budget worksheet. |
4 |
10 |
Click on the Solver worksheet. Load the Solver add-in if it is not already loaded. |
0 |
11 |
On the Solver sheet, set the Solver objective to calculate the highest balance possible. |
6 |
12 |
On the Solver sheet, set a constraint so that the number of attendees entered in the Input Section of the workbook does not exceed the specified limitation in cell B12. |
4 |
13 |
On the Solver sheet, set constraints so that the ticket price per person entered in the Input Section meets the requirements set in the range A14:B15. HINT: This will require two separate constraints. |
8 |
14 |
On the Solver sheet, set an appropriate integer constraint. HINT: Half a person cannot attend a party. |
4 |
15 |
Solve the problem and keep the solver solution. The balance should change to about $11,000. |
4 |
16 |
Click on the Linear Programming sheet. You will fill in this sheet and then use Solver to find the best solution. |
4 |
17 |
In F8 use SUMPRODUCT to multiply (and sum) Units by Unit Cost. |
3 |
18 |
In F9 use SUMPRODUCT to multiply (and sum) Units by Volume. |
3 |
19 |
In H5 sum the Units of Margaritas, Beer, and Scotch. |
2 |
20 |
Fill in the limits (red area) using the following information: |
4 |
21 |
Bring up Solver and set the Solver objective to maximize your alcohol consumption (Total Drinks). |
4 |
22 |
Add constraints to ensure that the Totals in F8 and F9 are controlled by the Limits in H8 and H9. |
6 |
23 |
Since you can't order half a beer, for example, add three more constraints to make sure Units are all integers. |
4 |
24 |
Solve the problem and keep the solver solution. Your Totals should be close to, but not exceed, your Limits. HINT: Six drinks. |
3 |
25 |
Save your work. Close the workbook and then exit Excel. Submit the workbook as directed. |
0 |
Total Points |
106 |
Excel Grader 6 – Linear Programming v18.0
Project Description:
Developed by Randy Jenne for PC and Mac
Steps to Perform:
Step |
Instructions |
Points Possible |
1 |
Download and open the file named e06_grader.xlsx. |
0 |
2 |
On the Budget sheet beginning in cell E3, complete the series of substitution values ranging from 200 to 500 at increments of 20 students vertically down column E. A good technique is to type 200 into E3, then type 220 into E4, then select cells E3 and E4 and drag the fill handle down to E18. |
3 |
3 |
Enter references to the total income, total expenses, and balance formulas in that order for a one-variable data table in Cell F2, Cell G2, and Cell H2 respectively. For example, F2 would be =C20 |
9 |
4 |
Create a one-variable data table for the range E2:H18 using the number of attendees as the Column input cell. Hint: The value in cell H18 will be -1382.5. Format the results (F3:H18) with Accounting Number Format with no decimal places. |
8 |
5 |
Use Goal Seek to achieve a Balance of $0 by changing the ticket price per person. |
6 |
6 |
Copy the number of attendees values from the one-variable data table (in cells E3:E18), and then paste the values starting in cell E22. |
3 |
7 |
Type $50 in cell F21 and $60 in cell G21 and using the same technique used earlier, complete the series of substitution values from $50 to $100 at $10 increments. |
2 |
8 |
Enter the reference to the balance formula (=C34) in the cell E21, then complete the two variable data table. HINT: The critical row, 21, contains variations on ticket prices, so the cell containing ticket price is your row input cell, and the critical column, E, contains variations on number of attendees, so the cell containing number of attendees is your column input cell. |
12 |
9 |
Using the Fill Color tool, manually apply a Red, Accent 2, Lighter 60% fill color to the three cells closest to break-even without creating a deficit. This will help to indicate the ticket prices and attendance necessary for the dance to make a profit. Hint: Look for the first occurrence of a positive number in each column (and some columns don't have any). This is the last step in the Budget worksheet. |
4 |
10 |
Click on the Solver worksheet. Load the Solver add-in if it is not already loaded. |
0 |
11 |
On the Solver sheet, set the Solver objective to calculate the highest balance possible. |
6 |
12 |
On the Solver sheet, set a constraint so that the number of attendees entered in the Input Section of the workbook does not exceed the specified limitation in cell B12. |
4 |
13 |
On the Solver sheet, set constraints so that the ticket price per person entered in the Input Section meets the requirements set in the range A14:B15. HINT: This will require two separate constraints. |
8 |
14 |
On the Solver sheet, set an appropriate integer constraint. HINT: Half a person cannot attend a party. |
4 |
15 |
Solve the problem and keep the solver solution. The balance should change to about $11,000. |
4 |
16 |
Click on the Linear Programming sheet. You will fill in this sheet and then use Solver to find the best solution. |
4 |
17 |
In F8 use SUMPRODUCT to multiply (and sum) Units by Unit Cost. |
3 |
18 |
In F9 use SUMPRODUCT to multiply (and sum) Units by Volume. |
3 |
19 |
In H5 sum the Units of Margaritas, Beer, and Scotch. |
2 |
20 |
Fill in the limits (red area) using the following information: |
4 |
21 |
Bring up Solver and set the Solver objective to maximize your alcohol consumption (Total Drinks). |
4 |
22 |
Add constraints to ensure that the Totals in F8 and F9 are controlled by the Limits in H8 and H9. |
6 |
23 |
Since you can't order half a beer, for example, add three more constraints to make sure Units are all integers. |
4 |
24 |
Solve the problem and keep the solver solution. Your Totals should be close to, but not exceed, your Limits. HINT: Six drinks. |
3 |
25 |
Save your work. Close the workbook and then exit Excel. Submit the workbook as directed. |
0 |
Total Points |
106 |
Excel Grader 6 – Linear Programming v18.0
Excel Grader 6 – Linear Programming v18.0Excel Grader 6 – Linear Programming v18.0Excel Grader 6 – Linear Programming v18.0
Project Description:
Project Description:Project Description:Project Description:
Developed by Randy Jenne for PC and Mac
Developed by Randy Jenne for PC and Mac
Steps to Perform:
Steps to Perform:Steps to Perform:Steps to Perform:
Step |
Instructions |
Points Possible |
1 |
Download and open the file named e06_grader.xlsx. |
0 |
2 |
On the Budget sheet beginning in cell E3, complete the series of substitution values ranging from 200 to 500 at increments of 20 students vertically down column E. A good technique is to type 200 into E3, then type 220 into E4, then select cells E3 and E4 and drag the fill handle down to E18. |
3 |
3 |
Enter references to the total income, total expenses, and balance formulas in that order for a one-variable data table in Cell F2, Cell G2, and Cell H2 respectively. For example, F2 would be =C20 |
9 |
4 |
Create a one-variable data table for the range E2:H18 using the number of attendees as the Column input cell. Hint: The value in cell H18 will be -1382.5. Format the results (F3:H18) with Accounting Number Format with no decimal places. |
8 |
5 |
Use Goal Seek to achieve a Balance of $0 by changing the ticket price per person. |
6 |
6 |
Copy the number of attendees values from the one-variable data table (in cells E3:E18), and then paste the values starting in cell E22. |
3 |
7 |
Type $50 in cell F21 and $60 in cell G21 and using the same technique used earlier, complete the series of substitution values from $50 to $100 at $10 increments. |
2 |
8 |
Enter the reference to the balance formula (=C34) in the cell E21, then complete the two variable data table. HINT: The critical row, 21, contains variations on ticket prices, so the cell containing ticket price is your row input cell, and the critical column, E, contains variations on number of attendees, so the cell containing number of attendees is your column input cell. |
12 |
9 |
Using the Fill Color tool, manually apply a Red, Accent 2, Lighter 60% fill color to the three cells closest to break-even without creating a deficit. This will help to indicate the ticket prices and attendance necessary for the dance to make a profit. Hint: Look for the first occurrence of a positive number in each column (and some columns don't have any). This is the last step in the Budget worksheet. |
4 |
10 |
Click on the Solver worksheet. Load the Solver add-in if it is not already loaded. |
0 |
11 |
On the Solver sheet, set the Solver objective to calculate the highest balance possible. |
6 |
12 |
On the Solver sheet, set a constraint so that the number of attendees entered in the Input Section of the workbook does not exceed the specified limitation in cell B12. |
4 |
13 |
On the Solver sheet, set constraints so that the ticket price per person entered in the Input Section meets the requirements set in the range A14:B15. HINT: This will require two separate constraints. |
8 |
14 |
On the Solver sheet, set an appropriate integer constraint. HINT: Half a person cannot attend a party. |
4 |
15 |
Solve the problem and keep the solver solution. The balance should change to about $11,000. |
4 |
16 |
Click on the Linear Programming sheet. You will fill in this sheet and then use Solver to find the best solution. |
4 |
17 |
In F8 use SUMPRODUCT to multiply (and sum) Units by Unit Cost. |
3 |
18 |
In F9 use SUMPRODUCT to multiply (and sum) Units by Volume. |
3 |
19 |
In H5 sum the Units of Margaritas, Beer, and Scotch. |
2 |
20 |
Fill in the limits (red area) using the following information: |
4 |
21 |
Bring up Solver and set the Solver objective to maximize your alcohol consumption (Total Drinks). |
4 |
22 |
Add constraints to ensure that the Totals in F8 and F9 are controlled by the Limits in H8 and H9. |
6 |
23 |
Since you can't order half a beer, for example, add three more constraints to make sure Units are all integers. |
4 |
24 |
Solve the problem and keep the solver solution. Your Totals should be close to, but not exceed, your Limits. HINT: Six drinks. |
3 |
25 |
Save your work. Close the workbook and then exit Excel. Submit the workbook as directed. |
0 |
Step
Instructions
Points Possible
Step
Instructions
Points Possible
Step
Step
StepStep
Instructions
Instructions
InstructionsInstructions
Points Possible
Points Possible
Points PossiblePoints Possible
1
Download and open the file named e06_grader.xlsx.
0
2
On the Budget sheet beginning in cell E3, complete the series of substitution values ranging from 200 to 500 at increments of 20 students vertically down column E. A good technique is to type 200 into E3, then type 220 into E4, then select cells E3 and E4 and drag the fill handle down to E18.
3
3
Enter references to the total income, total expenses, and balance formulas in that order for a one-variable data table in Cell F2, Cell G2, and Cell H2 respectively. For example, F2 would be =C20
9
4
Create a one-variable data table for the range E2:H18 using the number of attendees as the Column input cell. Hint: The value in cell H18 will be -1382.5. Format the results (F3:H18) with Accounting Number Format with no decimal places.
8
5
Use Goal Seek to achieve a Balance of $0 by changing the ticket price per person.
6
6
Copy the number of attendees values from the one-variable data table (in cells E3:E18), and then paste the values starting in cell E22.
3
7
Type $50 in cell F21 and $60 in cell G21 and using the same technique used earlier, complete the series of substitution values from $50 to $100 at $10 increments.
2
8
Enter the reference to the balance formula (=C34) in the cell E21, then complete the two variable data table. HINT: The critical row, 21, contains variations on ticket prices, so the cell containing ticket price is your row input cell, and the critical column, E, contains variations on number of attendees, so the cell containing number of attendees is your column input cell.
Format the results (F22:K37) with Accounting Number Format with no decimal places.
12
9
Using the Fill Color tool, manually apply a Red, Accent 2, Lighter 60% fill color to the three cells closest to break-even without creating a deficit. This will help to indicate the ticket prices and attendance necessary for the dance to make a profit. Hint: Look for the first occurrence of a positive number in each column (and some columns don't have any).
This is the last step in the Budget worksheet.
4
10
Click on the Solver worksheet.
Load the Solver add-in if it is not already loaded.
Mac: Tools | Excel Add-Ins
PC: File | Options | Add-Ins | Manage Excel Add-Ins | Go
Turn on: Analysis ToolPak and Solver Add-in
0
11
On the Solver sheet, set the Solver objective to calculate the highest balance possible.
Use the number of attendees and the ticket price per person as changing variable cells.
HINT: If you are having difficulty closing the Solver window try pressing the Esc key.
6
12
On the Solver sheet, set a constraint so that the number of attendees entered in the Input Section of the workbook does not exceed the specified limitation in cell B12.
4
13
On the Solver sheet, set constraints so that the ticket price per person entered in the Input Section meets the requirements set in the range A14:B15. HINT: This will require two separate constraints.
8
14
On the Solver sheet, set an appropriate integer constraint. HINT: Half a person cannot attend a party.
4
15
Solve the problem and keep the solver solution. The balance should change to about $11,000.
4
16
Click on the Linear Programming sheet.
You will fill in this sheet and then use Solver to find the best solution.
Begin by filling in the green area. Margaritas cost $5.00 and are 250 ml in volume. Beers cost $1.25 and are 331 ml. Scotch costs $12.50 and each one is 100 ml.
4
17
In F8 use SUMPRODUCT to multiply (and sum) Units by Unit Cost.
3
18
In F9 use SUMPRODUCT to multiply (and sum) Units by Volume.
3
19
In H5 sum the Units of Margaritas, Beer, and Scotch.
2
20
Fill in the limits (red area) using the following information:
You have set a budget of $50.00 for the evening.
Your bladder can hold a maximum of 1200 ml.
4
21
Bring up Solver and set the Solver objective to maximize your alcohol consumption (Total Drinks).
Use the number of Margaritas, Beer, and Scotch as changing variable cells.
Set the solving method to Simplex LP (otherwise Solver will take a long time)
Under Options, make sure Ignore Integer Constraints is turned off (unchecked).
4
22
Add constraints to ensure that the Totals in F8 and F9 are controlled by the Limits in H8 and H9.
6
23
Since you can't order half a beer, for example, add three more constraints to make sure Units are all integers.
4
24
Solve the problem and keep the solver solution. Your Totals should be close to, but not exceed, your Limits. HINT: Six drinks.
3
25
Save your work. Close the workbook and then exit Excel. Submit the workbook as directed.
0
1
Download and open the file named e06_grader.xlsx.
0
1
1
1
Download and open the file named e06_grader.xlsx.
Download and open the file named e06_grader.xlsx.
Download and open the file named e06_grader.xlsx.e06_grader.xlsx.
0
0
0
2
On the Budget sheet beginning in cell E3, complete the series of substitution values ranging from 200 to 500 at increments of 20 students vertically down column E. A good technique is to type 200 into E3, then type 220 into E4, then select cells E3 and E4 and drag the fill handle down to E18.
3
2
2
2
On the Budget sheet beginning in cell E3, complete the series of substitution values ranging from 200 to 500 at increments of 20 students vertically down column E. A good technique is to type 200 into E3, then type 220 into E4, then select cells E3 and E4 and drag the fill handle down to E18.
On the Budget sheet beginning in cell E3, complete the series of substitution values ranging from 200 to 500 at increments of 20 students vertically down column E. A good technique is to type 200 into E3, then type 220 into E4, then select cells E3 and E4 and drag the fill handle down to E18.
On the Budget sheet beginning in cell E3, complete the series of substitution values ranging from 200 to 500 at increments of 20 students vertically down column E. A good technique is to type 200 into E3, then type 220 into E4, then select cells E3 and E4 and drag the fill handle down to E18.20050020200 220
3
3
3
3
Enter references to the total income, total expenses, and balance formulas in that order for a one-variable data table in Cell F2, Cell G2, and Cell H2 respectively. For example, F2 would be =C20
9
3
3
3
Enter references to the total income, total expenses, and balance formulas in that order for a one-variable data table in Cell F2, Cell G2, and Cell H2 respectively. For example, F2 would be =C20
Enter references to the total income, total expenses, and balance formulas in that order for a one-variable data table in Cell F2, Cell G2, and Cell H2 respectively. For example, F2 would be =C20
Enter references to the total income, total expenses, and balance formulas in that order for a one-variable data table in Cell F2, Cell G2, and Cell H2 respectively. For example, F2 would be =C20
9
9
9
4
Create a one-variable data table for the range E2:H18 using the number of attendees as the Column input cell. Hint: The value in cell H18 will be -1382.5. Format the results (F3:H18) with Accounting Number Format with no decimal places.
8
4
4
4
Create a one-variable data table for the range E2:H18 using the number of attendees as the Column input cell. Hint: The value in cell H18 will be -1382.5. Format the results (F3:H18) with Accounting Number Format with no decimal places.
Create a one-variable data table for the range E2:H18 using the number of attendees as the Column input cell. Hint: The value in cell H18 will be -1382.5. Format the results (F3:H18) with Accounting Number Format with no decimal places.
Create a one-variable data table for the range E2:H18 using the number of attendees as the Column input cell. Hint: The value in cell H18 will be -1382.5. Format the results (F3:H18) with Accounting Number Format with no decimal places.
8
8
8
5
Use Goal Seek to achieve a Balance of $0 by changing the ticket price per person.
6
5
5
5
Use Goal Seek to achieve a Balance of $0 by changing the ticket price per person.
Use Goal Seek to achieve a Balance of $0 by changing the ticket price per person.
Use Goal Seek to achieve a Balance of $0 by changing the ticket price per person.
6
6
6
6
Copy the number of attendees values from the one-variable data table (in cells E3:E18), and then paste the values starting in cell E22.
3
6
6
6
Copy the number of attendees values from the one-variable data table (in cells E3:E18), and then paste the values starting in cell E22.
Copy the number of attendees values from the one-variable data table (in cells E3:E18), and then paste the values starting in cell E22.
Copy the number of attendees values from the one-variable data table (in cells E3:E18), and then paste the values starting in cell E22.
3
3
3
7
Type $50 in cell F21 and $60 in cell G21 and using the same technique used earlier, complete the series of substitution values from $50 to $100 at $10 increments.
2
7
7
7
Type $50 in cell F21 and $60 in cell G21 and using the same technique used earlier, complete the series of substitution values from $50 to $100 at $10 increments.
Type $50 in cell F21 and $60 in cell G21 and using the same technique used earlier, complete the series of substitution values from $50 to $100 at $10 increments.
Type $50 in cell F21 and $60 in cell G21 and using the same technique used earlier, complete the series of substitution values from $50 to $100 at $10 increments.$50$60$50$100$10
2
2
2
8
Enter the reference to the balance formula (=C34) in the cell E21, then complete the two variable data table. HINT: The critical row, 21, contains variations on ticket prices, so the cell containing ticket price is your row input cell, and the critical column, E, contains variations on number of attendees, so the cell containing number of attendees is your column input cell.
Format the results (F22:K37) with Accounting Number Format with no decimal places.
12
8
8
8
Enter the reference to the balance formula (=C34) in the cell E21, then complete the two variable data table. HINT: The critical row, 21, contains variations on ticket prices, so the cell containing ticket price is your row input cell, and the critical column, E, contains variations on number of attendees, so the cell containing number of attendees is your column input cell.
Format the results (F22:K37) with Accounting Number Format with no decimal places.
Enter the reference to the balance formula (=C34) in the cell E21, then complete the two variable data table. HINT: The critical row, 21, contains variations on ticket prices, so the cell containing ticket price is your row input cell, and the critical column, E, contains variations on number of attendees, so the cell containing number of attendees is your column input cell.
Format the results (F22:K37) with Accounting Number Format with no decimal places.
Enter the reference to the balance formula (=C34) in the cell E21, then complete the two variable data table. HINT: The critical row, 21, contains variations on ticket prices, so the cell containing ticket price is your row input cell, and the critical column, E, contains variations on number of attendees, so the cell containing number of attendees is your column input cell.=C34
Format the results (F22:K37) with Accounting Number Format with no decimal places.
12
12
12
9
Using the Fill Color tool, manually apply a Red, Accent 2, Lighter 60% fill color to the three cells closest to break-even without creating a deficit. This will help to indicate the ticket prices and attendance necessary for the dance to make a profit. Hint: Look for the first occurrence of a positive number in each column (and some columns don't have any).
This is the last step in the Budget worksheet.
4
9
9
9
Using the Fill Color tool, manually apply a Red, Accent 2, Lighter 60% fill color to the three cells closest to break-even without creating a deficit. This will help to indicate the ticket prices and attendance necessary for the dance to make a profit. Hint: Look for the first occurrence of a positive number in each column (and some columns don't have any).
This is the last step in the Budget worksheet.
Using the Fill Color tool, manually apply a Red, Accent 2, Lighter 60% fill color to the three cells closest to break-even without creating a deficit. This will help to indicate the ticket prices and attendance necessary for the dance to make a profit. Hint: Look for the first occurrence of a positive number in each column (and some columns don't have any).
This is the last step in the Budget worksheet.
Using the Fill Color tool, manually apply a Red, Accent 2, Lighter 60% fill color to the three cells closest to break-even without creating a deficit. This will help to indicate the ticket prices and attendance necessary for the dance to make a profit. Hint: Look for the first occurrence of a positive number in each column (and some columns don't have any).
This is the last step in the Budget worksheet.Budget
4
4
4
10
Click on the Solver worksheet.
Load the Solver add-in if it is not already loaded.
Mac: Tools | Excel Add-Ins
PC: File | Options | Add-Ins | Manage Excel Add-Ins | Go
Turn on: Analysis ToolPak and Solver Add-in
0
10
10
10
Click on the Solver worksheet.
Load the Solver add-in if it is not already loaded.
Mac: Tools | Excel Add-Ins
PC: File | Options | Add-Ins | Manage Excel Add-Ins | Go
Turn on: Analysis ToolPak and Solver Add-in
Click on the Solver worksheet.
Load the Solver add-in if it is not already loaded.
Mac: Tools | Excel Add-Ins
PC: File | Options | Add-Ins | Manage Excel Add-Ins | Go
Turn on: Analysis ToolPak and Solver Add-in
Click on the Solver worksheet.
Load the Solver add-in if it is not already loaded.
Mac: Tools | Excel Add-Ins
PC: File | Options | Add-Ins | Manage Excel Add-Ins | Go
Turn on: Analysis ToolPak and Solver Add-in
0
0
0
11
On the Solver sheet, set the Solver objective to calculate the highest balance possible.
Use the number of attendees and the ticket price per person as changing variable cells.
HINT: If you are having difficulty closing the Solver window try pressing the Esc key.
6
11
11
11
On the Solver sheet, set the Solver objective to calculate the highest balance possible.
Use the number of attendees and the ticket price per person as changing variable cells.
HINT: If you are having difficulty closing the Solver window try pressing the Esc key.
On the Solver sheet, set the Solver objective to calculate the highest balance possible.
Use the number of attendees and the ticket price per person as changing variable cells.
HINT: If you are having difficulty closing the Solver window try pressing the Esc key.
On the Solver sheet, set the Solver objective to calculate the highest balance possible.
Use the number of attendees and the ticket price per person as changing variable cells.
HINT: If you are having difficulty closing the Solver window try pressing the Esc key.
6
6
6
12
On the Solver sheet, set a constraint so that the number of attendees entered in the Input Section of the workbook does not exceed the specified limitation in cell B12.
4
12
12
12
On the Solver sheet, set a constraint so that the number of attendees entered in the Input Section of the workbook does not exceed the specified limitation in cell B12.
On the Solver sheet, set a constraint so that the number of attendees entered in the Input Section of the workbook does not exceed the specified limitation in cell B12.
On the Solver sheet, set a constraint so that the number of attendees entered in the Input Section of the workbook does not exceed the specified limitation in cell B12.Solver
4
4
4
13
On the Solver sheet, set constraints so that the ticket price per person entered in the Input Section meets the requirements set in the range A14:B15. HINT: This will require two separate constraints.
8
13
13
13
On the Solver sheet, set constraints so that the ticket price per person entered in the Input Section meets the requirements set in the range A14:B15. HINT: This will require two separate constraints.
On the Solver sheet, set constraints so that the ticket price per person entered in the Input Section meets the requirements set in the range A14:B15. HINT: This will require two separate constraints.
On the Solver sheet, set constraints so that the ticket price per person entered in the Input Section meets the requirements set in the range A14:B15. HINT: This will require two separate constraints.Solver
8
8
8
14
On the Solver sheet, set an appropriate integer constraint. HINT: Half a person cannot attend a party.
4
14
14
14
On the Solver sheet, set an appropriate integer constraint. HINT: Half a person cannot attend a party.
On the Solver sheet, set an appropriate integer constraint. HINT: Half a person cannot attend a party.
On the Solver sheet, set an appropriate integer constraint. HINT: Half a person cannot attend a party.Solver
4
4
4
15
Solve the problem and keep the solver solution. The balance should change to about $11,000.
4
15
15
15
Solve the problem and keep the solver solution. The balance should change to about $11,000.
Solve the problem and keep the solver solution. The balance should change to about $11,000.
Solve the problem and keep the solver solution. The balance should change to about $11,000.
4
4
4
16
Click on the Linear Programming sheet.
You will fill in this sheet and then use Solver to find the best solution.
Begin by filling in the green area. Margaritas cost $5.00 and are 250 ml in volume. Beers cost $1.25 and are 331 ml. Scotch costs $12.50 and each one is 100 ml.
4
16
16
16
Click on the Linear Programming sheet.
You will fill in this sheet and then use Solver to find the best solution.
Begin by filling in the green area. Margaritas cost $5.00 and are 250 ml in volume. Beers cost $1.25 and are 331 ml. Scotch costs $12.50 and each one is 100 ml.
Click on the Linear Programming sheet.
You will fill in this sheet and then use Solver to find the best solution.
Begin by filling in the green area. Margaritas cost $5.00 and are 250 ml in volume. Beers cost $1.25 and are 331 ml. Scotch costs $12.50 and each one is 100 ml.
Click on the Linear Programming sheet.
You will fill in this sheet and then use Solver to find the best solution.
Begin by filling in the green area. Margaritas cost $5.00 and are 250 ml in volume. Beers cost $1.25 and are 331 ml. Scotch costs $12.50 and each one is 100 ml.
4
4
4
17
In F8 use SUMPRODUCT to multiply (and sum) Units by Unit Cost.
3
17
17
17
In F8 use SUMPRODUCT to multiply (and sum) Units by Unit Cost.
In F8 use SUMPRODUCT to multiply (and sum) Units by Unit Cost.
In F8 use SUMPRODUCT to multiply (and sum) Units by Unit Cost.UnitsUnit Cost
3
3
3
18
In F9 use SUMPRODUCT to multiply (and sum) Units by Volume.
3
18
18
18
In F9 use SUMPRODUCT to multiply (and sum) Units by Volume.
In F9 use SUMPRODUCT to multiply (and sum) Units by Volume.
In F9 use SUMPRODUCT to multiply (and sum) Units by Volume.UnitsVolume
3
3
3
19
In H5 sum the Units of Margaritas, Beer, and Scotch.
2
19
19
19
In H5 sum the Units of Margaritas, Beer, and Scotch.
In H5 sum the Units of Margaritas, Beer, and Scotch.
In H5 sum the Units of Margaritas, Beer, and Scotch.
2
2
2
20
Fill in the limits (red area) using the following information:
You have set a budget of $50.00 for the evening.
Your bladder can hold a maximum of 1200 ml.
4
20
20
20
Fill in the limits (red area) using the following information:
You have set a budget of $50.00 for the evening.
Your bladder can hold a maximum of 1200 ml.
Fill in the limits (red area) using the following information:
You have set a budget of $50.00 for the evening.
Your bladder can hold a maximum of 1200 ml.
Fill in the limits (red area) using the following information:
You have set a budget of $50.00 for the evening.
Your bladder can hold a maximum of 1200 ml.
4
4
4
21
Bring up Solver and set the Solver objective to maximize your alcohol consumption (Total Drinks).
Use the number of Margaritas, Beer, and Scotch as changing variable cells.
Set the solving method to Simplex LP (otherwise Solver will take a long time)
Under Options, make sure Ignore Integer Constraints is turned off (unchecked).
4
21
21
21
Bring up Solver and set the Solver objective to maximize your alcohol consumption (Total Drinks).
Use the number of Margaritas, Beer, and Scotch as changing variable cells.
Set the solving method to Simplex LP (otherwise Solver will take a long time)
Under Options, make sure Ignore Integer Constraints is turned off (unchecked).
Bring up Solver and set the Solver objective to maximize your alcohol consumption (Total Drinks).
Use the number of Margaritas, Beer, and Scotch as changing variable cells.
Set the solving method to Simplex LP (otherwise Solver will take a long time)
Under Options, make sure Ignore Integer Constraints is turned off (unchecked).
Bring up Solver and set the Solver objective to maximize your alcohol consumption (Total Drinks).
Use the number of Margaritas, Beer, and Scotch as changing variable cells.
Set the solving method to Simplex LP (otherwise Solver will take a long time)Simplex LP
Under Options, make sure Ignore Integer Constraints is turned off (unchecked).Ignore Integer Constraints
4
4
4
22
Add constraints to ensure that the Totals in F8 and F9 are controlled by the Limits in H8 and H9.
6
22
22
22
Add constraints to ensure that the Totals in F8 and F9 are controlled by the Limits in H8 and H9.
Add constraints to ensure that the Totals in F8 and F9 are controlled by the Limits in H8 and H9.
Add constraints to ensure that the Totals in F8 and F9 are controlled by the Limits in H8 and H9.
6
6
6
23
Since you can't order half a beer, for example, add three more constraints to make sure Units are all integers.
4
23
23
23
Since you can't order half a beer, for example, add three more constraints to make sure Units are all integers.
Since you can't order half a beer, for example, add three more constraints to make sure Units are all integers.
Since you can't order half a beer, for example, add three more constraints to make sure Units are all integers.
4
4
4
24
Solve the problem and keep the solver solution. Your Totals should be close to, but not exceed, your Limits. HINT: Six drinks.
3
24
24
24
Solve the problem and keep the solver solution. Your Totals should be close to, but not exceed, your Limits. HINT: Six drinks.
Solve the problem and keep the solver solution. Your Totals should be close to, but not exceed, your Limits. HINT: Six drinks.
Solve the problem and keep the solver solution. Your Totals should be close to, but not exceed, your Limits. HINT: Six drinks.
3
3
3
25
Save your work. Close the workbook and then exit Excel. Submit the workbook as directed.
0
25
25
25
Save your work. Close the workbook and then exit Excel. Submit the workbook as directed.
Save your work. Close the workbook and then exit Excel. Submit the workbook as directed.
Save your work. Close the workbook and then exit Excel. Submit the workbook as directed.
0
0
0
Total Points |
106 |
Total Points
106
Total Points
106
Total Points
Total Points
Total PointsTotal Points
106
106
106106