http://www.production-scheduling.

com

EXCEL-BASED MRP
TABLE OF CONTENTS
INTRODUCTION
TABLE OF LINKS
THE BUSINESS
DATA FILES:
Item Master
Supplier Master
Bill of Materials (BOM)
Purchase Orders
FINITE SCHEDULE
Make-to-Inventory Logic Explained
MRP CALCULATIONS:
Production Runs
BOM x 3
Allocate Inventory
Purchase Action Report
INVENTORY RATIONING:
Inventory Rationing Logic
Inventory Rationing Calculations
Components Pivot
Products Pivot

http://www.production-scheduling.com

http://www.production-scheduling.com

Thank you for downloading this copy of Excel-Based MRP, I hope it will be of use to you.
Why Excel-Based MRP?
Production-Scheduling.com assists manufacturers to develop their own finite scheduling systems in Excel, and
will also custom develop scheduling systems for them. Most of these systems are interfaced to a host MRP/ERP
system, which acts as a repository for demand and inventory data, and will perform Material Requirements
Planning (MRP1) calculations.
However, many clients have approached us to develop MRP within Excel, because either:
- they do not have an MRP system, or
- having generated scheduled dates within Excel, they are unable to upload them to their MRP system, or
- their MRP systems are just too cumbersome and unresponsive
Contact Us
This is a simple example of an MRP system, with a small amount of sample data, but it will also cater for many
thousands of records.
If you need assistance to adapt the system to your own manufacturing process, please do not hesitate to contact
me, Tony Rice at:
[email protected]
Or visit our web site:
www.Production-Scheduling.com
Features
The Excel-Based MRP system has the following features:
- VBA free, no macros, it is all formulas and PivotTables, and nothing is hidden
- demand is generated by a make-to-inventory Finite Schedule, but may also be from another source
- a single level Bill of Material structure
- inventory of raw material and components is allocated to the earliest scheduled product first, and will be
dynamically re-allocated as the schedule changes
- a Purchase Action Report identifies purchase orders which must be placed or chased to meet the schedule
Inventory Rationing
The last 3 sheets of the system addresses a question that many manufacturers have:
"What products should I make with the inventory I have on hand right now?"
The system takes into account raw materials that are used by more than one product, and rations the inventory
across the products so as to even out the product inventory cover as much as possible.

http://www.production-scheduling.com

Table of Links

Formula link:
PivotTable link:

Products Pivot

Inventory Rationing Calculations

Allocate Inventory

BOM x 3

Production Runs

Finite Schedule

Purchase Orders

Bill of Material

Components Pivot

The Business
Item Master
Supplier Master
Bill of Material
Purchase Orders
Finite Schedule
Production Runs
BOM x 3
Allocate Inventory
Purchase Action Report
Inventory Rationing Calculations
Components Pivot
Products Pivot

Supplier Master

Worksheet

Item Master

The Business

Links to :

Purchase Action Report

This shows the worksheets that have links to other woksheets, either by having formulae
that refer to other worksheets, or by having a PivotTable based on a data base in another
worksheet.

F
F
F
F

F
P
F

F
F

P
P

F
P
P

F
P

Chained PivotTables
Data flows from the Finite Schedule to the Purchase Action Report through a chain of links that includes
3 PivotTables:
Finite
Schedule

Production
Runs

P
BOM x 3
Purchase
Orders

Allocate
Inventory

Purchase
Action
Report

Unlike formulas, PivotTables do not change automatically when the data is changed, they need to be refreshed.
You can refresh each PivotTable in turn by right clicking in it, and clicking on Refresh Data.
Alternatively you can click on the Refresh All icon
which will refresh all the PivotTables in the workbook.
You will need to click it 3 times.
Put the Refresh All icon on you toolbar by right clicking on the toolbar, then Cutomise|Commands|Data
find the icon and drag it up onto your toolbar

http://www.production-scheduling.com

to be refreshed.

n the workbook.

http://www.production-scheduling.com

Outline of the Business

This example is based in a fictional business that makes and sells shampoo.
Product Range
We are a new business, and the product range is small. We have 2 types of shampoo, Apple
Blossom and Fragrant Lemon, and each comes in 3 sizes 100ml, 250ml and 500ml. Our
product range is:
Code
AB100
AB250
AB500
FL100
FL250
FL500

Description
Apple Blossom 100ml
Apple Blossom 250ml
Apple Blossom 500ml
Fragrant Lemon 100ml
Fragrant Lemon 250ml
Fragrant Lemon 500ml

Bottles per Carton

48
48
24
48
48
24

Forecast Cartons per Week

1000
1400
800
200
1200
1600

The Process
We buy a base shampoo in bulk, pump it into a blending vessel, add either apple or lemon
essence to it, stir it, pump it into a holding vessel from where it is fed to the filling line where
the bottles are filled, labelled, shrink wrapped 6 together, packed into corrugated cartons,
and stacked on pallets.
Bill of Materials
There is one base shampoo, two fragrances, three bottle sizes and six labels. The 100ml
and 250ml bottles are fitted with a flip top cap, and the 500ml bottle has a screw cap. The
100ml bottles are packed 48 to a small carton, the 250ml is packed 48 in a large carton and
the 500ml uses the same carton but 24 to a carton. Two of the bottle labels are pasted on
the carton, one on each end.
Glue, shrink wrap and adhesive tape for the cartons are considered to be consumables, and
the pallets are hired from a national pallet pool. The minimum that a customer can buy is
one carton, so that is the SKU (stock keeping unit).
Capacities
Blending is quicker than filling, 100ml and 250ml bottles can be filled at 70 per minute, and
500ml bottles at 40 per minute. To change the line from one bottle size to another takes
time, but we shall ignore change over times in this example.

http://www.production-scheduling.com

Item Master
The item master file contains details of both finished products and raw materials.
This data would typically be stored in a host system, and transferred to Excel via an ODBC link and MS Query.

Code
AB100
AB250
AB500
FL100
FL250
FL500
BS01
ESA
ESL
BT100
BT250
BT500
CAPF
CAPS
LAB10
LAB25
LAB50
LFL10
LFL25
LFL50
CARS
CARL

Description
Apple Blossom 100ml
Apple Blossom 250ml
Apple Blossom 500ml
Fragrant Lemon 100ml
Fragrant Lemon 250ml
Fragrant Lemon 500ml
Base Shampoo
Apple essence
Lemon essence
Bottle 100ml
Bottle 250ml
Bottle 500ml
Flip cap
Screw cap
Label Apple Blossom 100ml
Label Apple Blossom 250ml
Label Apple Blossom 500ml
Label Fragrant Lemon 100ml
Label Fragrant Lemon 250ml
Label Fragrant Lemon 500ml
Carton small
Carton large

Unit of
Measure
carton
carton
carton
carton
carton
carton
litre
litre
litre
each
each
each
each
each
each
each
each
each
each
each
each
each

Warehouse
FP
FP
FP
FP
FP
FP
RM
RM
RM
RM
RM
RM
RM
RM
RM
RM
RM
RM
RM
RM
RM
RM

Supplier
01
01
01
01
01
01
AC01
CE01
CE01
BP01
BP01
BP01
BP01
BP01
DP01
DP01
DP01
DP01
DP01
DP01
EC01
EC01

Supplier Name
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
Ace Chemicals
Chang Essences
Chang Essences
Best Plastics
Best Plastics
Best Plastics
Best Plastics
Best Plastics
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Eduardo Corrugates
Eduardo Corrugates

http://www.production-scheduling.com

link and MS Query.

Lead
Time
Days

14
30
30
21
21
21
21
21
7
7
7
7
7
7
21
21

Inventory
1,854
1,470
470
488
1,148
766
34,276
1,494
1,002
122,472
184,968
36,516
65,738
37,818
211,796
266,776
24,964
32,140
83,496
117,354
998
4,368

http://www.production-scheduling.com

Supplier Master

Code
AC01
BP01
CE01
DP01
EC01

Supplier Name
Ace Chemicals
Best Plastics
Chang Essences
Dave's Printing
Eduardo Corrugates

http://www.production-scheduling.com

