University of Example: Decision Modelling Assignment Solution

This report provides a comprehensive analysis of decision modelling using spreadsheets. It examines transportation problems and network flow, demonstrating how to minimize costs through optimized solutions. The report includes practical examples, such as determining the optimal production and supply of goods to various markets and minimizing operational costs. The analysis utilizes Excel to solve these problems, showcasing the application of different techniques to achieve cost-effective outcomes. The report also touches upon the relevance of machine productivity in decision-making. The report explores the application of decision modeling in real-world scenarios, emphasizing the importance of optimized decision-making processes in finance. The report showcases the use of excel in solving the problems. The report provides details on the minimum cost incurred during the production and transportation of goods.

1
Decision Modelling with spreadsheets
Student name
Institution Name

2
5.6
The network representation of the flow problem is;
1-8
3
4
5
2-7
5
5
5
2
6
5
4
3
7
From the network drawn above, this is a transportation problem. The supply nodes and demand
nodes are indicated with arc costs illustrated. Solving the model using excel we obtain the
following.
3 4 5 Total Supply
1 5 0 3 8 = 8
2 0 5 2 7 = 7
Total 5 5 5
= = =
Demand 5 5 5
No of units
The above table shows that to minimize the cost of transportation, 8 units should be produced at
node 1 with 5 transported to node 3 and 3 transported to node 5. On the other hand, 7 units need
to be produced at node 2 with 5 of the units transported to node 4 and 2 transported to node 5.
The above action will minimize the cost at 54.
5.11
The problem to be solved is the minimum cost that should be incurred to produce and transport
the ladies’ swimwear to the respective markets.
a. The network flow representation of the problem is described below;

3
S2
S2
S3
S3
S3
S3
S3
S3
S4
S4
S4
S4
S4
S5
S5
S5
S5
S6
S6
S6
JanS
FebS
Mar Mar
D
AprS
May
JunS
Apr
May
D
Jun
D
Jul
Aug
D 10
28
33
26
20
14
-16
-18
-20
-28
-29
-36
7600
7720
7775

4
b. Model implementation is done in excel
c. Upon implementing the model and solving it using the solver add in in excel, it was
derived that the best cause of action will be to produce and supply the swimwear as per
the table below.
Arc Production Months
From To Flow Cost Held
S1 D3 0 7100 2
S1 D4 0 7100 3
S1 D5 0 7100 4
S1 D6 0 7100 5
S1 D7 6 7100 6
S1 D8 10 7100 7
S2 D3 0 7700 1
S2 D4 0 7700 2
S2 D5 0 7700 3
S2 D6 0 7700 4
S2 D7 13 7700 5
S2 D8 0 7700 6
S3 D3 14 7600 0
S3 D4 0 7600 1
S3 D5 0 7600 2
S3 D6 0 7600 3
S3 D7 6 7600 4
S3 D8 0 7600 5
S4 D4 20 7800 0
S4 D5 0 7800 1
S4 D6 0 7800 2
S4 D7 0 7800 3
S4 D8 0 7800 4
S5 D5 26 7900 0
S5 D6 0 7900 1
S5 D7 0 7900 2
S5 D8 0 7900 3
S6 D6 33 7400 0
S6 D7 3 7400 1
S6 D8 0 7400 2
S1 to S6 represents the manufacturing months while D3 to D8 represents the supply
months. This are the months when the produced goods are sold to the respective markets.
The Flow column represents the units that are produced in a given month (Si) and sold in
the month (Dj). Where (i) and j represents the various months while S and D indicate

5
production and supply respectively. The above production and supply structure will
minimize the cost of business operation to 1006675.
5.13
a) Network representation of the problem
Replace
Now
Keep
current 2 yr
old machine
Decision 1st year 2nd year 3rd year 4th year 5th year
about existing
machine
0
1
2
3 4 5 6 7-1 +1
b) Implementation of the model in excel gives the following output as the optimal solution
From To Flow Age
0 1 0 2
0 2 1 2
1 3 0 3
1 4 0 4
1 5 0 5
2 3 0 1
2 4 0 2
2 5 0 3
2 6 0 4
2 7 1 5
3 4 0 1
3 5 0 2
3 6 0 3
3 7 0 4
4 5 0 1
4 6 0 2
4 7 0 3
5 6 0 1
5 7 0 2
6 7 0 1
Arcs

6
This decision minimizes the operation costs to 6400.
c) An analysist might also want to consider how the productivity of the machines diminish
with time. This might be a relevant information on the decision whether to replace or
retain a machine.