Question:
UMC wants to evaluate the sales statistics for the past year and the current year to predict future sales. The general manager knows enough about Excel to realise it could be a useful tool so he has asked you to create a Microsoft Excel
prototype which lists the milk product types with the client ordering details and the revenues collected. The partly completed spreadsheet from a random average week (week 32) is available as a separate attachment. Sales data is collected on a 52 week cycle.
Produce one (1) process model (not hand drawn) using Swim Lane format from the details provided in the case study:
1. The first Swim Lane diagram ‘As-Is’ is provided. Use this model and the provided template to assist you in developing a second Swim Lane diagram that represents the proposed ‘To-Be’ application processes at UMC.(Partly completed template will be provided).
Answer:
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
Raw Data | User Controls |
|
|
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Customer |
Supplier Price Per Unit |
Retail Sale Price Per Unit |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Woolworths Petrol |
$ 11.10 |
$ 11.88 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Woolworths Petrol |
$ 27.09 |
$ 28.99 |
|
Milk Products |
|
|
|
|
|
|
|
|
|
|
|
|
| Shell |
$ 27.09 |
$ 30.48 |
|
Product Code |
Product Description |
Units per Crate/Carton |
Unit Packaging (Litre) |
Wholesale Cost per Unit |
Total No# Sales |
Supplier % Overhead costs |
Supplier Price Per Unit |
Supplier Price Per Crate/Carton |
|
|
|
|
| Shell |
$ 27.09 |
$ 30.48 |
|
|
Customer Order (week32) review |
|
|
| Joe's milkbar |
$ 27.09 |
$ 33.19 |
|
WM |
Whole Milk |
9 |
3 |
$ 1.85 |
13 |
5 |
$ 11.10 |
$ 99.90 |
|
Customers |
Total Orders 2016 (W32) |
Total Orders 2017(W32) |
Total Orders 2018 (W32) |
| 7eleven |
$ 27.09 |
$ 31.97 |
|
RF |
Reduce Fat Milk |
9 |
3 |
$ 2.34 |
10 |
5 |
$ 14.04 |
$ 126.36 |
|
| Woolworths Petrol |
$ 33.15 |
$ 35.47 |
|
A2 |
Special Milk |
9 |
2 |
$ 3.01 |
5 |
8 |
$ 27.09 |
$ 243.81 |
|
Woolworths Petrol |
15 |
18 |
21 |
| Woolworths Petrol |
$ 33.15 |
$ 35.47 |
|
FM |
Flavoured Milk |
24 |
0.7 |
$ 2.55 |
9 |
12 |
$ 33.15 |
$ 795.60 |
|
Shell |
10 |
13 |
15 |
| Shell |
$ 33.15 |
$ 37.29 |
|
SM |
Skim milk |
12 |
2 |
$ 2.10 |
4 |
9 |
$ 21.00 |
$ 252.00 |
|
7eleven |
11 |
13 |
15 |
| Shell |
$ 33.15 |
$ 37.29 |
|
UM |
UHT Milk |
12 |
1 |
$ 1.96 |
4 |
12 |
$ 25.48 |
$ 305.76 |
|
Joe's milkbar |
5 |
6 |
7 |
| Shell |
$ 33.15 |
$ 37.29 |
|
LF |
Lactose Free Milk |
18 |
1 |
$ 2.45 |
3 |
16 |
$ 41.65 |
$ 749.70 |
|
Totals |
41 |
50 |
59 |
| Joe's milkbar |
$ 33.15 |
$ 40.61 |
|
OM |
Organic Milk |
18 |
1 |
$ 2.33 |
2 |
22 |
$ 53.59 |
$ 964.62 |
|
|
|
|
|
| 7eleven |
$ 33.15 |
$ 39.12 |
|
|
|
|
|
|
|
|
$ 227.10 |
$ 3,537.75 |
|
|
|
|
|
| 7eleven |
$ 33.15 |
$ 39.12 |
|
Totals |
|
|
|
|
50 |
|
|
|
|
|
|
Projected 2018 Increase % |
18.6 |
| 7eleven |
$ 33.15 |
$ 39.12 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Woolworths Petrol |
$ 41.65 |
$ 44.57 |
|
Customers Specific % price premiums |
|
|
|
|
|
|
|
|
|
|
|
|
| Woolworths Petrol |
$ 41.65 |
$ 44.57 |
|
|
|
|
|
|
|
|
|
|
|
|
| 7eleven |
$ 41.65 |
$ 49.15 |
|
Woolworths Petrol |
|
7 |
|
|
|
|
|
|
|
|
|
| Woolworths Petrol |
$ 53.59 |
$ 57.34 |
|
Shell |
|
12.5 |
|
|
|
|
|
|
|
|
|
|
|
| 7eleven |
$ 53.59 |
$ 63.24 |
|
7eleven |
|
18 |
|
|
|
|
|
|
|
|
|
|
|
| Woolworths Petrol |
$ 14.04 |
$ 15.02 |
|
Joe's milkbar |
|
22.5 |
|
|
|
|
|
|
|
|
|
|
|
| Woolworths Petrol |
$ 14.04 |
$ 15.02 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Woolworths Petrol |
$ 14.04 |
$ 15.02 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Woolworths Petrol |
$ 14.04 |
$ 15.02 |
|
Milk production - Overall litres (past, present and projected future) |
|
|
|
|
|
|
|
|
| Shell |
$ 14.04 |
$ 15.80 |
|
Product Code |
Product Description |
Raw to processed ratio |
Litres Ordered (W32-2016) |
Litres Ordered (W32-2017) |
Projected Litres (W32-2018) |
|
|
|
|
|
|
|
|
| Shell |
$ 14.04 |
$ 15.80 |
|
|
|
|
|
|
|
|
|
| Shell |
$ 14.04 |
$ 15.80 |
|
WM |
Whole Milk |
0.95 |
182.81 |
351.0 |
416.3 |
|
|
|
|
|
|
|
|
| Joe's milkbar |
$ 14.04 |
$ 17.20 |
|
RF |
Reduce Fat Milk |
0.8 |
140.63 |
270.0 |
320.2 |
|
|
|
|
|
|
|
|
| 7eleven |
$ 14.04 |
$ 16.57 |
|
A2 |
Special Milk |
0.95 |
70.31 |
90.0 |
106.7 |
|
|
|
|
|
|
|
|
| 7eleven |
$ 14.04 |
$ 16.57 |
|
FM |
Flavoured Milk |
0.9 |
101.25 |
151.2 |
179.3 |
|
|
|
|
|
|
|
|
| Woolworths Petrol |
$ 21.00 |
$ 22.47 |
|
SM |
Skim milk |
0.7 |
37.50 |
96.0 |
113.9 |
|
|
|
|
|
|
|
|
| Woolworths Petrol |
$ 21.00 |
$ 22.47 |
|
UM |
UHT Milk |
0.5 |
37.50 |
48.0 |
56.9 |
|
|
|
|
|
|
|
|
| Shell |
$ 21.00 |
$ 23.63 |
|
LF |
Lactose Free Milk |
0.6 |
42.19 |
54.0 |
64.0 |
|
|
|
|
|
|
|
|
| 7eleven |
$ 21.00 |
$ 24.78 |
|
OM |
Organic Milk |
0.75 |
28.13 |
36.0 |
42.7 |
|
|
|
|
|
|
|
|
| Woolworths Petrol |
$ 25.48 |
$ 27.26 |
|
|
Totals |
|
640.31 |
1096.20 |
1300.09 |
|
|
|
|
|
|
|
|
| Shell |
$ 25.48 |
$ 28.67 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