Bill of Material
This is a typical structure of a single level bill of materials, describing the relationship between
products and components
Product
AB100
AB100
AB100
AB100
AB100
AB100
AB250
AB250
AB250
AB250
AB250
AB250
AB500
AB500
AB500
AB500
AB500
AB500
FL100
FL100
FL100
FL100
FL100
FL100
FL250
FL250
FL250
FL250
FL250
FL250
FL500
FL500
FL500
FL500
FL500
FL500

Component
BS01
BT100
CAPF
CARS
ESA
LAB10
BS01
BT250
CAPF
CARL
ESA
LAB25
BS01
BT500
CAPS
CARL
ESA
LAB50
BS01
BT100
CAPF
CARS
ESL
LFL10
BS01
BT250
CAPF
CARL
ESL
LFL25
BS01
BT500
CAPS
CARL
ESL
LFL50

Qty
4.800
48.000
48.000
1.000
0.048
50.000
12.000
48.000
48.000
1.000
0.120
50.000
12.000
24.000
24.000
1.000
0.120
26.000
4.800
48.000
48.000
1.000
0.048
50.000
12.000
48.000
48.000
1.000
0.120
50.000
12.000
24.000
24.000
1.000
0.120
26.000

Component Description
Base Shampoo
Bottle 100ml
Flip cap
Carton small
Apple essence
Label Apple Blossom 100ml
Base Shampoo
Bottle 250ml
Flip cap
Carton large
Apple essence
Label Apple Blossom 250ml
Base Shampoo
Bottle 500ml
Screw cap
Carton large
Apple essence
Label Apple Blossom 500ml
Base Shampoo
Bottle 100ml
Flip cap
Carton small
Lemon essence
Label Fragrant Lemon 100ml
Base Shampoo
Bottle 250ml
Flip cap
Carton large
Lemon essence
Label Fragrant Lemon 250ml
Base Shampoo
Bottle 500ml
Screw cap
Carton large
Lemon essence
Label Fragrant Lemon 500ml

http://www.production-scheduling.com

Purchase Orders
This table lists the components that are on order from the suppliers, and the date they are due to
be delivered.
Code
BS01
BS01
BT100
BT250
BT250
BT250
BT500
BT500
BT500
CAPF
CAPF
CAPS
CAPS
CARL
CARL
CARS
CARS
ESA
ESA
ESL
LAB10
LAB25
LAB25
LAB50
LAB50
LAB50
LFL10
LFL10
LFL25
LFL25
LFL50
LFL50

Description
Base Shampoo
Base Shampoo
Bottle 100ml
Bottle 250ml
Bottle 250ml
Bottle 250ml
Bottle 500ml
Bottle 500ml
Bottle 500ml
Flip cap
Flip cap
Screw cap
Screw cap
Carton large
Carton large
Carton small
Carton small
Apple essence
Apple essence
Lemon essence
Label Apple Blossom 100ml
Label Apple Blossom 250ml
Label Apple Blossom 250ml
Label Apple Blossom 500ml
Label Apple Blossom 500ml
Label Apple Blossom 500ml
Label Fragrant Lemon 100ml
Label Fragrant Lemon 100ml
Label Fragrant Lemon 250ml
Label Fragrant Lemon 250ml
Label Fragrant Lemon 500ml
Label Fragrant Lemon 500ml

Supplier
AC01
AC01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
EC01
EC01
EC01
EC01
CE01
CE01
CE01
DP01
DP01
DP01
DP01
DP01
DP01
DP01
DP01
DP01
DP01
DP01
DP01

Supplier Name
Ace Chemicals
Ace Chemicals
Best Plastics
Best Plastics
Best Plastics
Best Plastics
Best Plastics
Best Plastics
Best Plastics
Best Plastics
Best Plastics
Best Plastics
Best Plastics
Eduardo Corrugates
Eduardo Corrugates
Eduardo Corrugates
Eduardo Corrugates
Chang Essences
Chang Essences
Chang Essences
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing

Qty
100,000
100,000
40,000
90,000
40,000
50,000
100,000
50,000
50,000
200,000
300,000
100,000
100,000
8,000
5,000
1,000
1,000
60
100
350
50,000
70,000
60,000
20,000
20,000
20,000
10,000
10,000
60,000
60,000
40,000
40,000

Due in
18-Mar-00
30-Mar-00
14-Apr-00
18-Mar-00
1-Apr-00
22-Apr-00
16-Mar-00
1-Apr-00
15-Apr-00
16-Mar-00
7-Apr-00
17-Mar-00
2-Apr-00
14-Mar-00
4-Apr-00
18-Mar-00
26-Apr-00
23-Mar-00
6-Apr-00
3-Apr-00
7-Apr-00
14-Mar-00
21-Mar-00
2-Apr-00
16-Apr-00
30-Apr-00
14-Apr-00
28-Apr-00
17-Mar-00
31-Mar-00
30-Mar-00
13-Apr-00

http://www.production-scheduling.com
Make-to-Stock (Inventory) Schedule
This schedule is contained in the MRP system, purely to generate a demand for materials.
Explanations of the fomulae, and how the system is constructed, is contained in Section 42 of the scheduling tutorial, downloadable from
www.production-scheduling.com
Start of the first job:
Product running now:
Quantity committed:
Utilization:

Produc
t
Force
AB500
FL500
Idle
AB500
Idle
FL250
AB250
Idle
AB100
Idle
FL500
Idle
AB500
Idle
FL100
Idle
FL250
Idle
AB250
Idle
AB100
Idle
FL500
Idle

35.48%

Qty
110
2,580
0
1,234
0
1,960
2,383
0
1,609
0
2,538
0
1,234
0
304
0
1,960
0
2,321
0
1,609
0
2,538
0

14/3 8:00
AB500
110

AB100

Speed - cartons per hour

Forecast sales - cartons per week
Min cover - weeks
Max cover - weeks
Min cover - cartons
Max cover - cartons

Idle
Run
Force hours Force hours
Opening stock:
0.0
0.6
0.0
14.7
22.4
0.0
0.0
7.1
31.9
0.0
0.0
22.4
0.0
27.2
101.1
0.0
0.0
18.4
21.5
0.0
0.0
14.5
15.1
0.0
0.0
7.1
21.9
0.0
0.0
3.5
21.7
0.0
0.0
22.4
4.8
0.0
0.0
26.5
92.9
0.0
0.0
18.4
17.6
0.0
0.0
14.5
7.8
0.0

Start
14/3 8:00
14/3 8:37
14/3 23:22
15/3 21:48
16/3 4:51
17/3 12:43
18/3 11:07
19/3 14:21
23/3 19:28
24/3 13:51
25/3 11:19
26/3 1:49
26/3 16:56
26/3 23:59
27/3 21:55
28/3 1:23
28/3 23:07
29/3 21:31
30/3 2:21
31/3 4:53
4/4 1:47
4/4 20:10
5/4 13:49
6/4 4:19

Stop
14/3 8:00
14/3 8:37
14/3 23:22
15/3 21:48
16/3 4:51
17/3 12:43
18/3 11:07
19/3 14:21
23/3 19:28
24/3 13:51
25/3 11:19
26/3 1:49
26/3 16:56
26/3 23:59
27/3 21:55
28/3 1:23
28/3 23:07
29/3 21:31
30/3 2:21
31/3 4:53
4/4 1:47
4/4 20:10
5/4 13:49
6/4 4:19
6/4 12:04

AB250

AB500

175
800
0.5
2.0
400
1,600

FL100

88
200
0.5
2.0
100
400

FL250

88
1,000
0.5
2.0
500
2,000

88
1,400
0.5
2.0
700
2,800

88
1,200
0.5
2.0
600
2,400

1,854
1,850
1,763
1,629
1,587
1,397
1,264
1,102
500
2,000
1,872
1,785
1,696
1,654
1,523
1,502
1,373
1,240
1,211
1,053
500
2,000
1,894
1,808
1,762

Projected Inventory - units

1,470
470
488
1,148
1,465
577
487
1,144
1,342
507
470
1,038
1,155
400
443
878
1,096
1,600
435
828
831
1,449
397
600
644
1,342
370
2,400
2,800
1,212
338
2,205
1,957
731
217
1,483
1,804
643
195
1,352
1,625
541
170
1,198
1,504
472
152
1,095
1,379
400
135
987
1,320
1,600
126
937
1,137
1,496
100
780
1,108
1,479
400
755
927
1,376
374
600
740
1,269
347
2,400
700
1,246
342
2,365
2,800
1,120
310
2,176
2,026
678
199
1,512
1,873
590
178
1,381
1,725
506
156
1,255
1,605
437
139
1,151
1,540
400
130
1,096

