Finance report
slickman123
final_coursework_1.pptxBA 5001 Final Coursework
Final coursework: Lamberts Heating
Submission date: Friday 24th May 2013
This coursework is worth 40% of your final mark for this module.
Part A: 50 marks
Part B: 20 marks
Part C: 30 marks
Part B : Installing the new machinery
| Activity | Normal Duration in weeks | Preceding activity | Minimum Duration [Each day saved cost £200] | |
| A: | Order new machinery | 2 | None | 2 |
| B: | Plan new physical layout of factory | 3 | None | 3 |
| C: | Determine changes needed in existing machinery | 3 | None | 3 |
| D | Receive new equipment | 10 | A | 8 |
| E | Hire new employee to supervise the operation of the new machinery | 7 | A | 6 |
| F | Make changes needed to accommodate new machinery | 15 | B | 13 |
| G: | Make changes needed in existing machinery | 9 | C | 7 |
| H: | Train existing employees to use new machinery | 7 | D,E | 6 |
| I: | Install new machinery | 4 | F | 3 |
| J: | Disassemble old machinery | 5 | G | 4 |
| K: | Conduct employee safety training on new installation | 2 | H,I | 2 |
Part B : Instructions
You may complete this task manually or by using MS project.
Assuming that the project starts on Monday 3rd June 2013, determine the shortest duration for the entire project, using the normal duration times. Specify the date at which the project can finish and the number of weeks required.
Produce a Gantt chart showing the starting and finishing times of each activity
Identify which activities form the critical path(s) of the project.
Part B : Example from booklet
Estate Agency : pages 113 to 115 for manual example
Estate Agency: pages 124 to 127 for MS Project
Estate Agents: Network Diagram
| A | 0 | 9 | C | 9 | 14 | |||||||||
| 9 | 0 | 9 | 5 | 10 | 15 | |||||||||
| D | 9 | 12 | E | 12 | 15 | G | 15 | 16 | ||||||
| 3 | 9 | 12 | 3 | 12 | 15 | 1 | 15 | 16 | ||||||
| B | 0 | 7 | F | 7 | 11 | |||||||||
| 7 | 4 | 11 | 4 | 11 | 15 | |||||||||
The critical path is ADEG. The shortest finishing time for the project is 16 weeks.
Estate Agents: Floats
Float = Latest finish time (LFT) – Earliest finish time (EFT)
| Activity | EFT | LFT | float | Critical |
| A | 9 | 9 | 0 | yes |
| B | 7 | 11 | 4 | |
| C | 14 | 15 | 1 | |
| D | 12 | 12 | 0 | yes |
| E | 15 | 15 | 0 | yes |
| F | 11 | 15 | 4 | |
| G | 16 | 16 | 0 | yes |
Estate Agents : gantt Chart
Estate Agents
no activity A B C D E F G 0 0 9 9 12 7 15 duration A B C D E F G 9 7 5 3 3 4 1 float A B C D E F G 0 4 1 0 0 4 0
days
UsinG MS project: network diagram
UsinG MS project: Gantt Chart
Part B : continued
If the project could be speeded up by a maximum of 3 weeks at a cost of £200 a day, where should this money be spent? Produce a new network diagram to show the new critical path(s) and total duration.
example of crashing on pages 117 to 120 (coursework is easier than this )
make sure you take note of the minimum durations
calculate the extra cost of speeding up the project
Part B : Report (about 500 words)
Give details of the proposed calendar of works, making it clear which activities are critical and which activities have some slack. This section should be written so that the management can understand the advice being given without reference to the work in the appendix. Give details of the starting date, the finishing date and the duration of the project.
Explain how, by spending more money, the project duration can be shortened. Explain which activities have been shortened and which activities are now critical. Give details of the starting date, the finishing date and the duration of the project. Give details of the extra cost involved in shortening the project.
Final Coursework : part C
Cost of procuring boilers
This part of the coursework requires you to use linear programming techniques to determine the best way to minimise the cost of supplying boilers to Lamberts Heating
As well as manufacturing radiators (as mentioned in Part A), Lamberts Heating is also reviewing the way it procures boilers to enable the installation of domestic heating systems.
Part C : Identify the decision variables
The purchasing department has identified three possible suppliers of boilers; Apex, Brunswich and Centrale. It has also identified five kinds of domestic boiler that it wants to purchase so it can supply and fit these boilers to a variety of sizes of homes.
The five boilers are known and coded by Italian numbers as most are manufactured in Italy ; Uno (1), Duo (2), Tre (3), Quattro (4) and Cinque (5).
PArt C : Identify the consTraints
The table below gives the cost of each boiler (in £s) from each supplier. The final row of the table specifies the minimum requirement of each type of boiler per year. Please note that not all boilers are available from each supplier.
| Uno | Duo | Tre | Quattro | Cinque | |
| Apex | 500 | 750 | 300 | --- | 450 |
| Brunswich | --- | 725 | 320 | 875 | 420 |
| Centrale | 480 | 775 | 310 | 900 | --- |
| Requirement (number of boilers) | 2000 | 1500 | 3000 | 2500 | 2200 |
PArt C : Identify the consTraints
There are certain limitations that have to be taken into account;
Apex can supply no more than 1,000 of the Tre boiler each year.
Brunswich can supply no more than 800 of the Duo boiler each year.
Centrale can supply no more than 1,800 Uno boilers each year.
Lamberts Heating wants to meet its requirement for the number of boilers needed each year at the minimum cost.
Example from Module booklet page 105
| Home Farm | yield per acre | cost per acre | Meadow Farm | yield per acre | cost per acre | |
| corn | 400 barrels | £100 | corn | 650 barrels | £120 | |
| wheat | 300 barrels | £90 | wheat | 350 barrels | £80 |
Farm Production
A farmer has two farms, Home Farm and Meadow Farm, in which he grows corn and wheat.
Both farms are 40 acres in size.
To satisfy a contract with a local mill, the farmer must produce 7,000 barrels of corn and 11,000 barrels of wheat each year.
The farmer wishes to minimise the cost of meeting the contract. The data for each farm is given below:
Definition of Decision variables
Variables: We need to distinguish between production at Home and Meadow Farm.
Let CH be the number of acres of corn planted at Home Farm
Let CM be the number of acres of corn planted at Meadow Farm
Let WH be the number of acres of wheat planted at Home Farm
Let WM be the number of acres of wheat planted at Meadow Farm
Formulation of problem
Minimise Cost:
Minimise 100CH + 90WH + 120CM + 80WM
subject to:
yield corn(1) 400CH + 650CM ≥ 7,000
wheat (2) 300WH + 350WM ≥11,000
area Home Farm (3) CH + WH ≤ 40
Meadow Farm (4) CM + WM ≤ 40
CH ≥ 0, CM ≥ 0, WH ≥ 0 WM≥ 0
Farm production : Input
Farm’s Answer Report
Farm’s Sensitivity Report
Coursework requirements: Appendix
Define the meaning of any decision variables you are using.
Formulate the situation as a linear programming problem.
Include your computer input of the problem (and show any formulae used)
Include the “Answer Report” and “Sensitivity Report” printouts
Coursework requirements: Report [about 500 words]
Write a brief report to the management of Lambert Heating detailing the cheapest way of meeting their need for boilers.
This report, written in a formal style of English, should include:
The nature of the problem being solved
The suggested purchasing plan
The cost of the suggested purchasing plan.
The robustness of the plan. Provide details of the ranges of costs of boilers from each suggested supplier for which your suggested plan remains optimal, and advise the company when they will have to generate a new plan
coursework requirements
Do not just provide lists but consider your answers in the context of the question.
At present, Apex can supply no more than 1,000 of the Tre boiler each year. If this limit was changed so that Apex could now supply 1,100 Tre boilers, explain what effect this would have on the total minimum cost?
Farm Production : Notes for a report
This is taken from page 106 of the module booklet
1. Planting Plan
In order to minimise the planting costs, the following should be implemented:
Plant Home Farm with 2.56 acres of wheat
Plant Meadow Farm with 10.77 acres of corn and 29.23 acres of wheat.
This will incur costs of £3,861.54, the minimum that can be achieved.
Farm Production : Notes
2. Implications of Suggested Planting Programme
The planting plan given above will result in exactly 7,000 barrels of corn and exactly 11,000 barrels of wheat being produced.
[S1 = 0, S2 = 0]
37.44 acres of Home Farm will not be planted, whilst all 40 acres of Meadow Farm will be used.
Farm Production : Notes
3. Scope of the recommendations
3.1 Change in Costs
The planting plan given above will result in a minimum cost whilst the following conditions hold:
* The cost of planting corn at Home Farm stays above £89.23 per acre.
* The cost of planting corn at Meadow Farm stays below £137.50 per acre.
* The cost of planting wheat at Home Farm stays in the range £68.57 to £105 per acres.
* The cost of planting wheat at Meadow Farm stays in the range £62.50 to £105 per acre.
Farm Production : Notes
If any one of the conditions above fails to hold then a new planting plan will be required. Furthermore, if two or more of the present costs change, a new plan will be required.
As these ranges are relatively large, the proposed plan should hold good for some time
Farm Production : Notes
3.2 Change in Contract
The proposed plan exactly meets the contracts for 7,000 barrels of corn and 11,000 barrels of wheat.
If the requirement for corn was to increase by one barrel, the extra costs incurred in meeting this target would be 22.3 pence. This marginal cost of 22.3 pence per barrel applies for levels of production between 5,571.43 and 26,000 barrels.
If the minimum amount of wheat required was increased, the extra cost would be 30 pence per barrel. This extra cost per barrel will apply whilst the contract for wheat lies in the range 10,230.8 to 22,230.8 barrels.
If the minimum requirement for corn or wheat fell outside these ranges, a new planting plan would be required.
Farm Production : Notes
3.3 Changes in Farm Size
In total only 2.56 acres of Home Farm are being used. Clearly it would not make sense to consider increasing the size of Home Farm. All 40 acres of Meadow Farm are being used. If the size of Meadow Farm could be increased by 1 acre the minimum cost could be reduced by £25. This marginal reduction in cost applies when the size of Meadow Farm lies in the range 10.77 acres to 42.2 acres.
Similarly, if the size of Meadow Farm is reduced by 1 acre, the minimum cost will rise by £25.
Farm Production : Notes
If we could increase the size of Meadow Farm by 1 acre, we could produce an extra 350 barrels at Meadow Farm at a cost of £80.
At the same time we would reduce the yield from Home Farm by 350 barrels
i.e. 350/300 = 1.16666 acres. This reduction would save 1.16666*90 =105.
Thus the net reduction would be 105 - 80 = £25 ]
Marking Scheme: Part B
40
| Input data for original problem shown | 2 |
| Correct Network diagram produced | 4 |
| Reduced time input data | 2 |
| Reduced time correct network diagram | 2 |
| total | 10 |
| Mention start data and finish date . Mention duration = 24 weeks (2) | 4 |
| Mention critical activities, giving full description of each activity for full marks (3) making it clear what critical means (2). List activities that have some slack (3) make it clear what slack means (2) | 12 |
| Advice client as to how reduce time | 2 |
| Mention new start data and new finish date , duration and cost | 4 |
| Mention the new critical activities giving full description of each activity for full marks (2) making it clear what critical means (2) | 8 |
| total | 30 |
| 40 |
Marking scheme: Part C
| appendix | Defining variables | 6 |
| Stating objective function | 2 | |
| Stating constraints | 9 | |
| Showing correct excel input data | 2 | |
| Showing all of the output data | 4 | |
| total | 23 | |
| report | Explaining the problem . | 5 |
| Explaining the suggest purchasing plan . | 8 | |
| Stating the minimum cost | 3 | |
| Robustness: This plan remains optimal whilst the costs of Uno from Apex are ???, Due from Apex are ??? etc | 17 | |
| If the Apex could supply 100 more Tre boilers year, how would the minimum cost change | 4 | |
| total | 37 | |
| total | 60 |
farms
corn homecorn meadowwheat homewheat meadow
0000
minimise10012090800
corn4006500>=7000
wheat3003500>=11000
home farm 110<=40
meadow farm110<=40
Answer Report 1
| Microsoft Excel 14.0 Answer Report | ||||||
| Worksheet: [Book1]Sheet1 | ||||||
| Report Created: 05/12/2012 15:33:15 | ||||||
| Result: Solver found a solution. All Constraints and optimality conditions are satisfied. | ||||||
| Solver Engine | ||||||
| Engine: Simplex LP | ||||||
| Solution Time: 0.032 Seconds. | ||||||
| Iterations: 4 Subproblems: 0 | ||||||
| Solver Options | ||||||
| Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling | ||||||
| Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative | ||||||
| Objective Cell (Max) | ||||||
| Cell | Name | Original Value | Final Value | |||
| $E$4 | 0 | 12750 | ||||
| Variable Cells | ||||||
| Cell | Name | Original Value | Final Value | Integer | ||
| $B$3 | Alpha | 0 | 750 | Contin | ||
| $C$3 | Beta | 0 | 1000 | Contin | ||
| $D$3 | Gamma | 0 | 1500 | Contin | ||
| Constraints | ||||||
| Cell | Name | Cell Value | Formula | Status | Slack | |
| $E$5 | total investments | 100000 | $E$5<=$G$5 | Binding | 0 | |
| $E$6 | max Alpha | 750 | $E$6<=$G$6 | Not Binding | 250 | |
| $E$7 | max Beta | 1000 | $E$7<=$G$7 | Binding | 0 | |
| $E$8 | max Gamma | 1500 | $E$8<=$G$8 | Binding | 0 |
Sensitivity Report 1
| Microsoft Excel 14.0 Sensitivity Report | |||||||
| Worksheet: [Book1]Sheet1 | |||||||
| Report Created: 05/12/2012 15:33:15 | |||||||
| Variable Cells | |||||||
| Final | Reduced | Objective | Allowable | Allowable | |||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | |
| $B$3 | Alpha | 750 | 0 | 7 | 0.2 | 7 | |
| $C$3 | Beta | 1000 | 0 | 3 | 1E+30 | 0.0833333333 | |
| $D$3 | Gamma | 1500 | 0 | 3 | 1E+30 | 0.6666666667 | |
| Constraints | |||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |
| $E$5 | total investments | 100000 | 0.1166666667 | 100000 | 15000 | 45000 | |
| $E$6 | max Alpha | 750 | 0 | 1000 | 1E+30 | 250 | |
| $E$7 | max Beta | 1000 | 0.0833333333 | 1000 | 1800 | 600 | |
| $E$8 | max Gamma | 1500 | 0.6666666667 | 1500 | 2250 | 750 |
fred
| Alpha | Beta | Gamma | ||||
| 750 | 1000 | 1500 | ||||
| 7 | 3 | 3 | 12750 | |||
| total investments | 60 | 25 | 20 | 100000 | <= | 100000 |
| max Alpha | 1 | 750 | <= | 1000 | ||
| max Beta | 1 | 1000 | <= | 1000 | ||
| max Gamma | 1 | 1500 | <= | 1500 |
Answer Report 2
| Microsoft Excel 14.0 Answer Report | ||||||
| Worksheet: [LP problems.xlsx]farms | ||||||
| Report Created: 05/12/2012 15:48:05 | ||||||
| Result: Solver found a solution. All Constraints and optimality conditions are satisfied. | ||||||
| Solver Engine | ||||||
| Engine: Simplex LP | ||||||
| Solution Time: 0.016 Seconds. | ||||||
| Iterations: 3 Subproblems: 0 | ||||||
| Solver Options | ||||||
| Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling | ||||||
| Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative | ||||||
| Objective Cell (Min) | ||||||
| Cell | Name | Original Value | Final Value | |||
| $F$5 | minimise | 0 | 3861.5384615385 | |||
| Variable Cells | ||||||
| Cell | Name | Original Value | Final Value | Integer | ||
| $B$4 | corn home | 0 | 0 | Contin | ||
| $C$4 | corn meadow | 0 | 10.7692307692 | Contin | ||
| $D$4 | wheat home | 0 | 2.5641025641 | Contin | ||
| $E$4 | wheat meadow | 0 | 29.2307692308 | Contin | ||
| Constraints | ||||||
| Cell | Name | Cell Value | Formula | Status | Slack | |
| $F$6 | corn | 7000 | $F$6>=$H$6 | Binding | 0 | |
| $F$7 | wheat | 11000 | $F$7>=$H$7 | Binding | 0 | |
| $F$8 | home farm | 2.5641025641 | $F$8<=$H$8 | Not Binding | 37.4358974359 | |
| $F$9 | meadow farm | 40 | $F$9<=$H$9 | Binding | 0 |
Sensitivity Report 2
| Microsoft Excel 14.0 Sensitivity Report | |||||||
| Worksheet: [LP problems.xlsx]farms | |||||||
| Report Created: 05/12/2012 15:48:05 | |||||||
| Variable Cells | |||||||
| Final | Reduced | Objective | Allowable | Allowable | |||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | |
| $B$4 | corn home | 0 | 10.7692307692 | 100 | 1E+30 | 10.7692307692 | |
| $C$4 | corn meadow | 10.7692307692 | 0 | 120 | 17.5 | 145 | |
| $D$4 | wheat home | 2.5641025641 | 0 | 90 | 15 | 21.4285714286 | |
| $E$4 | wheat meadow | 29.2307692308 | 0 | 80 | 25 | 17.5 | |
| Constraints | |||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |
| $F$6 | corn | 7000 | 0.2230769231 | 7000 | 19000 | 1428.5714285714 | |
| $F$7 | wheat | 11000 | 0.3 | 11000 | 11230.7692307692 | 769.2307692308 | |
| $F$8 | home farm | 2.5641025641 | 0 | 40 | 1E+30 | 37.4358974359 | |
| $F$9 | meadow farm | 40 | -25 | 40 | 2.1978021978 | 29.2307692308 |
farms
| farms | |||||||
| corn home | corn meadow | wheat home | wheat meadow | ||||
| 0 | 0 | 0 | 0 | ||||
| minimise | 100 | 120 | 90 | 80 | 0 | ||
| corn | 400 | 650 | 0 | >= | 7000 | ||
| wheat | 300 | 350 | 0 | >= | 11000 | ||
| home farm | 1 | 1 | 0 | <= | 40 | ||
| meadow farm | 1 | 1 | 0 | <= | 40 | ||
Sheet3
Objective Cell (Min)
CellNameOriginal ValueFinal Value
$F$5minimise03861.538462
Variable Cells
CellNameOriginal ValueFinal ValueInteger
$B$4corn home00Contin
$C$4corn meadow010.76923077Contin
$D$4wheat home02.564102564Contin
$E$4wheat meadow029.23076923Contin
Constraints
CellNameCell ValueFormulaStatusSlack
$F$6corn7000$F$6>=$H$6Binding0
$F$7wheat11000$F$7>=$H$7Binding0
$F$8home farm 2.564102564$F$8<=$H$8Not Binding37.43589744
$F$9meadow farm40$F$9<=$H$9Binding0
Answer Report 1
| Microsoft Excel 14.0 Answer Report | ||||||
| Worksheet: [Book1]Sheet1 | ||||||
| Report Created: 05/12/2012 15:33:15 | ||||||
| Result: Solver found a solution. All Constraints and optimality conditions are satisfied. | ||||||
| Solver Engine | ||||||
| Engine: Simplex LP | ||||||
| Solution Time: 0.032 Seconds. | ||||||
| Iterations: 4 Subproblems: 0 | ||||||
| Solver Options | ||||||
| Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling | ||||||
| Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative | ||||||
| Objective Cell (Max) | ||||||
| Cell | Name | Original Value | Final Value | |||
| $E$4 | 0 | 12750 | ||||
| Variable Cells | ||||||
| Cell | Name | Original Value | Final Value | Integer | ||
| $B$3 | Alpha | 0 | 750 | Contin | ||
| $C$3 | Beta | 0 | 1000 | Contin | ||
| $D$3 | Gamma | 0 | 1500 | Contin | ||
| Constraints | ||||||
| Cell | Name | Cell Value | Formula | Status | Slack | |
| $E$5 | total investments | 100000 | $E$5<=$G$5 | Binding | 0 | |
| $E$6 | max Alpha | 750 | $E$6<=$G$6 | Not Binding | 250 | |
| $E$7 | max Beta | 1000 | $E$7<=$G$7 | Binding | 0 | |
| $E$8 | max Gamma | 1500 | $E$8<=$G$8 | Binding | 0 |
Sensitivity Report 1
| Microsoft Excel 14.0 Sensitivity Report | |||||||
| Worksheet: [Book1]Sheet1 | |||||||
| Report Created: 05/12/2012 15:33:15 | |||||||
| Variable Cells | |||||||
| Final | Reduced | Objective | Allowable | Allowable | |||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | |
| $B$3 | Alpha | 750 | 0 | 7 | 0.2 | 7 | |
| $C$3 | Beta | 1000 | 0 | 3 | 1E+30 | 0.0833333333 | |
| $D$3 | Gamma | 1500 | 0 | 3 | 1E+30 | 0.6666666667 | |
| Constraints | |||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |
| $E$5 | total investments | 100000 | 0.1166666667 | 100000 | 15000 | 45000 | |
| $E$6 | max Alpha | 750 | 0 | 1000 | 1E+30 | 250 | |
| $E$7 | max Beta | 1000 | 0.0833333333 | 1000 | 1800 | 600 | |
| $E$8 | max Gamma | 1500 | 0.6666666667 | 1500 | 2250 | 750 |
fred
| Alpha | Beta | Gamma | ||||
| 750 | 1000 | 1500 | ||||
| 7 | 3 | 3 | 12750 | |||
| total investments | 60 | 25 | 20 | 100000 | <= | 100000 |
| max Alpha | 1 | 750 | <= | 1000 | ||
| max Beta | 1 | 1000 | <= | 1000 | ||
| max Gamma | 1 | 1500 | <= | 1500 |
Answer Report 2
| Microsoft Excel 14.0 Answer Report | ||||||
| Worksheet: [LP problems.xlsx]farms | ||||||
| Report Created: 05/12/2012 15:48:05 | ||||||
| Result: Solver found a solution. All Constraints and optimality conditions are satisfied. | ||||||
| Solver Engine | ||||||
| Engine: Simplex LP | ||||||
| Solution Time: 0.016 Seconds. | ||||||
| Iterations: 3 Subproblems: 0 | ||||||
| Solver Options | ||||||
| Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling | ||||||
| Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative | ||||||
| Objective Cell (Min) | ||||||
| Cell | Name | Original Value | Final Value | |||
| $F$5 | minimise | 0 | 3861.5384615385 | |||
| Variable Cells | ||||||
| Cell | Name | Original Value | Final Value | Integer | ||
| $B$4 | corn home | 0 | 0 | Contin | ||
| $C$4 | corn meadow | 0 | 10.7692307692 | Contin | ||
| $D$4 | wheat home | 0 | 2.5641025641 | Contin | ||
| $E$4 | wheat meadow | 0 | 29.2307692308 | Contin | ||
| Constraints | ||||||
| Cell | Name | Cell Value | Formula | Status | Slack | |
| $F$6 | corn | 7000 | $F$6>=$H$6 | Binding | 0 | |
| $F$7 | wheat | 11000 | $F$7>=$H$7 | Binding | 0 | |
| $F$8 | home farm | 2.5641025641 | $F$8<=$H$8 | Not Binding | 37.4358974359 | |
| $F$9 | meadow farm | 40 | $F$9<=$H$9 | Binding | 0 |
Sensitivity Report 2
| Microsoft Excel 14.0 Sensitivity Report | |||||||
| Worksheet: [LP problems.xlsx]farms | |||||||
| Report Created: 05/12/2012 15:48:05 | |||||||
| Variable Cells | |||||||
| Final | Reduced | Objective | Allowable | Allowable | |||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | |
| $B$4 | corn home | 0 | 10.7692307692 | 100 | 1E+30 | 10.7692307692 | |
| $C$4 | corn meadow | 10.7692307692 | 0 | 120 | 17.5 | 145 | |
| $D$4 | wheat home | 2.5641025641 | 0 | 90 | 15 | 21.4285714286 | |
| $E$4 | wheat meadow | 29.2307692308 | 0 | 80 | 25 | 17.5 | |
| Constraints | |||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |
| $F$6 | corn | 7000 | 0.2230769231 | 7000 | 19000 | 1428.5714285714 | |
| $F$7 | wheat | 11000 | 0.3 | 11000 | 11230.7692307692 | 769.2307692308 | |
| $F$8 | home farm | 2.5641025641 | 0 | 40 | 1E+30 | 37.4358974359 | |
| $F$9 | meadow farm | 40 | -25 | 40 | 2.1978021978 | 29.2307692308 |
farms
| farms | |||||||
| corn home | corn meadow | wheat home | wheat meadow | ||||
| 0 | 10.7692307692 | 2.5641025641 | 29.2307692308 | ||||
| minimise | 100 | 120 | 90 | 80 | 3861.5384615385 | ||
| corn | 400 | 650 | 7000 | >= | 7000 | ||
| wheat | 300 | 350 | 11000 | >= | 11000 | ||
| home farm | 1 | 1 | 2.5641025641 | <= | 40 | ||
| meadow farm | 1 | 1 | 40 | <= | 40 | ||
Sheet3
Variable Cells
FinalReducedObjectiveAllowableAllowable
CellNameValueCostCoefficientIncreaseDecrease
$B$4corn home010.769230771001E+3010.76923077
$C$4corn meadow10.76923077012017.5145
$D$4wheat home2.5641025640901521.42857143
$E$4wheat meadow29.230769230802517.5
Constraints
FinalShadowConstraintAllowableAllowable
CellNameValuePriceR.H. SideIncreaseDecrease
$F$6corn70000.2230769237000190001428.571429
$F$7wheat110000.31100011230.76923769.2307692
$F$8home farm 2.5641025640401E+3037.43589744
$F$9meadow farm40-25402.19780219829.23076923
Answer Report 1
| Microsoft Excel 14.0 Answer Report | ||||||
| Worksheet: [Book1]Sheet1 | ||||||
| Report Created: 05/12/2012 15:33:15 | ||||||
| Result: Solver found a solution. All Constraints and optimality conditions are satisfied. | ||||||
| Solver Engine | ||||||
| Engine: Simplex LP | ||||||
| Solution Time: 0.032 Seconds. | ||||||
| Iterations: 4 Subproblems: 0 | ||||||
| Solver Options | ||||||
| Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling | ||||||
| Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative | ||||||
| Objective Cell (Max) | ||||||
| Cell | Name | Original Value | Final Value | |||
| $E$4 | 0 | 12750 | ||||
| Variable Cells | ||||||
| Cell | Name | Original Value | Final Value | Integer | ||
| $B$3 | Alpha | 0 | 750 | Contin | ||
| $C$3 | Beta | 0 | 1000 | Contin | ||
| $D$3 | Gamma | 0 | 1500 | Contin | ||
| Constraints | ||||||
| Cell | Name | Cell Value | Formula | Status | Slack | |
| $E$5 | total investments | 100000 | $E$5<=$G$5 | Binding | 0 | |
| $E$6 | max Alpha | 750 | $E$6<=$G$6 | Not Binding | 250 | |
| $E$7 | max Beta | 1000 | $E$7<=$G$7 | Binding | 0 | |
| $E$8 | max Gamma | 1500 | $E$8<=$G$8 | Binding | 0 |
Sensitivity Report 1
| Microsoft Excel 14.0 Sensitivity Report | |||||||
| Worksheet: [Book1]Sheet1 | |||||||
| Report Created: 05/12/2012 15:33:15 | |||||||
| Variable Cells | |||||||
| Final | Reduced | Objective | Allowable | Allowable | |||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | |
| $B$3 | Alpha | 750 | 0 | 7 | 0.2 | 7 | |
| $C$3 | Beta | 1000 | 0 | 3 | 1E+30 | 0.0833333333 | |
| $D$3 | Gamma | 1500 | 0 | 3 | 1E+30 | 0.6666666667 | |
| Constraints | |||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |
| $E$5 | total investments | 100000 | 0.1166666667 | 100000 | 15000 | 45000 | |
| $E$6 | max Alpha | 750 | 0 | 1000 | 1E+30 | 250 | |
| $E$7 | max Beta | 1000 | 0.0833333333 | 1000 | 1800 | 600 | |
| $E$8 | max Gamma | 1500 | 0.6666666667 | 1500 | 2250 | 750 |
fred
| Alpha | Beta | Gamma | ||||
| 750 | 1000 | 1500 | ||||
| 7 | 3 | 3 | 12750 | |||
| total investments | 60 | 25 | 20 | 100000 | <= | 100000 |
| max Alpha | 1 | 750 | <= | 1000 | ||
| max Beta | 1 | 1000 | <= | 1000 | ||
| max Gamma | 1 | 1500 | <= | 1500 |
Answer Report 2
| Microsoft Excel 14.0 Answer Report | ||||||
| Worksheet: [LP problems.xlsx]farms | ||||||
| Report Created: 05/12/2012 15:48:05 | ||||||
| Result: Solver found a solution. All Constraints and optimality conditions are satisfied. | ||||||
| Solver Engine | ||||||
| Engine: Simplex LP | ||||||
| Solution Time: 0.016 Seconds. | ||||||
| Iterations: 3 Subproblems: 0 | ||||||
| Solver Options | ||||||
| Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling | ||||||
| Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative | ||||||
| Objective Cell (Min) | ||||||
| Cell | Name | Original Value | Final Value | |||
| $F$5 | minimise | 0 | 3861.5384615385 | |||
| Variable Cells | ||||||
| Cell | Name | Original Value | Final Value | Integer | ||
| $B$4 | corn home | 0 | 0 | Contin | ||
| $C$4 | corn meadow | 0 | 10.7692307692 | Contin | ||
| $D$4 | wheat home | 0 | 2.5641025641 | Contin | ||
| $E$4 | wheat meadow | 0 | 29.2307692308 | Contin | ||
| Constraints | ||||||
| Cell | Name | Cell Value | Formula | Status | Slack | |
| $F$6 | corn | 7000 | $F$6>=$H$6 | Binding | 0 | |
| $F$7 | wheat | 11000 | $F$7>=$H$7 | Binding | 0 | |
| $F$8 | home farm | 2.5641025641 | $F$8<=$H$8 | Not Binding | 37.4358974359 | |
| $F$9 | meadow farm | 40 | $F$9<=$H$9 | Binding | 0 |
Sensitivity Report 2
| Microsoft Excel 14.0 Sensitivity Report | |||||||
| Worksheet: [LP problems.xlsx]farms | |||||||
| Report Created: 05/12/2012 15:48:05 | |||||||
| Variable Cells | |||||||
| Final | Reduced | Objective | Allowable | Allowable | |||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | |
| $B$4 | corn home | 0 | 10.7692307692 | 100 | 1E+30 | 10.7692307692 | |
| $C$4 | corn meadow | 10.7692307692 | 0 | 120 | 17.5 | 145 | |
| $D$4 | wheat home | 2.5641025641 | 0 | 90 | 15 | 21.4285714286 | |
| $E$4 | wheat meadow | 29.2307692308 | 0 | 80 | 25 | 17.5 | |
| Constraints | |||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |
| $F$6 | corn | 7000 | 0.2230769231 | 7000 | 19000 | 1428.5714285714 | |
| $F$7 | wheat | 11000 | 0.3 | 11000 | 11230.7692307692 | 769.2307692308 | |
| $F$8 | home farm | 2.5641025641 | 0 | 40 | 1E+30 | 37.4358974359 | |
| $F$9 | meadow farm | 40 | -25 | 40 | 2.1978021978 | 29.2307692308 |
farms
| farms | |||||||
| corn home | corn meadow | wheat home | wheat meadow | ||||
| 0 | 10.7692307692 | 2.5641025641 | 29.2307692308 | ||||
| minimise | 100 | 120 | 90 | 80 | 3861.5384615385 | ||
| corn | 400 | 650 | 7000 | >= | 7000 | ||
| wheat | 300 | 350 | 11000 | >= | 11000 | ||
| home farm | 1 | 1 | 2.5641025641 | <= | 40 | ||
| meadow farm | 1 | 1 | 40 | <= | 40 | ||
