Part 1: Goal Seek
The purpose of this project is to practice and apply what you have learned about using the Goal Seek tool in Excel. In this project, you will create a spreadsheet to help determine what score you need to earn on a final exam or project to get the desired letter grade in one of your classes. Please read the instructions carefully because you will have to customize your worksheet to fit your own needs and the class you choose.
Requirements:
Open a new Excel workbook. Use “GoalSeek†for the name of the spreadsheet—you will add additional spreadsheets to this workbook in parts 2 and 3.
Choose any one of your current classes and create a spreadsheet that includes at least the following headings: Category, Points Possible, Estimated Percentage, Estimated Points. You are free to include any additional headings that are useful to you.
Under Category, list the categories of the major components (assignments, quizzes, projects, exams, etc.) that make up your grade for that class. You don’t need to list the individual items in that category, just list the category (i.e., use “Quizzes†instead of “Quiz 1â€, “Quiz 2â€, etc.). However, list the final exam or final project separately from your other exams, as shown in the example screenshot. (If you don’t have any exams in the class, use the final project or whatever major graded activity that falls near the end of the semester.)
Under Points Possible, enter the total number of possible points for each category. (If you have ten quizzes in the class and each quiz is worth 10 points, the points possible for quizzes is 100.) When you get to the final exam or project, list the possible points for that one exam or that one project and be sure to exclude those points from the general exams category.
Under Estimated Percentage, enter your best guess for the percentage of points you think you will earn for each category. If you think you will average 80% on the quizzes or the other exams, enter in 80% for that category.Do this for all the categories in your spreadsheet.
Use a formula to calculate the estimated points using the points possible and the estimated percentage. Do this for all the categories in your spreadsheet.
In the three rows immediately below the last grade category in your spreadsheet, enter three labels: Total Points, Points Possible, and Percent Score. In the column directly to the right of those labels, use formulas or enter the total estimated points, the actual points possible for the class, and the percentage score (Total Points/Points Possible) based on your estimated points for the categories. The screenshot below shows a partial version of the table you are creating. Yours should not be exactly like this, but it should have at least the headings and labels as shown.
Use the Goal Seek tool to determine the percent score that you need to get on the final exam or project to get an A (90%) in the class. In a cell beneath the table you just created, record what score you need to get on the final exam to earn an A in the class and also comment on whether you think it is likely you will achieve that.
Use the Goal Seek tool again to determine the percent score that you need to get on the final exam or project to get a B (80%) in the class. In a cell beneath your first comment, record what score you need to get on the final exam to earn an B in the class and also comment on whether you think it is likely you will achieve that.
Save your progress and proceed to Part 2.
Part 2: Solver
The purpose of this part of the project is to practice and apply what you have learned about the Solver tool. You will use Solver to determine an optimal combination and number of products to produce to maximize profit.
Requirements:
Add a new spreadsheet into your project 4 workbook and name it “Solverâ€.
Enter in the labels and numbers as shown in the following screenshot. This table shows the profit and requirements for three different products. Each product requires a certain number of hours and materials. The company only has so many resources available, so the solution has to fit within what is available.
Cells F5, F6, and G8 use functions or formulas. All other numbers should be entered in exactly as shown in the screenshot.
Cell F5 holds the calculation for the total number of hours used, based on the production size for each product. This is calculated by taking the hours required multiplied by the production size for Product 1, added to the hours required multiplied by the production size for Product 2, added to the hours required multiplied by the production size for Product 3. The easiest way to calculate this is to use the SUMPRODUCT function for the hours cells and the production size cells. (Look up the SUMPRODUCT function on Google if you need help figuring out how to use it.)
Cell F6 holds the calculation for the total number of materials used, based on the production size for each product. Use the SUMPRODUCT function to calculate this value similar to what you did with hours.
Cell G8 is the total profit. It is calculated using a formula that takes the sum of the product profits multiplied by the production sizes. Use SUMPRODUCT for this cell as well.
Use the Solver tool to determine the optimal mix and number of products to produce.
Your goal is to maximize total profit.
You achieve this by allowing Solver to change to production sizes for all the products.
You must implement the following bounds and constraints:
You cannot use more resources than you have available.
The minimum production size for each product is 5.
The production size must be in whole numbers or integers—no partially products produced.
After you have successfully used Solver to maximize total profit, save your spreadsheet in that state without changing the numbers or your Solver settings. The optimal total profit should be around $6,700.
Save your progress and proceed to Part 3.
Part 3: Data Tables
The purpose of this part of the project is for you to practice and apply what you have learned about data tables. You will create two different data tables that show how loan interest rates and terms affect your total and monthly payments.
Requirements:
Add a new spreadsheet to your existing project 4 workbook and name it “DataTablesâ€.
Copy a mortgage table from a previous example or project or create a new one. Your table should be similar to the one in the screenshot below. Be sure to use $300,000 for the home price, a 20% down payment amount, a 3.5% interest rate, and a 30 year loan.
To the right of the mortgage table, create a one-variable data table using the Data Table tool that shows the monthly payments and total amounts paid for the range of interest rates between 3.250% and 4.000%. Your completed data table should match the one below. You must use the Data Table tool; do not simply manually enter the numbers into the table.
Below your first data table, create a second data table. This should be a two-variable data table that shows how the number of years determines the total amounts paid for the loan for the interest rates between 3.250% and 4.000%. You must use the Data Table tool, and your completed table should match the one below.
Save your progress and workbook.Part 1: Goal Seek
The purpose of this project is to practice and apply what you have learned about using the Goal Seek tool in Excel. In this project, you will create a spreadsheet to help determine what score you need to earn on a final exam or project to get the desired letter grade in one of your classes. Please read the instructions carefully because you will have to customize your worksheet to fit your own needs and the class you choose.
Requirements:
1. Open a new Excel workbook. Use “GoalSeek†for the name of the spreadsheet—you will add additional spreadsheets to this workbook in parts 2 and 3.
2. Choose any one of your current classes and create a spreadsheet that includes at least the following headings: Category, Points Possible, Estimated Percentage, Estimated Points. You are free to include any additional headings that are useful to you.
3. Under Category, list the categories of the major components (assignments, quizzes, projects, exams, etc.) that make up your grade for that class. You don’t need to list the individual items in that category, just list the category (i.e., use “Quizzes†instead of “Quiz 1â€, “Quiz 2â€, etc.). However, list the final exam or final project separately from your other exams, as shown in the example screenshot. (If you don’t have any exams in the class, use the final project or whatever major graded activity that falls near the end of the semester.)
4. Under Points Possible, enter the total number of possible points for each category. (If you have ten quizzes in the class and each quiz is worth 10 points, the points possible for quizzes is 100.) When you get to the final exam or project, list the possible points for that one exam or that one project and be sure to exclude those points from the general exams category.
5. Under Estimated Percentage, enter your best guess for the percentage of points you think you will earn for each category. If you think you will average 80% on the quizzes or the other exams, enter in 80% for that category. Do this for all the categories in your spreadsheet.
6. Use a formula to calculate the estimated points using the points possible and the estimated percentage. Do this for all the categories in your spreadsheet.
7. In the three rows immediately below the last grade category in your spreadsheet, enter three labels: Total Points, Points Possible, and Percent Score. In the column directly to the right of those labels, use formulas or enter the total estimated points, the actual points possible for the class, and the percentage score (Total Points/Points Possible) based on your estimated points for the categories. The screenshot below shows a partial version of the table you are creating. Yours should not be exactly like this, but it should have at least the headings and labels as shown.
8. Use the Goal Seek tool to determine the percent score that you need to get on the final exam or project to get an A (90%) in the class. In a cell beneath the table you just created, record what score you need to get on the final exam to earn an A in the class and also comment on whether you think it is likely you will achieve that.
9. Use the Goal Seek tool again to determine the percent score that you need to get on the final exam or project to get a B (80%) in the class. In a cell beneath your first comment, record what score you need to get on the final exam to earn an B in the class and also comment on whether you think it is likely you will achieve that.
Save your progress and proceed to Part 2.
Part 2: Solver
The purpose of this part of the project is to practice and apply what you have learned about the Solver tool. You will use Solver to determine an optimal combination and number of products to produce to maximize profit.
Requirements:
1. Add a new spreadsheet into your project 4 workbook and name it “Solverâ€.
2. Enter in the labels and numbers as shown in the following screenshot. This table shows the profit and requirements for three different products. Each product requires a certain number of hours and materials. The company only has so many resources available, so the solution has to fit within what is available.
3. Cells F5, F6, and G8 use functions or formulas. All other numbers should be entered in exactly as shown in the screenshot.
A. Cell F5 holds the calculation for the total number of hours used, based on the production size for each product. This is calculated by taking the hours required multiplied by the production size for Product 1, added to the hours required multiplied by the production size for Product 2, added to the hours required multiplied by the production size for Product 3. The easiest way to calculate this is to use the SUMPRODUCT function for the hours cells and the production size cells. (Look up the SUMPRODUCT function on Google if you need help figuring out how to use it.)
B. Cell F6 holds the calculation for the total number of materials used, based on the production size for each product. Use the SUMPRODUCT function to calculate this value similar to what you did with hours.
C. Cell G8 is the total profit. It is calculated using a formula that takes the sum of the product profits multiplied by the production sizes. Use SUMPRODUCT for this cell as well.
4. Use the Solver tool to determine the optimal mix and number of products to produce.
A. Your goal is to maximize total profit.
B. You achieve this by allowing Solver to change to production sizes for all the products.
C. You must implement the following bounds and constraints:
1. You cannot use more resources than you have available.
2. The minimum production size for each product is 5.
3. The production size must be in whole numbers or integers—no partially products produced.
5. After you have successfully used Solver to maximize total profit, save your spreadsheet in that state without changing the numbers or your Solver settings. The optimal total profit should be around $6,700.
Save your progress and proceed to Part 3.
Part 3: Data Tables (10 points)
The purpose of this part of the project is for you to practice and apply what you have learned about data tables. You will create two different data tables that show how loan interest rates and terms affect your total and monthly payments.
Requirements:
1. Add a new spreadsheet to your existing project 4 workbook and name it “DataTablesâ€.
2. Copy a mortgage table from a previous example or project or create a new one. Your table should be similar to the one in the screenshot below. Be sure to use $300,000 for the home price, a 20% down payment amount, a 3.5% interest rate, and a 30 year loan.
3. To the right of the mortgage table, create a one-variable data table using the Data Table tool that shows the monthly payments and total amounts paid for the range of interest rates between 3.250% and 4.000%. Your completed data table should match the one below. You must use the Data Table tool; do not simply manually enter the numbers into the table.
4. Below your first data table, create a second data table. This should be a two-variable data table that shows how the number of years determines the total amounts paid for the loan for the interest rates between 3.250% and 4.000%. You must use the Data Table tool, and your completed table should match the one below.
5. Save your progress and workbook.