FL500

AB100

AB250

AB500

FL100

FL250

FL500

175
1,600
0.5
2.0
800
3,200

766
760
3,200
2,986
2,919
2,615
2,402
2,143
1,180
1,004
800
3,200
3,056
2,989
2,780
2,747
2,540
2,327
2,281
2,028
1,143
968
800
3,200
3,126

1.85
1.85
1.76
1.63
1.59
1.40
1.26
1.10
0.50
2.00
1.87
1.79
1.70
1.65
1.52
1.50
1.37
1.24
1.21
1.05
0.50
2.00
1.89
1.81
1.76

Projected Cover - weeks

1.05
0.59
2.44
0.96
1.05
0.72
2.44
0.95
0.96
0.63
2.35
0.87
0.83
0.50
2.22
0.73
0.78
2.00
2.17
0.69
0.59
1.81
1.98
0.50
0.46
1.68
1.85
2.00
2.00
1.52
1.69
1.84
1.40
0.91
1.09
1.24
1.29
0.80
0.98
1.13
1.16
0.68
0.85
1.00
1.07
0.59
0.76
0.91
0.98
0.50
0.67
0.82
0.94
2.00
0.63
0.78
0.81
1.87
0.50
0.65
0.79
1.85
2.00
0.63
0.66
1.72
1.87
0.50
0.53
1.59
1.74
2.00
0.50
1.56
1.71
1.97
2.00
1.40
1.55
1.81
1.45
0.85
1.00
1.26
1.34
0.74
0.89
1.15
1.23
0.63
0.78
1.05
1.15
0.55
0.70
0.96
1.10
0.50
0.65
0.91

0.48
0.48
2.00
1.87
1.82
1.63
1.50
1.34
0.74
0.63
0.50
2.00
1.91
1.87
1.74
1.72
1.59
1.45
1.43
1.27
0.71
0.61
0.50
2.00
1.95

Lowest Offs
Cover
et

Next
Prod

0.48
0.63
0.50
0.69
0.50
0.46
1.10
0.50
0.63
0.50
0.59
0.50
0.63
0.50
0.63
0.50
0.53
0.50
1.05
0.50
0.61
0.50
0.55
0.50

FL500
AB500
AB500
FL250
FL250
AB250
AB100
AB100
FL500
FL500
AB500
AB500
FL100
FL100
FL250
FL250
AB250
AB250
AB100
AB100
FL500
FL500
AB500
AB500

3.00

Projected Inventory
Cover

Weeks

2.50
2.00

AB100
AB250
AB500
FL100
FL250
FL500

1.50
1.00
0.50
0.00
10/3 0:00

15/3 0:00

20/3 0:00

25/3 0:00

30/3 0:00

4/4 0:00

9/4 0:00

6
3
3
5
5
2
1
1
6
6
3
3
4
4
5
5
2
2
1
1
6
6
3
3

Invento
ry
Speed
760
507
400
828
600
644
1102
500
1004
800
472
400
126
100
755
600
740
700
1053
500
968
800
437
400

175
175
175
87.5
87.5
87.5
87.5
87.5
175
175
175
175
87.5
87.5
87.5
87.5
87.5
87.5
87.5
87.5
175
175
175
175

Forcast
1600
800
800
1200
1200
1400
1000
1000
1600
1600
800
800
200
200
1200
1200
1400
1400
1000
1000
1600
1600
800
800

Min
Cover
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5

Min
cartons
800
400
400
600
600
700
500
500
800
800
400
400
100
100
600
600
700
700
500
500
800
800
400
400

Max
cartons
3200
1600
1600
2400
2400
2800
2000
2000
3200
3200
1600
1600
400
400
2400
2400
2800
2800
2000
2000
3200
3200
1600
1600

To
make
2580
1124
1234
1712
1960
2383
964
1609
2322
2538
1160
1234
278
304
1791
1960
2276
2321
1016
1609
2360
2538
1196
1234

http://www.production-scheduling.com

Make-to-Inventory Logic Explained

The logic works as follows:
- the first job is the "Product running now"
- it starts at the "Start of the first job"
- and the "Quantity committed" is specified
the
duration of the job is calculated from the speed, and shown in the "Run hours" column
a
separate
column for each product records the projected inventory level at the end of each job
- at the end of each job, the inventory level of one product has risen, and the other four products have fallen
- the opening inventory is recorded at the top of the columns, and would typically come from an inventory
management system
- the inventory at the end of the job is the opening inventory, less the forecast sales for the duration of the job,
plus the production quantity of the job
- another bank of six columns expresses the projected inventory level as days of cover
- the product with the lowest cover at the end of the job is identified as the next product to be made
- the quantity to make is that required to increase the inventory level to the maximum, plus sufficient to
replenish the amount sold while the job was running
- the duration of the next job is calculated, and the cycle begins again
- if the projected inventory level of the lowest cover product is above its minimum, then an idle period is
scheduled
- the duration of the idle period is the time it will take for forecast sales to reduce the inventory to the
minimum and trigger the next run
You will see from the chart of inventory cover, that the model attempts to keep the inventory of each product
between the minimum and maximum.

http://www.production-scheduling.com

This is a PivotTable of the Finite Schedule on the previous sheet.

For each product, it lists the start time, and numbers each production run.

Prod/Run
AB100/1
AB100/2
AB250/1
AB250/2
AB500/1
AB500/2
AB500/3
FL100/1
FL250/1
FL250/2
FL500/1
FL500/2
FL500/3

Prod
AB100
AB100
AB250
AB250
AB500
AB500
AB500
FL100
FL250
FL250
FL500
FL500
FL500

Sum of Qty
Product
Start
Total
AB100
### 1609
4/4/2000 1:47 1609
AB250
### 2383
3/30/2000 2:21 2321
AB500
3/14/2000 8:00
110
### 1234
### 1234
FL100
###
304
FL250
### 1960
### 1960
FL500
3/14/2000 8:37 2580
### 2538
4/5/2000 13:49 2538
Grand Total
22380

Run
1
2
1
2
1
2
3
1
1
2
1
2
3

http://www.production-scheduling.com

This sheet allows for 3 production runs of each product. The Bill of Materials is repeated 3 times,
and the quantities of each component required for each production run, is calculated.

Product/
Run
AB100/1
AB100/1
AB100/1
AB100/1
AB100/1
AB100/1
AB250/1
AB250/1
AB250/1
AB250/1
AB250/1
AB250/1
AB500/1
AB500/1
AB500/1
AB500/1
AB500/1
AB500/1
FL100/1
FL100/1
FL100/1
FL100/1
FL100/1
FL100/1
FL250/1
FL250/1
FL250/1
FL250/1
FL250/1
FL250/1
FL500/1
FL500/1
FL500/1
FL500/1
FL500/1
FL500/1
/1
/1
/1
/1
/1
/1
/1
/1
/1
/1
AB100/2
AB100/2

Component
BS01
BT100
CAPF
CARS
ESA
LAB10
BS01
BT250
CAPF
CARL
ESA
LAB25
BS01
BT500
CAPS
CARL
ESA
LAB50
BS01
BT100
CAPF
CARS
ESL
LFL10
BS01
BT250
CAPF
CARL
ESL
LFL25
BS01
BT500
CAPS
CARL
ESL
LFL50
0
0
0
0
0
0
0
0
0
0
BS01
BT100

