Alex Processing

Alex Processing produces a line of food products that are used as key ingredients in mass-produced
meals. The CEO, who recently hired you as his assistant, requested you to prepare an equipment
acquisition plan.

After consulting Sam English, the executive engineer, you are able to tabulate the equipment holding, the
last 10 years' annual aggregate demands, and the next 10 years' expected annual aggregate demands.

You can now begin work by estimating the equipment requirements for each equipment type. But before

● Past forecasts and acquisition plans were very crude, though they were adequate in the fast business
growth phase.
● Demand growth rate has slowed significantly, and from hereon such rough planning may lead to buying
equipment too early and even possible expensive over-purchase mistakes.
● Productivity will continue to improve, but its change rate will eventually approach saturation.

This exercise was written as a basis for class discussion. It illustrates neither the effective nor ineffective
management of the given situation. Data provided may be entirely hypothetical.

LeongTY 753780673.xlsx/Home
 Alex Processing Copyright © Leong Thin Yin, 2004. All Rights Reserved.

Input
Unit Cost ($) 100,000 50,000 250,000 70,000 120,000 90,000 110,000
Order Leadtime (yrs) 1 1 3 1 1 2 2

Output
Aggregate Grinder Grader Seive Sorting Bonding Packing Test
Year Demand Machine Machine Machine Equipment Machine Machine Equipment
(yyyy) (ton) (qty) (qty) (qty) (qty) (qty) (qty) (qty)
1997 3.91 4 5 2 9 1 2 5
1998 5.06 4 5 2 10 1 3 6
1999 6.05 4 4 3 11 1 4 7
2000 7.77 5 7 3 13 2 5 8
2001 10.03 6 8 4 15 2 5 9
2002 12.01 7 11 4 16 3 7 10
2003 14.61 7 12 5 19 3 7 11
2004 17.39 8 15 6 20 3 8 12
2005 20.82 9 16 6 23 5 9 14
2006 23.92 10 19 7 24 5 10 14
2007 27.74
2008 31.90
2009 36.04
2010 40.73
2011 45.22
2012 50.19
2013 55.36
2014 62.02
2015 68.03
2016 73.29

LeongTY 753780673.xlsx/Proto
 Alex Processing Copyright © Leong Thin Yin, 2004. All Rights Reserved.

Input
Unit Cost ($) 100,000 50,000 250,000 70,000 120,000 90,000 110,000
Order Leadtime (yrs) 1 1 3 1 1 2 2

Holding & Requirement

Aggregate Grinder Grader Sieve Sorting Bonding Packing Test
Year Demand Machine Machine Machine Equipment Machine Machine Equipment
(yyyy) (ton) (qty) (qty) (qty) (qty) (qty) (qty) (qty)
1997 3.91 4 5 2 9 1 2 5
1998 5.06 4 5 2 10 1 3 6
1999 6.05 4 4 3 11 1 4 7
2000 7.77 5 7 3 13 2 5 8
2001 10.03 6 8 4 15 2 5 9
2002 12.01 7 11 4 16 3 7 10
2003 14.61 7 12 5 19 3 7 11
2004 17.39 8 15 6 20 3 8 12
2005 20.82 9 16 6 23 5 9 14
2006 23.92 10 19 7 24 5 10 14
2007 27.74 10 21 7 25 6 11 15
2008 31.90 10 23 8 26 7 12 15
2009 36.04 11 25 8 27 7 13 16
2010 40.73 11 28 9 28 8 14 16
2011 45.22 12 30 9 29 9 14 17
2012 50.19 12 32 9 29 10 15 17
2013 55.36 12 34 10 30 11 16 18
2014 62.02 12 36 10 31 13 16 18
2015 68.03 13 38 10 31 14 17 18
2016 73.29 13 40 11 31 15 17 19

