| BUAD - ASSIGNMENT 3 - OCTOBER 2017 |
| PART 1 |
| a) | The shaded cells below will be your variable cells in your Solver input file. |
| | Calculations for all other cells are based on these 5 cells. Thus, you need only 5 variables. |
| | | Inputs | | Outputs |
| | Final Product | Grade A (lbs) | Grade B (lbs) | Avg Quality Points /lb | Production (cases) | Demand Forecast |
| | Canned Slices | | | | | 80,000 |
| | Orange Juice | | | | | 50,000 |
| | Frozen Concentrate | | | | | 80,000 |
| | Total Grade A Used |
| | Total Grade B Used |
| | Total lbs used |
| Note: Your formulation will have a constraint that forces the average quality points for orange slices to be at least 8 and another constraint |
| | that forces the average quality points of orange juice to be at least 6. Use the following cells as the left-hand-side of these constraints |
| | If you are having problems determing this Excel formula, see the worksheet 'hint'. |
| | Left-hand-side of constraint (in terms of cells C11 and D11) that forces avg quality points of canned oranges to be at least 8: -----> |
| | Left-hand-side of constraint (in terms of cells C12 and D12) that forces avg quality points of orange juice to be at least 6: -----> |
| | | Canned Slices | Orange Juice | Frozen Concentrate |
| | Revenue |
| | Variable Costs |
| | Contribution to Profit |
| | TOTAL Contribution to Profit |
| | Fixed Cost of Oranges | | $1,800,000 |
| | Net Total Profit | | -$1,800,000 |
| b) |
| | % increase in profit = |
| c) | Should the additional 80,000 lbs be purchased? Yes or No. |
| | If yes, what's the new maximum profit? Use only your output from the 'Solution Report' worksheet to |
| | answer. No credit otherwise. |
| | If no, why not? |