Qty Production
Production
each Run Index Start Date
Qty
4.8
6 23-Mar-00
1609
48
6 23-Mar-00
1609
48
6 23-Mar-00
1609
1
6 23-Mar-00
1609
0.048
6 23-Mar-00
1609
50
6 23-Mar-00
1609
12
8 18-Mar-00
2383
48
8 18-Mar-00
2383
48
8 18-Mar-00
2383
1
8 18-Mar-00
2383
0.12
8 18-Mar-00
2383
50
8 18-Mar-00
2383
12
10 14-Mar-00
110
24
10 14-Mar-00
110
24
10 14-Mar-00
110
1
10 14-Mar-00
110
0.12
10 14-Mar-00
110
26
10 14-Mar-00
110
4.8
13 27-Mar-00
304
48
13 27-Mar-00
304
48
13 27-Mar-00
304
1
13 27-Mar-00
304
0.048
13 27-Mar-00
304
50
13 27-Mar-00
304
12
14 17-Mar-00
1960
48
14 17-Mar-00
1960
48
14 17-Mar-00
1960
1
14 17-Mar-00
1960
0.12
14 17-Mar-00
1960
50
14 17-Mar-00
1960
12
16 14-Mar-00
2580
24
16 14-Mar-00
2580
24
16 14-Mar-00
2580
1
16 14-Mar-00
2580
0.12
16 14-Mar-00
2580
26
16 14-Mar-00
2580
0
#N/A 30-Dec-99
0
0
#N/A 30-Dec-99
0
0
#N/A 30-Dec-99
0
0
#N/A 30-Dec-99
0
0
#N/A 30-Dec-99
0
0
#N/A 30-Dec-99
0
0
#N/A 30-Dec-99
0
0
#N/A 30-Dec-99
0
0
#N/A 30-Dec-99
0
0
#N/A 30-Dec-99
0
4.8
7 4-Apr-00
1609
48
7 4-Apr-00
1609

Component
Quantity
Required
7723.2
77232
77232
1609
77.232
80450
28596
114384
114384
2383
285.96
119150
1320
2640
2640
110
13.2
2860
1459.2
14592
14592
304
14.592
15200
23520
94080
94080
1960
235.2
98000
30960
61920
61920
2580
309.6
67080
0
0
0
0
0
0
0
0
0
0
7723.2
77232

http://www.production-scheduling.com

AB100/2
AB100/2
AB100/2
AB100/2
AB250/2
AB250/2
AB250/2
AB250/2
AB250/2
AB250/2
AB500/2
AB500/2
AB500/2
AB500/2
AB500/2
AB500/2
FL100/2
FL100/2
FL100/2
FL100/2
FL100/2
FL100/2
FL250/2
FL250/2
FL250/2
FL250/2
FL250/2
FL250/2
FL500/2
FL500/2
FL500/2
FL500/2
FL500/2
FL500/2
/2
/2
/2
/2
/2
/2
/2
/2
/2
/2
AB100/3
AB100/3
AB100/3
AB100/3
AB100/3
AB100/3
AB250/3
AB250/3
AB250/3
AB250/3

CAPF
CARS
ESA
LAB10
BS01
BT250
CAPF
CARL
ESA
LAB25
BS01
BT500
CAPS
CARL
ESA
LAB50
BS01
BT100
CAPF
CARS
ESL
LFL10
BS01
BT250
CAPF
CARL
ESL
LFL25
BS01
BT500
CAPS
CARL
ESL
LFL50
0
0
0
0
0
0
0
0
0
0
BS01
BT100
CAPF
CARS
ESA
LAB10
BS01
BT250
CAPF
CARL

48
1
0.048
50
12
48
48
1
0.12
50
12
24
24
1
0.12
26
4.8
48
48
1
0.048
50
12
48
48
1
0.12
50
12
24
24
1
0.12
26
0
0
0
0
0
0
0
0
0
0
4.8
48
48
1
0.048
50
12
48
48
1

7
7
7
7
9
9
9
9
9
9
11
11
11
11
11
11
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
15
15
15
15
15
15
17
17
17
17
17
17
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A

4-Apr-00
4-Apr-00
4-Apr-00
4-Apr-00
30-Mar-00
30-Mar-00
30-Mar-00
30-Mar-00
30-Mar-00
30-Mar-00
15-Mar-00
15-Mar-00
15-Mar-00
15-Mar-00
15-Mar-00
15-Mar-00
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
28-Mar-00
28-Mar-00
28-Mar-00
28-Mar-00
28-Mar-00
28-Mar-00
25-Mar-00
25-Mar-00
25-Mar-00
25-Mar-00
25-Mar-00
25-Mar-00
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99

1609
1609
1609
1609
2321
2321
2321
2321
2321
2321
1234
1234
1234
1234
1234
1234
0
0
0
0
0
0
1960
1960
1960
1960
1960
1960
2538
2538
2538
2538
2538
2538
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0

77232
1609
77.232
80450
27852
111408
111408
2321
278.52
116050
14808
29616
29616
1234
148.08
32084
0
0
0
0
0
0
23520
94080
94080
1960
235.2
98000
30456
60912
60912
2538
304.56
65988
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0

http://www.production-scheduling.com

AB250/3
AB250/3
AB500/3
AB500/3
AB500/3
AB500/3
AB500/3
AB500/3
FL100/3
FL100/3
FL100/3
FL100/3
FL100/3
FL100/3
FL250/3
FL250/3
FL250/3
FL250/3
FL250/3
FL250/3
FL500/3
FL500/3
FL500/3
FL500/3
FL500/3
FL500/3
/3
/3
/3
/3
/3
/3
/3
/3
/3
/3

ESA
LAB25
BS01
BT500
CAPS
CARL
ESA
LAB50
BS01
BT100
CAPF
CARS
ESL
LFL10
BS01
BT250
CAPF
CARL
ESL
LFL25
BS01
BT500
CAPS
CARL
ESL
LFL50
0
0
0
0
0
0
0
0
0
0

0.12
50
12
24
24
1
0.12
26
4.8
48
48
1
0.048
50
12
48
48
1
0.12
50
12
24
24
1
0.12
26
0
0
0
0
0
0
0
0
0
0

#N/A
#N/A
12
12
12
12
12
12
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
18
18
18
18
18
18
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A

30-Dec-99
30-Dec-99
26-Mar-00
26-Mar-00
26-Mar-00
26-Mar-00
26-Mar-00
26-Mar-00
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
5-Apr-00
5-Apr-00
5-Apr-00
5-Apr-00
5-Apr-00
5-Apr-00
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99
30-Dec-99

0
0
1234
1234
1234
1234
1234
1234
0
0
0
0
0
0
0
0
0
0
0
0
2538
2538
2538
2538
2538
2538
0
0
0
0
0
0
0
0
0
0

0
0
14808
29616
29616
1234
148.08
32084
0
0
0
0
0
0
0
0
0
0
0
0
30456
60912
60912
2538
304.56
65988
0
0
0
0
0
0
0
0
0
0

http://www.production-scheduling.com

Columns A to D are a PivotTable of the database on BOM x 3, and the rest are formulae.
At row 150 there are links to the Purchase Orders worksheet.
Here component inventory is allocated to the earliest production runs first.
2
Sum of Component Quantity Required
ComponentStart Date Product/Run Total
BS01
14-Mar-00 AB500/1
1320
FL500/1
30960
15-Mar-00 AB500/2
14808
17-Mar-00 FL250/1
23520
18-Mar-00 AB250/1
28596
23-Mar-00 AB100/1
7723.2
25-Mar-00 FL500/2
30456
26-Mar-00 AB500/3
14808
27-Mar-00 FL100/1
1459.2
28-Mar-00 FL250/2
23520
30-Mar-00 AB250/2
27852
4-Apr-00 AB100/2
7723.2
5-Apr-00 FL500/3
30456
BT100
23-Mar-00 AB100/1
77232
27-Mar-00 FL100/1
14592
4-Apr-00 AB100/2
77232
BT250
17-Mar-00 FL250/1
94080
18-Mar-00 AB250/1
114384
28-Mar-00 FL250/2
94080
30-Mar-00 AB250/2
111408
BT500
14-Mar-00 AB500/1
2640
FL500/1
61920
15-Mar-00 AB500/2
29616
25-Mar-00 FL500/2
60912
26-Mar-00 AB500/3
29616
5-Apr-00 FL500/3
60912
CAPF
17-Mar-00 FL250/1
94080
18-Mar-00 AB250/1
114384
23-Mar-00 AB100/1
77232
27-Mar-00 FL100/1
14592
28-Mar-00 FL250/2
94080
30-Mar-00 AB250/2
111408
4-Apr-00 AB100/2
77232
CAPS
14-Mar-00 AB500/1
2640
FL500/1
61920
15-Mar-00 AB500/2
29616
25-Mar-00 FL500/2
60912
26-Mar-00 AB500/3
29616
5-Apr-00 FL500/3
60912
CARL
14-Mar-00 AB500/1
110
FL500/1
2580
15-Mar-00 AB500/2
1234
17-Mar-00 FL250/1
1960
18-Mar-00 AB250/1
2383
25-Mar-00 FL500/2
2538
26-Mar-00 AB500/3
1234
28-Mar-00 FL250/2
1960

