Construction of linear programme model in excel solver

1
Linear Algebra
Student Name
Institution Name

2
Construction of linear programme model in excel solver
The excel solver is an ad in tool in Microsoft excel that functions as an optimization tool
applicable in deriving desired outcomes through fluctuations of the model’s assumptions. It is a
type of what if analysis that is applicable in obtaining the best outcome when there are a set of
more than two assumptions affecting the final outcome (Arsham, 2012).
The model that was developed using the excel solver can be summarised by the table below.
Production Planning Model
Decision
Variables
Month Month 1 Month 2 Month 3 Month 4
Production (units)
x 1350 1000 1400 1500
y 4000 4000 8800 9000
z 4000 2100 5400 4800
Model Inputs
Raw material consumed (Kg)
Steel
x 21600 16000 22400 24000
y 88000 88000 193600 198000
z 40000 21000 54000 48000
Protection
x 5.4 4 5.6 6
y 10 10 22 22.5
z 24 12.6 32.4 28.8
Cost of raw
materials
Steel
x £4,968,000.0 £3,680,000.0 £5,152,000.0 £5,520,000.0

3
0 0 0 0
y
£20,240,000.
00
£20,240,000.
00
£44,528,000.
00
£45,540,000.
00
z
£9,200,000.0
0
£4,830,000.0
0
£12,420,000.
00
£11,040,000.
00
Protection
x £108.00 £80.00 £112.00 £120.00
y £200.00 £200.00 £440.00 £450.00
z £480.00 £252.00 £648.00 £576.00
Activity of Machines (Hrs.)
A
x 225 166.6666667 233.3333333 250
y 1333.333333 1333.333333 2933.333333 3000
z 1000 525 1350 1200
B
x 562.5 416.6666667 583.3333333 625
y 2000 2000 4400 4500
z 1333.333333 700 1800 1600
C
x 900 666.6666667 933.3333333 1000
y 3333.333333 3333.333333 7333.333333 7500
z 2333.333333 1225 3150 2800
Cost of Machines
A
x £67,500.00 £50,000.00 £70,000.00 £75,000.00
y £400,000.00 £400,000.00 £880,000.00 £900,000.00
z £300,000.00 £157,500.00 £405,000.00 £360,000.00
B
x £140,625.00 £104,166.67 £145,833.33 £156,250.00
y £500,000.00 £500,000.00 £1,100,000.0 £1,125,000.0