Part I- Using Goal Seek in Excel. Solve each problem in a different sheet in Excel.
Problem 1- 11a-6a=5. Find the value of a.
Problem 2- 2(y+3)=14. Find the value of y.
Problem 3- 3h-h=20. Find the value of h.
Problem 4- -7b+4b=15. Find the value of b.
Part II- Using Scenario Manager in Excel.
Problem 1- By using Scenario Manager in Excel and the data provided in the table below, create 4 scenarios for the number of items sold during each season (i.e. Spring, Summer, Fall, and Winter)
|
Item
|
Number Sold
|
$/Item
|
Revenue
|
|
Jeans
|
0
|
$ 39.99
|
$ -
|
|
Shorts
|
0
|
$ 29.99
|
$ -
|
|
T-Shirt
|
0
|
$ 19.99
|
$ -
|
|
Jacket
|
0
|
$ 99.99
|
$ -
|
|
Belt
|
0
|
$ 19.99
|
$ -
|
|
|
|
Total
|
$ -
|
Use the following Scenarios;
Spring - 1000 Jeans, 5000 Shorts, 10000 t-shirts, 500 Jackets, 800 Belts.
Summer - 600 Jeans, 15000 Shorts, 15000 t-shirts, 0 Jackets, 1800 Belts.
Fall - 1000 Jeans, 5000 Shorts, 8000 t-shirts, 700 Jackets, 700 Belts.
Winter - 10000 Jeans, 5000 Shorts, 5000 t-shirts, 5000 Jackets, 80 Belts.
Revenue for each item is calculated by multiplying the number of items sold times the cost of each item. The total revenue is the sum of the revenues for each item.
Part III- Using Excel's Solver-
Using the data from Part II, find the optimal number of each item type that must be sold in order to maximize the revenue.
Consider the following constraints;
Constraint 1: Total number of items that can be sold is 20000.
Constraint 2: The minimum number of belts I can sell is 2000.
Constraint 3: The amount of t-shirts has to be higher than the amount of shorts.
Constraint 4: I can't sell more than 4500 Jackets