Item
ComponenStart Date Index
Description
BS01
14-Mar-00
13 Base Shampoo
BS01
14-Mar-00
13 Base Shampoo
BS01
15-Mar-00
13 Base Shampoo
BS01
17-Mar-00
13 Base Shampoo
BS01
18-Mar-00
13 Base Shampoo
BS01
23-Mar-00
13 Base Shampoo
BS01
25-Mar-00
13 Base Shampoo
BS01
26-Mar-00
13 Base Shampoo
BS01
27-Mar-00
13 Base Shampoo
BS01
28-Mar-00
13 Base Shampoo
BS01
30-Mar-00
13 Base Shampoo
BS01
4-Apr-00
13 Base Shampoo
BS01
5-Apr-00
13 Base Shampoo
BT100
23-Mar-00
16 Bottle 100ml
BT100
27-Mar-00
16 Bottle 100ml
BT100
4-Apr-00
16 Bottle 100ml
BT250
17-Mar-00
17 Bottle 250ml
BT250
18-Mar-00
17 Bottle 250ml
BT250
28-Mar-00
17 Bottle 250ml
BT250
30-Mar-00
17 Bottle 250ml
BT500
14-Mar-00
18 Bottle 500ml
BT500
14-Mar-00
18 Bottle 500ml
BT500
15-Mar-00
18 Bottle 500ml
BT500
25-Mar-00
18 Bottle 500ml
BT500
26-Mar-00
18 Bottle 500ml
BT500
5-Apr-00
18 Bottle 500ml
CAPF
17-Mar-00
19 Flip cap
CAPF
18-Mar-00
19 Flip cap
CAPF
23-Mar-00
19 Flip cap
CAPF
27-Mar-00
19 Flip cap
CAPF
28-Mar-00
19 Flip cap
CAPF
30-Mar-00
19 Flip cap
CAPF
4-Apr-00
19 Flip cap
CAPS
14-Mar-00
20 Screw cap
CAPS
14-Mar-00
20 Screw cap
CAPS
15-Mar-00
20 Screw cap
CAPS
25-Mar-00
20 Screw cap
CAPS
26-Mar-00
20 Screw cap
CAPS
5-Apr-00
20 Screw cap
CARL
14-Mar-00
28 Carton large
CARL
14-Mar-00
28 Carton large
CARL
15-Mar-00
28 Carton large
CARL
17-Mar-00
28 Carton large
CARL
18-Mar-00
28 Carton large
CARL
25-Mar-00
28 Carton large
CARL
26-Mar-00
28 Carton large
CARL
28-Mar-00
28 Carton large

http://www.production-scheduling.com

CARS

ESA

ESL

LAB10
LAB25
LAB50

LFL10
LFL25
LFL50

30-Mar-00
5-Apr-00
23-Mar-00
27-Mar-00
4-Apr-00
14-Mar-00
15-Mar-00
18-Mar-00
23-Mar-00
26-Mar-00
30-Mar-00
4-Apr-00
14-Mar-00
17-Mar-00
25-Mar-00
27-Mar-00
28-Mar-00
5-Apr-00
23-Mar-00
4-Apr-00
18-Mar-00
30-Mar-00
14-Mar-00
15-Mar-00
26-Mar-00
27-Mar-00
17-Mar-00
28-Mar-00
14-Mar-00
25-Mar-00
5-Apr-00

AB250/2
FL500/3
AB100/1
FL100/1
AB100/2
AB500/1
AB500/2
AB250/1
AB100/1
AB500/3
AB250/2
AB100/2
FL500/1
FL250/1
FL500/2
FL100/1
FL250/2
FL500/3
AB100/1
AB100/2
AB250/1
AB250/2
AB500/1
AB500/2
AB500/3
FL100/1
FL250/1
FL250/2
FL500/1
FL500/2
FL500/3

2321
2538
1609
304
1609
13.2
148.08
285.96
77.232
148.08
278.52
77.232
309.6
235.2
304.56
14.592
235.2
304.56
80450
80450
119150
116050
2860
32084
32084
15200
98000
98000
67080
65988
65988

CARL
CARL
CARS
CARS
CARS
ESA
ESA
ESA
ESA
ESA
ESA
ESA
ESL
ESL
ESL
ESL
ESL
ESL
LAB10
LAB10
LAB25
LAB25
LAB50
LAB50
LAB50
LFL10
LFL25
LFL25
LFL50
LFL50
LFL50

30-Mar-00
5-Apr-00
23-Mar-00
27-Mar-00
4-Apr-00
14-Mar-00
15-Mar-00
18-Mar-00
23-Mar-00
26-Mar-00
30-Mar-00
4-Apr-00
14-Mar-00
17-Mar-00
25-Mar-00
27-Mar-00
28-Mar-00
5-Apr-00
23-Mar-00
4-Apr-00
18-Mar-00
30-Mar-00
14-Mar-00
15-Mar-00
26-Mar-00
27-Mar-00
17-Mar-00
28-Mar-00
14-Mar-00
25-Mar-00
5-Apr-00

28
28
27
27
27
14
14
14
14
14
14
14
15
15
15
15
15
15
21
21
22
22
23
23
23
24
25
25
26
26
26
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A

Carton large
Carton large
Carton small
Carton small
Carton small
Apple essence
Apple essence
Apple essence
Apple essence
Apple essence
Apple essence
Apple essence
Lemon essence
Lemon essence
Lemon essence
Lemon essence
Lemon essence
Lemon essence
Label Apple Blossom 1
Label Apple Blossom 1
Label Apple Blossom 2
Label Apple Blossom 2
Label Apple Blossom 5
Label Apple Blossom 5
Label Apple Blossom 5
Label Fragrant Lemon
Label Fragrant Lemon
Label Fragrant Lemon
Label Fragrant Lemon
Label Fragrant Lemon
Label Fragrant Lemon
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A

http://www.production-scheduling.com

#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
BS01
BS01
BT100
BT250
BT250
BT250
BT500
BT500
BT500
CAPF
CAPF
CAPS

18-Mar-00
30-Mar-00
14-Apr-00
18-Mar-00
1-Apr-00
22-Apr-00
16-Mar-00
1-Apr-00
15-Apr-00
16-Mar-00
7-Apr-00
17-Mar-00

#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
Base Shampoo
Base Shampoo
Bottle 100ml
Bottle 250ml
Bottle 250ml
Bottle 250ml
Bottle 500ml
Bottle 500ml
Bottle 500ml
Flip cap
Flip cap
Screw cap

http://www.production-scheduling.com

CAPS
CARL
CARL
CARS
CARS
ESA
ESA
ESL
LAB10
LAB25
LAB25
LAB50
LAB50
LAB50
LFL10
LFL10
LFL25
LFL25
LFL50
LFL50

2-Apr-00
14-Mar-00
4-Apr-00
18-Mar-00
26-Apr-00
23-Mar-00
6-Apr-00
3-Apr-00
7-Apr-00
14-Mar-00
21-Mar-00
2-Apr-00
16-Apr-00
30-Apr-00
14-Apr-00
28-Apr-00
17-Mar-00
31-Mar-00
30-Mar-00
13-Apr-00

Screw cap
Carton large
Carton large
Carton small
Carton small
Apple essence
Apple essence
Lemon essence
Label Apple Blossom 1
Label Apple Blossom 2
Label Apple Blossom 2
Label Apple Blossom 5
Label Apple Blossom 5
Label Apple Blossom 5
Label Fragrant Lemon
Label Fragrant Lemon
Label Fragrant Lemon
Label Fragrant Lemon
Label Fragrant Lemon
Label Fragrant Lemon

http://www.production-scheduling.com

Supplier
AC01
AC01
AC01
AC01
AC01
AC01
AC01
AC01
AC01
AC01
AC01
AC01
AC01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
EC01
EC01
EC01
EC01
EC01
EC01
EC01
EC01