Performance Ratio
Aggregate Grinder Grader Sieve Sorting Bonding Packing Test
Year Demand Maching Machine Machine Equipment Machine Machine Equipment
(yyyy) (ton) (qty) (qty) (qty) (qty) (qty) (qty) (qty)
1997 3.91 0.978 0.782 1.955 0.434 3.910 1.955 0.782
1998 5.06 1.265 1.012 2.530 0.506 5.060 1.687 0.843
1999 6.05 1.512 1.512 2.016 0.550 6.049 1.512 0.864
2000 7.77 1.555 1.111 2.591 0.598 3.887 1.555 0.972
2001 10.03 1.671 1.254 2.507 0.669 5.014 2.006 1.114
2002 12.01 1.715 1.091 3.001 0.750 4.002 1.715 1.201
2003 14.61 2.086 1.217 2.921 0.769 4.868 2.086 1.328
2004 17.39 2.173 1.159 2.898 0.869 5.796 2.173 1.449
2005 20.82 2.313 1.301 3.469 0.905 4.163 2.313 1.487
2006 23.92 2.392 1.259 3.417 0.997 4.784 2.392 1.709
2007 27.74 2.780 1.340 3.790 1.120 4.800 2.520 1.880
2008 31.90 3.050 1.380 4.080 1.230 4.810 2.680 2.070
2009 36.04 3.320 1.430 4.360 1.340 4.820 2.840 2.260
2010 40.73 3.630 1.480 4.680 1.460 4.830 3.010 2.470
2011 45.22 3.920 1.520 4.980 1.580 4.850 3.180 2.670
2012 50.19 4.240 1.580 5.320 1.710 4.860 3.370 2.900
2013 55.36 4.580 1.630 5.670 1.850 4.880 3.560 3.130
2014 62.02 5.010 1.700 6.130 2.030 4.890 3.810 3.430
2015 68.03 5.410 1.770 6.540 2.190 4.910 4.040 3.700
2016 73.29 5.750 1.820 6.900 2.330 4.930 4.230 3.940
Performance ratios projection may be set by performance targeting.

LeongTY 753780673.xlsx/Model
 Trends
2007 2.781 1.337 3.793 1.118 4.797 2.524 1.880
2008 3.052 1.381 4.076 1.229 4.809 2.681 2.069
2009 3.322 1.426 4.358 1.339 4.821 2.836 2.256
2010 3.627 1.476 4.678 1.463 4.834 3.012 2.469
2011 3.920 1.524 4.984 1.582 4.847 3.180 2.672
2012 4.243 1.577 5.322 1.714 4.861 3.367 2.897
2013 4.580 1.632 5.675 1.851 4.876 3.561 3.131
2014 5.014 1.704 6.129 2.028 4.894 3.811 3.432
2015 5.405 1.768 6.538 2.187 4.911 4.036 3.704
2016 5.748 1.825 6.897 2.327 4.926 4.234 3.943
Performance ratios projection thus derived may not be acceptable.

LeongTY 753780673.xlsx/Model
 Documentation
Unit Cost of Equipment D4:J4 <Input>
Equipment Order Leadtime D5:J5 <Input>

Actual: Aggregate Demand C9:C18 <Input>

Equipment Holding D9:J18 <Input>
Projected: Aggregate Demand C19:C28 <Input>

Grinder Machine | Projected: Requirement D19 =$C19/D42

Actual: Performance ratio D32 =$C9/D9
Projected: Performance ratio D42:J51 <Input>, values copied from D55:J64 and then adjusted.

Projected (time-based): Performance ratio D55 =TREND(D$32:D$41,$B$32:$B$41,$B42)

Projected (vol-based): Performance ratio D55 =TREND(D$32:D$41,$C$32:$C$41,$C42)

Notes
Performance ratio projection may be assumed to be function of time or volume.
FORECAST may be used in place of TREND.
The values from TREND are then adjusted in a negotiated process and recorded as planned performance targets.

80 RefLine 16
Aggre-
75 This Year 15 gate
70 2007 0 14
Demand
2007 1E+10 Column
65 13 O
60 12 Grinder
Maching
Aggregate Demand (ton)

55 11
Productivity (ton/qty)

(qty)
50 10 Grader
Machine
45 9 (qty)
40 8 Sieve
Machine
35 7 (qty)

30 6 Bonding
Machine
25 5 (qty)

20 4 Packing
Machine
15 3 (qty)

10 2 Test
Equip
5 1 ment
(qty)
0 0
1996

1997

1998

1999

2000

2001

2002

2003

2004

2005

2006

2007

2008

2009

2010

2011

2012

2013

2014

2015

2016

2017

LeongTY 753780673.xlsx/Model