Balance
Supplier Name
Inventory Remaining Get In
On order
Ace Chemicals
34,276
32,956
0
Ace Chemicals
34,276
1,996
0
Ace Chemicals
34,276
-12,812
12,812
Ace Chemicals
34,276
-36,332
23,520
Ace Chemicals
34,276
-64,928
28,596
Ace Chemicals
34,276
-72,651
7,723
Ace Chemicals
34,276
-103,107
30,456
Ace Chemicals
34,276
-117,915
14,808
Ace Chemicals
34,276
-119,374
1,459
Ace Chemicals
34,276
-142,894
23,520
Ace Chemicals
34,276
-170,746
27,852
Ace Chemicals
34,276
-178,470
7,723
Ace Chemicals
34,276
-208,926
30,456
Best Plastics
122,472
45,240
0
Best Plastics
122,472
30,648
0
Best Plastics
122,472
-46,584
46,584
Best Plastics
184,968
90,888
0
Best Plastics
184,968
-23,496
23,496
Best Plastics
184,968
-117,576
94,080
Best Plastics
184,968
-228,984
111,408
Best Plastics
36,516
33,876
0
Best Plastics
36,516
-28,044
28,044
Best Plastics
36,516
-57,660
29,616
Best Plastics
36,516
-118,572
60,912
Best Plastics
36,516
-148,188
29,616
Best Plastics
36,516
-209,100
60,912
Best Plastics
65,738
-28,342
28,342
Best Plastics
65,738
-142,726 114,384
Best Plastics
65,738
-219,958
77,232
Best Plastics
65,738
-234,550
14,592
Best Plastics
65,738
-328,630
94,080
Best Plastics
65,738
-440,038
111,408
Best Plastics
65,738
-517,270
77,232
Best Plastics
37,818
35,178
0
Best Plastics
37,818
-26,742
26,742
Best Plastics
37,818
-56,358
29,616
Best Plastics
37,818
-117,270
60,912
Best Plastics
37,818
-146,886
29,616
Best Plastics
37,818
-207,798
60,912
Eduardo Corrugates
4,368
4,258
0
Eduardo Corrugates
4,368
1,678
0
Eduardo Corrugates
4,368
444
0
Eduardo Corrugates
4,368
-1,516
1,516
Eduardo Corrugates
4,368
-3,899
2,383
Eduardo Corrugates
4,368
-6,437
2,538
Eduardo Corrugates
4,368
-7,671
1,234
Eduardo Corrugates
4,368
-9,631
1,960

http://www.production-scheduling.com

EC01
EC01
EC01
EC01
EC01
CE01
CE01
CE01
CE01
CE01
CE01
CE01
CE01
CE01
CE01
CE01
CE01
CE01
DP01
DP01
DP01
DP01
DP01
DP01
DP01
DP01
DP01
DP01
DP01
DP01
DP01
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A

Eduardo Corrugates
Eduardo Corrugates
Eduardo Corrugates
Eduardo Corrugates
Eduardo Corrugates
Chang Essences
Chang Essences
Chang Essences
Chang Essences
Chang Essences
Chang Essences
Chang Essences
Chang Essences
Chang Essences
Chang Essences
Chang Essences
Chang Essences
Chang Essences
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A

4,368
4,368
998
998
998
1,494
1,494
1,494
1,494
1,494
1,494
1,494
1,002
1,002
1,002
1,002
1,002
1,002
211,796
211,796
266,776
266,776
24,964
24,964
24,964
32,140
83,496
83,496
117,354
117,354
117,354
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A

-11,952
-14,490
-611
-915
-2,524
1,481
1,333
1,047
970
821
543
466
692
457
153
138
-97
-402
131,346
50,896
147,626
31,576
22,104
-9,980
-42,064
16,940
-14,504
-112,504
50,274
-15,714
-81,702
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A

2,321
2,538
611
304
1,609
0
0
0
0
0
0
0
0
0
0
0
97
305
0
0
0
0
0
9,980
32,084
0
14,504
98,000
0
15,714
65,988
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A

http://www.production-scheduling.com

#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
AC01
AC01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01
BP01

#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
Ace Chemicals
Ace Chemicals
Best Plastics
Best Plastics
Best Plastics
Best Plastics
Best Plastics
Best Plastics
Best Plastics
Best Plastics
Best Plastics
Best Plastics

#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A

#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A

#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
100,000
100,000
40,000
90,000
40,000
50,000
100,000
50,000
50,000
200,000
300,000
100,000

http://www.production-scheduling.com

BP01
EC01
EC01
EC01
EC01
CE01
CE01
CE01
DP01
DP01
DP01
DP01
DP01
DP01
DP01
DP01
DP01
DP01
DP01
DP01

Best Plastics
Eduardo Corrugates
Eduardo Corrugates
Eduardo Corrugates
Eduardo Corrugates
Chang Essences
Chang Essences
Chang Essences
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing
Dave's Printing

100,000
8,000
5,000
1,000
1,000
60
100
350
50,000
70,000
60,000
20,000
20,000
20,000
10,000
10,000
60,000
60,000
40,000
40,000

Purchase Action Report

Supplier Name Description Start Date

Ace Chemicals Base Sham 14-Mar-00

Best Plastics

15-Mar-00
17-Mar-00
18-Mar-00
23-Mar-00
25-Mar-00
26-Mar-00
27-Mar-00
28-Mar-00
30-Mar-00
4-Apr-00
5-Apr-00
Base Shampoo Result
Bottle 100ml23-Mar-00
27-Mar-00
4-Apr-00
Bottle 100ml Result
Bottle 250ml17-Mar-00
18-Mar-00
28-Mar-00
30-Mar-00
Bottle 250ml Result
Bottle 500ml14-Mar-00
15-Mar-00
25-Mar-00
26-Mar-00
5-Apr-00
Bottle 500ml Result
Flip cap
17-Mar-00
18-Mar-00
23-Mar-00
27-Mar-00
28-Mar-00
30-Mar-00
4-Apr-00
Flip cap Result
Screw cap 14-Mar-00

15-Mar-00
25-Mar-00
26-Mar-00
5-Apr-00
Screw cap Result
Chang Essence Apple essen14-Mar-00
15-Mar-00
18-Mar-00
23-Mar-00
26-Mar-00
30-Mar-00

http://www.production-scheduling.com

Product/Run
AB500/1
FL500/1
AB500/2
FL250/1
AB250/1
AB100/1
FL500/2
AB500/3
FL100/1
FL250/2
AB250/2
AB100/2
FL500/3
AB100/1
FL100/1
AB100/2
FL250/1
AB250/1
FL250/2
AB250/2
AB500/1
FL500/1
AB500/2
FL500/2
AB500/3
FL500/3
FL250/1
AB250/1
AB100/1
FL100/1
FL250/2
AB250/2
AB100/2
AB500/1
FL500/1
AB500/2
FL500/2
AB500/3
FL500/3
AB500/1
AB500/2
AB250/1
AB100/1
AB500/3
AB250/2

Data
Get In
0
0
12,812
23,520
28,596
7,723
30,456
14,808
1,459
23,520
27,852
7,723
30,456
208,926
0
0
46,584
46,584
0
23,496
94,080
111,408
228,984
0
28,044
29,616
60,912
29,616
60,912
209,100
28,342
114,384
77,232
14,592
94,080
111,408
77,232
517,270
0
26,742
29,616
60,912
29,616
60,912
207,798
0
0
0
0
0
0

Balance
On order
0
0
-12,812
-36,332
-64,928
-72,651
-103,107
-117,915
-119,374
-142,894
-170,746
-178,470
-208,926
-417,851
-417,851
-417,851
-464,435
-511,019
-511,019
-534,515
-628,595
-740,003
-968,987
-968,987
-997,031
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###

Page 25 of 33

Purchase Action Report

Supplier Name Description Start Date

Product/Run
4-Apr-00
AB100/2
Apple essence Result
Lemon esse14-Mar-00
FL500/1
17-Mar-00
FL250/1
25-Mar-00
FL500/2
27-Mar-00
FL100/1
28-Mar-00
FL250/2
5-Apr-00
FL500/3
Lemon essence Result
Dave's Printing Label Apple 23-Mar-00
AB100/1
4-Apr-00
AB100/2
Label Apple Blossom 100ml Result
Label Apple 18-Mar-00
AB250/1
30-Mar-00
AB250/2
Label Apple Blossom 250ml Result
Label Apple 14-Mar-00
AB500/1
15-Mar-00
AB500/2
26-Mar-00
AB500/3
Label Apple Blossom 500ml Result
Label Fragr 27-Mar-00
FL100/1
Label Fragrant Lemon 100ml Result
Label Fragr 17-Mar-00
FL250/1
28-Mar-00
FL250/2
Label Fragrant Lemon 250ml Result
Label Fragr 14-Mar-00
FL500/1
25-Mar-00
FL500/2
5-Apr-00
FL500/3
Label Fragrant Lemon 500ml Result
Eduardo Corrug Carton large14-Mar-00
AB500/1
FL500/1
15-Mar-00
AB500/2
17-Mar-00
FL250/1
18-Mar-00
AB250/1
25-Mar-00
FL500/2
26-Mar-00
AB500/3
28-Mar-00
FL250/2
30-Mar-00
AB250/2
5-Apr-00
FL500/3
Carton large Result
Carton smal 23-Mar-00
AB100/1
27-Mar-00
FL100/1
4-Apr-00
AB100/2
Carton small Result

http://www.production-scheduling.com

Data
Get In
0
0
0
0
0
0
97
305
402
0
0
0
0
0
0
0
9,980
32,084
42,064
0
0
14,504
98,000
112,504
0
15,714
65,988
81,702
0
0
0
1,516
2,383
2,538
1,234
1,960
2,321
2,538
14,490
611
304
1,609
2,524

Balance
On order
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###

Page 26 of 33

http://www.production-scheduling.com

Inventory Rationing Logic

http://www.production-scheduling.com

Inventory Rationing Logic

Inventory rationing addresses a question that many manufacturers have:
"What products should I make with the inventory I have on hand right now?"
The system takes into account raw materials that are used by more than one product, and rations the
inventory across the products so as to even out the product inventory cover as much as possible.
You will see that On the Inventory Rationing Calculation sheet, there are 3 groups of columns:
- Bill of Materials
- First Pass Calculations
- Second Pass Calculations
The bill of materials data is repeated in the first 4 columns.

The finished product inventory and forecast is used to calculate finished product inventory cover. In other
words, how many weeks the finished product inventory will last if sales are according to forecast.
Imagine that all the finished products in inventory are broken down to their component parts, and "pooled"
by being put back into the raw material warehouse, and added to the component inventory that has not
yet been used. The forecast usage of each component is then calculated from the sales forecast of the
products they are used on, times the quantity used in each case of product. From this, the average cover
of each component is calculated.
Finished products which have a cover above the average, then have their components and their forecast
eliminated from the calculation, and the average is re-calculated. The quantity of each product that can
be made is constrained by the component with the lowest average cover. Components are alloacted to
products on that basis, the new projected inventories of products and components are calculated, and the
Second Pass Calculations repeat the logic again to determinte what products could be made with the
components that are left over.
The calculation involves circular, iterative references as follows:
F
P
Inventory
Rationing
Calculation

Components
Pivot

P
Products Pivot
F

Formula link:
PivotTable link:

F
P

After a change, click on the Refresh All icon

several times, until the data stops changing.
Put the Refresh All icon on you toolbar by right clicking on the toolbar, then Cutomise|Commands|Data
find the icon and drag it up onto your toolbar

http://www.production-scheduling.com

8
Bill of Materials

Product
AB100
AB100
AB100
AB100
AB100
AB100
AB250
AB250
AB250
AB250
AB250
AB250
AB500
AB500
AB500
AB500
AB500
AB500
FL100
FL100
FL100
FL100
FL100
FL100
FL250
FL250
FL250
FL250
FL250
FL250
FL500
FL500
FL500
FL500
FL500
FL500

Component Qty
BS01
4.800
BT100
48.000
CAPF
48.000
CARS
1.000
ESA
0.048
LAB10
50.000
BS01
12.000
BT250
48.000
CAPF
48.000
CARL
1.000
ESA
0.120
LAB25
50.000
BS01
12.000
BT500
24.000
CAPS
24.000
CARL
1.000
ESA
0.120
LAB50
26.000
BS01
4.800
BT100
48.000
CAPF
48.000
CARS
1.000
ESL
0.048
LFL10
50.000
BS01
12.000
BT250
48.000
CAPF
48.000
CARL
1.000
ESL
0.120
LFL25
50.000
BS01
12.000
BT500
24.000
CAPS
24.000
CARL
1.000
ESL
0.120
LFL50
26.000

Component Description
Base Shampoo
Bottle 100ml
Flip cap
Carton small
Apple essence
Label Apple Blossom 100ml
Base Shampoo
Bottle 250ml
Flip cap
Carton large
Apple essence
Label Apple Blossom 250ml
Base Shampoo
Bottle 500ml
Screw cap
Carton large
Apple essence
Label Apple Blossom 500ml
Base Shampoo
Bottle 100ml
Flip cap
Carton small
Lemon essence
Label Fragrant Lemon 100ml
Base Shampoo
Bottle 250ml
Flip cap
Carton large
Lemon essence
Label Fragrant Lemon 250ml
Base Shampoo
Bottle 500ml
Screw cap
Carton large
Lemon essence
Label Fragrant Lemon 500ml

8
First Pass Calculations

Product
Product Component
Total
Total
Total
Sales
Inventory
Sales
Component
Components Components Average
Forcast
Product
Cover
Forcast per
Sales
Components
in Product
in Product
Inventory
per Week Inventory Weeks
Week
Forecast
in Inventory
Inventory
Inventory
Cover
1000
1854
1.85
#VALUE!
#VALUE!
34276
#VALUE!
#VALUE! #VALUE!
1000
1854
1.85
#VALUE!
#VALUE!
122472
#VALUE!
#VALUE! #VALUE!
1000
1854
1.85
#VALUE!
#VALUE!
65738
#VALUE!
#VALUE! #VALUE!
1000
1854
1.85
#VALUE!
#VALUE!
998
#VALUE!
#VALUE! #VALUE!
1000
1854
1.85
#VALUE!
#VALUE!
1494
#VALUE!
#VALUE! #VALUE!
1000
1854
1.85
#VALUE!
#VALUE!
211796
#VALUE!
#VALUE! #VALUE!
1400
1470
1.05
#VALUE!
#VALUE!
34276
#VALUE!
#VALUE! #VALUE!
1400
1470
1.05
#VALUE!
#VALUE!
184968
#VALUE!
#VALUE! #VALUE!
1400
1470
1.05
#VALUE!
#VALUE!
65738
#VALUE!
#VALUE! #VALUE!
1400
1470
1.05
#VALUE!
#VALUE!
4368
#VALUE!
#VALUE! #VALUE!
1400
1470
1.05
#VALUE!
#VALUE!
1494
#VALUE!
#VALUE! #VALUE!
1400
1470
1.05
#VALUE!
#VALUE!
266776
#VALUE!
#VALUE! #VALUE!
800
470
0.59
#VALUE!
#VALUE!
34276
#VALUE!
#VALUE! #VALUE!
800
470
0.59
#VALUE!
#VALUE!
36516
#VALUE!
#VALUE! #VALUE!
800
470
0.59
#VALUE!
#VALUE!
37818
#VALUE!
#VALUE! #VALUE!
800
470
0.59
#VALUE!
#VALUE!
4368
#VALUE!
#VALUE! #VALUE!
800
470
0.59
#VALUE!
#VALUE!
1494
#VALUE!
#VALUE! #VALUE!
800
470
0.59
#VALUE!
#VALUE!
24964
#VALUE!
#VALUE! #VALUE!
200
488
2.44
#VALUE!
#VALUE!
34276
#VALUE!
#VALUE! #VALUE!
200
488
2.44
#VALUE!
#VALUE!
122472
#VALUE!
#VALUE! #VALUE!
200
488
2.44
#VALUE!
#VALUE!
65738
#VALUE!
#VALUE! #VALUE!
200
488
2.44
#VALUE!
#VALUE!
998
#VALUE!
#VALUE! #VALUE!
200
488
2.44
#VALUE!
#VALUE!
1002
#VALUE!
#VALUE! #VALUE!
200
488
2.44
#VALUE!
#VALUE!
32140
#VALUE!
#VALUE! #VALUE!
1200
1148
0.96
#VALUE!
#VALUE!
34276
#VALUE!
#VALUE! #VALUE!
1200
1148
0.96
#VALUE!
#VALUE!
184968
#VALUE!
#VALUE! #VALUE!
1200
1148
0.96
#VALUE!
#VALUE!
65738
#VALUE!
#VALUE! #VALUE!
1200
1148
0.96
#VALUE!
#VALUE!
4368
#VALUE!
#VALUE! #VALUE!
1200
1148
0.96
#VALUE!
#VALUE!
1002
#VALUE!
#VALUE! #VALUE!
1200
1148
0.96
#VALUE!
#VALUE!
83496
#VALUE!
#VALUE! #VALUE!
1600
766
0.48
#VALUE!
#VALUE!
34276
#VALUE!
#VALUE! #VALUE!
1600
766
0.48
#VALUE!
#VALUE!
36516
#VALUE!
#VALUE! #VALUE!
1600
766
0.48
#VALUE!
#VALUE!
37818
#VALUE!
#VALUE! #VALUE!
1600
766
0.48
#VALUE!
#VALUE!
4368
#VALUE!
#VALUE! #VALUE!
1600
766
0.48
#VALUE!
#VALUE!
1002
#VALUE!
#VALUE! #VALUE!
1600
766
0.48
#VALUE!
#VALUE!
117354
#VALUE!
#VALUE! #VALUE!

Product
Cover
Below
Average
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!

Min
Average Make
Cover Weeks
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###
#VALUE!
###

http://www.production-scheduling.com

Second Pass Calculations

Make
Use
Total Use
Product Component Component
###
#VALUE!
28714
###
#VALUE!
0
###
#VALUE!
41826
###
#VALUE!
0
###
#VALUE!
103
###
#VALUE!
0
###
#VALUE!
28714
###
#VALUE!
41826
###
#VALUE!
41826
###
#VALUE!
2393
###
#VALUE!
103
###
#VALUE!
20445
###
#VALUE!
28714
###
#VALUE!
36516
###
#VALUE!
36516
###
#VALUE!
2393
###
#VALUE!
103
###
#VALUE!
11678
###
#VALUE!
28714
###
#VALUE!
0
###
#VALUE!
41826
###
#VALUE!
0
###
#VALUE!
184
###
#VALUE!
0
###
#VALUE!
28714
###
#VALUE!
41826
###
#VALUE!
41826
###
#VALUE!
2393
###
#VALUE!
184
###
#VALUE!
23124
###
#VALUE!
28714
###
#VALUE!
36516
###
#VALUE!
36516
###
#VALUE!
2393
###
#VALUE!
184
###
#VALUE!
27881

Product
Inventory
2
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!

Product Component
Inventory
Sales
Cover
Forcast per
Weeks 2
Week 2
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!

Total
Total
Compone Compone
Compone Compone
nts in
nts in
Total
Product
nt Sales
nts in
Product
Product Average
Cover
Forecast Inventory Inventory Inventory Inventory
Below
2
2
2
2
Cover 2 Average 2
4800
5562 #VALUE!
2342.4
1.65 #VALUE!
48000
122472 #VALUE!
23424
3.04 #VALUE!
48000
23912 #VALUE!
23424
0.99 #VALUE!
1000
998 #VALUE!
488
1.49 #VALUE!
48
1391 #VALUE!
0
28.98 #VALUE!
50000
211796 #VALUE!
0
4.24 #VALUE!
16800
5562 #VALUE!
2342.4
0.47 #VALUE!
67200
143142 #VALUE!
0
2.13 #VALUE!
67200
23912 #VALUE!
23424
0.70 #VALUE!
1400
1975 #VALUE!
0
1.41 #VALUE!
168
1391 #VALUE!
0
8.28 #VALUE!
70000
246331 #VALUE!
0
3.52 #VALUE!
9600
5562 #VALUE!
2342.4
0.82 #VALUE!
19200
0 #VALUE!
0
0.00 #VALUE!
19200
1302 #VALUE!
0
0.07 #VALUE!
800
1975 #VALUE!
0
2.47 #VALUE!
96
1391 #VALUE!
0
14.49 #VALUE!
20800
13286 #VALUE!
0
0.64 #VALUE!
960
5562 #VALUE!
2342.4
8.23 #VALUE!
9600
122472 #VALUE!
23424
15.20 #VALUE!
9600
23912 #VALUE!
23424
4.93 #VALUE!
200
998 #VALUE!
488
7.43 #VALUE!
9.6
818 #VALUE!
23.424
87.63 #VALUE!
10000
32140 #VALUE!
24400
5.65 #VALUE!
14400
5562 #VALUE!
2342.4
0.55 #VALUE!
57600
143142 #VALUE!
0
2.49 #VALUE!
57600
23912 #VALUE!
23424
0.82 #VALUE!
1200
1975 #VALUE!
0
1.65 #VALUE!
144
818 #VALUE!
23.424
5.84 #VALUE!
60000
60372 #VALUE!
0
1.01 #VALUE!
19200
5562 #VALUE!
2342.4
0.41 #VALUE!
38400
0 #VALUE!
0
0.00 #VALUE!
38400
1302 #VALUE!
0
0.03 #VALUE!
1600
1975 #VALUE!
0
1.23 #VALUE!
192
818 #VALUE!
23.424
4.38 #VALUE!
41600
89473 #VALUE!
0
2.15 #VALUE!

Min
Average
Cover 2
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!

Make
Weeks 2
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!

Make
Product 2
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!

Use
Compone
nt 2
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!

Compone
Total Use
nts in
Compone Inventory
nt 2
3
2391.208
3170
23912.08
98560
23912.08
0
498.1683
500
0
1391
0
211796
2391.208
3170
0
143142
23912.08
0
0
1975
0
1391
0
246331
2391.208
3170
0
0
0
1302
0
1975
0
1391
0
13286
2391.208
3170
23912.08
98560
23912.08
0
498.1683
500
23.91208
794
24908.42
7232
2391.208
3170
0
143142
23912.08
0
0
1975
23.91208
794
0
60372
2391.208
3170
0
0
0
1302
0
1975
23.91208
794
0
89473

Make
Product
1+2
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!

http://www.production-scheduling.com

Data

Component
BS01
BT100
BT250
BT500
CAPF
CAPS
CARL
CARS
ESA
ESL
LAB10
LAB25
LAB50
LFL10
LFL25
LFL50

Sum of Compone
Sum of Compon
Sum of Use C Sum of Compone
Sum of Compon
Sum of Use Component 2
#VALUE!
#VALUE!
28714.48
960
2342.4
2391.208
#VALUE!
#VALUE!
0
9600
23424
23912.08
#VALUE!
#VALUE!
41825.92
0
0
0
#VALUE!
#VALUE!
36516
0
0
0
#VALUE!
#VALUE!
41825.92
9600
23424
23912.08
#VALUE!
#VALUE!
36516
0
0
0
#VALUE!
#VALUE! 2392.873333
0
0
0
#VALUE!
#VALUE!
0
200
488 498.1683333
#VALUE!
#VALUE!
102.9672
0
0
0
#VALUE!
#VALUE!
184.1776
9.6
23.424
23.91208
#VALUE!
#VALUE!
0
0
0
0
#VALUE!
#VALUE! 20444.66667
0
0
0
#VALUE!
#VALUE! 11678.33333
0
0
0
#VALUE!
#VALUE!
0
10000
24400 24908.41667
#VALUE!
#VALUE!
23124
0
0
0
#VALUE!
#VALUE! 27880.66667
0
0
0

um of Use Component 2

http://www.production-scheduling.com

http://www.production-scheduling.com

Data

Product
AB100
AB250
AB500
FL100
FL250
FL500

Min of Total A Min of Total A Product to make with components in inventory

#VALUE!
#VALUE!
0
#VALUE!
#VALUE!
2454
#VALUE!
#VALUE!
2694
#VALUE!
#VALUE!
2988
#VALUE!
#VALUE!
2772
#VALUE!
#VALUE!
6432