Independant Demand Inventory Excel Assignment I have attached the assignment along with the supporting files. Please complete all the questions. Independen
Independant Demand Inventory Excel Assignment I have attached the assignment along with the supporting files. Please complete all the questions. Independent Demand Inventory
The following problems have been selected from Chapter 20 of your textbook. Please complete
an analysis for each of the problems using the data given. Your deliverable will be a Microsoft
Office-compatible file, preferably an Excel spreadsheet, with your answers clearly highlighted in
the work to arrive at those answers shown.
What is the order quantity which will minimize the total annual cost of purchasing and inventorying the item?
35000
2000
10
EOQ
3741,7
Q
AOC
ACC
500 140000
2500
550 127273
2750
140000
600 116667
3000
650 107692
3250
120000
700 100000
3500
750 100000
93333
3750
800 87500
4000
850 80000
82353
4250
900 77778
4500
60000
950 73684
4750
1000 70000
5000
40000
1050 66667
5250
1100 20000
63636
5500
1150 60870
5750
0
1200 58333
6000
1250 56000
6250
1300 53846
6500
1350 51852
6750
1400 50000
7000
1450 48276
7250
1500 46667
7500
1550 45161
7750
1600 43750
8000
1650 42424
8250
1700 41176
8500
1750 40000
8750
1800 38889
9000
1850 37838
9250
1900 36842
9500
1950 35897
9750
2000 35000 10000
2050 34146 10250
2100 33333 10500
2150 32558 10750
2200 31818 11000
2250 31111 11250
TAC
142500
130023
119667
110942
103500
97083
91500
86603
82278
78434
75000
71917
69136
66620
64333
62250
60346
58602
57000
55526
54167
52911
51750
50674
49676
48750
47889
47088
46342
45647
45000
44396
43833
43308
42818
42361
1
5
9
13
17
21
25
29
33
37
41
45
49
Annual Demand
Ordering Cost
Holding Cost
Total Ann
2300
2350
2400
2450
2500
2550
2600
2650
2700
2750
2800
2850
2900
2950
3000
3050
3100
3150
3200
3250
3300
3350
3400
3450
3500
3550
3600
3650
3700
3750
3800
3850
3900
3950
4000
4050
4100
30435
29787
29167
28571
28000
27451
26923
26415
25926
25455
25000
24561
24138
23729
23333
22951
22581
22222
21875
21538
21212
20896
20588
20290
20000
19718
19444
19178
18919
18667
18421
18182
17949
17722
17500
17284
17073
11500
11750
12000
12250
12500
12750
13000
13250
13500
13750
14000
14250
14500
14750
15000
15250
15500
15750
16000
16250
16500
16750
17000
17250
17500
17750
18000
18250
18500
18750
19000
19250
19500
19750
20000
20250
20500
41935
41537
41167
40821
40500
40201
39923
39665
39426
39205
39000
38811
38638
38479
38333
38201
38081
37972
37875
37788
37712
37646
37588
37540
37500
37468
37444
37428
37419
37417
37421
37432
37449
37472
37500
37534
37573
4150
4200
4250
4300
4350
4400
4450
4500
4550
4600
4650
4700
4750
4800
4850
4900
4950
5000
5050
5100
5150
5200
5250
5300
5350
5400
5450
5500
5550
5600
5650
5700
5750
5800
5850
5900
5950
16867
16667
16471
16279
16092
15909
15730
15556
15385
15217
15054
14894
14737
14583
14433
14286
14141
14000
13861
13725
13592
13462
13333
13208
13084
12963
12844
12727
12613
12500
12389
12281
12174
12069
11966
11864
11765
20750
21000
21250
21500
21750
22000
22250
22500
22750
23000
23250
23500
23750
24000
24250
24500
24750
25000
25250
25500
25750
26000
26250
26500
26750
27000
27250
27500
27750
28000
28250
28500
28750
29000
29250
29500
29750
37617
37667
37721
37779
37842
37909
37980
38056
38135
38217
38304
38394
38487
38583
38683
38786
38891
39000
39111
39225
39342
39462
39583
39708
39834
39963
40094
40227
40363
40500
40639
40781
40924
41069
41216
41364
41515
6000
6050
6100
6150
6200
6250
6300
6350
6400
6450
6500
6550
6600
6650
6700
6750
6800
6850
6900
6950
7000
7050
7100
7150
7200
7250
7300
7350
7400
7450
7500
11667
11570
11475
11382
11290
11200
11111
11024
10938
10853
10769
10687
10606
10526
10448
10370
10294
10219
10145
10072
10000
9929,1
9859,2
9790,2
9722,2
9655,2
9589
9523,8
9459,5
9396
9333,3
30000
30250
30500
30750
31000
31250
31500
31750
32000
32250
32500
32750
33000
33250
33500
33750
34000
34250
34500
34750
35000
35250
35500
35750
36000
36250
36500
36750
37000
37250
37500
41667
41820
41975
42132
42290
42450
42611
42774
42938
43103
43269
43437
43606
43776
43948
44120
44294
44469
44645
44822
45000
45179
45359
45540
45722
45905
46089
46274
46459
46646
46833
49
53
57
61
65
69
73
77
81
85
89
93
97
101
105
109
113
117
121
125
129
133
137
141
Total Annual Cost
AOC
ACC
TAC
35000
500
10
600
140
POQ
Run Time
Imax
Down Time
2136,6
3,5611
1638,1
11,701
POQ
Run Time
Imax
Down Time
5123,5
17,078
2732,5
19,518
Q
1000
1050
1100
1150
1200
1250
1300
1350
1400
1450
1500
1550
1600
1650
1700
1750
1800
1850
1900
1950
2000
2050
2100
2150
2200
2250
2300
2350
2400
2450
2500
2550
2600
2650
2700
2750
ASC
17500
16667
80000
15909
70000
15217
60000
14583
14000
50000
13462
40000
12963
30000
12500
12069
20000
11667
10000
11290
0
10938
10606
10294
10000
9722,2
9459,5
9210,5
8974,4
8750
8536,6
8333,3
8139,5
7954,5
7777,8
7608,7
7446,8
7291,7
7142,9
7000
6862,7
6730,8
6603,8
6481,5
6363,6
ACC
3833,3
4025
4216,7
4408,3
4600
4791,7
4983,3
5175
5366,7
5558,3
5750
5941,7
6133,3
6325
6516,7
6708,3
6900
7091,7
7283,3
7475
7666,7
7858,3
8050
8241,7
8433,3
8625
8816,7
9008,3
9200
9391,7
9583,3
9775
9966,7
10158
10350
10542
TAC
21333
20692
20126
19626
19183
18792
18445
18138
17867
17627
17417
17232
17071
16931
16811
16708
16622
16551
16494
16449
16417
16395
16383
16381
16388
16403
16425
16455
16492
16535
16583
16638
16697
16762
16831
16905
Imax
766,67
Chart Title
805
843,33
881,67
920
958,33
996,67
1035
1073,3
1111,7
1150
1188,3
1226,7
1265
ASC
ACC
1303,3
1341,7
1380
1418,3
1456,7
1495
1533,3
1571,7
1610
1648,3
1686,7
1725
1763,3
1801,7
1840
1878,3
1916,7
1955
1993,3
2031,7
2070
2108,3
1
7
13
19
25
31
37
43
49
55
61
67
73
79
85
91
97
103
109
Annual Demand
Setup Cost
Holding Cost
Production Rate
Demand Rate
2800
2850
2900
2950
3000
3050
3100
3150
3200
3250
3300
3350
3400
3450
3500
3550
3600
3650
3700
3750
3800
3850
3900
3950
4000
4050
4100
4150
4200
4250
4300
4350
4400
4450
4500
4550
4600
6250
6140,4
6034,5
5932,2
5833,3
5737,7
5645,2
5555,6
5468,8
5384,6
5303
5223,9
5147,1
5072,5
5000
4929,6
4861,1
4794,5
4729,7
4666,7
4605,3
4545,5
4487,2
4430,4
4375
4321
4268,3
4216,9
4166,7
4117,6
4069,8
4023
3977,3
3932,6
3888,9
3846,2
3804,3
10733
10925
11117
11308
11500
11692
11883
12075
12267
12458
12650
12842
13033
13225
13417
13608
13800
13992
14183
14375
14567
14758
14950
15142
15333
15525
15717
15908
16100
16292
16483
16675
16867
17058
17250
17442
17633
16983
17065
17151
17241
17333
17429
17528
17631
17735
17843
17953
18066
18180
18297
18417
18538
18661
18786
18913
19042
19172
19304
19437
19572
19708
19846
19985
20125
20267
20409
20553
20698
20844
20991
21139
21288
21438
2146,7
2185
2223,3
2261,7
2300
2338,3
2376,7
2415
2453,3
2491,7
2530
2568,3
2606,7
2645
2683,3
2721,7
2760
2798,3
2836,7
2875
2913,3
2951,7
2990
3028,3
3066,7
3105
3143,3
3181,7
3220
3258,3
3296,7
3335
3373,3
3411,7
3450
3488,3
3526,7
4650
4700
4750
4800
4850
4900
4950
5000
5050
5100
5150
5200
5250
5300
5350
5400
5450
5500
5550
5600
5650
5700
5750
5800
5850
5900
5950
6000
6050
6100
6150
6200
6250
6300
6350
6400
6450
3763,4
3723,4
3684,2
3645,8
3608,2
3571,4
3535,4
3500
3465,3
3431,4
3398,1
3365,4
3333,3
3301,9
3271
3240,7
3211
3181,8
3153,2
3125
3097,3
3070,2
3043,5
3017,2
2991,5
2966,1
2941,2
2916,7
2892,6
2868,9
2845,5
2822,6
2800
2777,8
2755,9
2734,4
2713,2
17825
18017
18208
18400
18592
18783
18975
19167
19358
19550
19742
19933
20125
20317
20508
20700
20892
21083
21275
21467
21658
21850
22042
22233
22425
22617
22808
23000
23192
23383
23575
23767
23958
24150
24342
24533
24725
21588
21740
21893
22046
22200
22355
22510
22667
22824
22981
23140
23299
23458
23619
23779
23941
24103
24265
24428
24592
24756
24920
25085
25251
25416
25583
25750
25917
26084
26252
26421
26589
26758
26928
27098
27268
27438
3565
3603,3
3641,7
3680
3718,3
3756,7
3795
3833,3
3871,7
3910
3948,3
3986,7
4025
4063,3
4101,7
4140
4178,3
4216,7
4255
4293,3
4331,7
4370
4408,3
4446,7
4485
4523,3
4561,7
4600
4638,3
4676,7
4715
4753,3
4791,7
4830
4868,3
4906,7
4945
6500
6550
6600
6650
6700
6750
6800
6850
6900
6950
7000
7050
7100
7150
7200
7250
7300
7350
7400
7450
7500
7550
7600
7650
7700
7750
7800
7850
7900
7950
8000
8050
8100
8150
8200
8250
8300
2692,3
2671,8
2651,5
2631,6
2611,9
2592,6
2573,5
2554,7
2536,2
2518
2500
2482,3
2464,8
2447,6
2430,6
2413,8
2397,3
2381
2364,9
2349
2333,3
2317,9
2302,6
2287,6
2272,7
2258,1
2243,6
2229,3
2215,2
2201,3
2187,5
2173,9
2160,5
2147,2
2134,1
2121,2
2108,4
24917
25108
25300
25492
25683
25875
26067
26258
26450
26642
26833
27025
27217
27408
27600
27792
27983
28175
28367
28558
28750
28942
29133
29325
29517
29708
29900
30092
30283
30475
30667
30858
31050
31242
31433
31625
31817
27609
27780
27952
28123
28295
28468
28640
28813
28986
29160
29333
29507
29681
29856
30031
30205
30381
30556
30732
30907
31083
31260
31436
31613
31789
31966
32144
32321
32499
32676
32854
33032
33210
33389
33567
33746
33925
4983,3
5021,7
5060
5098,3
5136,7
5175
5213,3
5251,7
5290
5328,3
5366,7
5405
5443,3
5481,7
5520
5558,3
5596,7
5635
5673,3
5711,7
5750
5788,3
5826,7
5865
5903,3
5941,7
5980
6018,3
6056,7
6095
6133,3
6171,7
6210
6248,3
6286,7
6325
6363,3
8350
8400
8450
8500
8550
8600
8650
8700
8750
8800
8850
8900
8950
9000
9050
9100
9150
9200
9250
9300
9350
9400
9450
9500
9550
9600
9650
9700
9750
9800
9850
9900
9950
10000
2095,8
2083,3
2071
2058,8
2046,8
2034,9
2023,1
2011,5
2000
1988,6
1977,4
1966,3
1955,3
1944,4
1933,7
1923,1
1912,6
1902,2
1891,9
1881,7
1871,7
1861,7
1851,9
1842,1
1832,5
1822,9
1813,5
1804,1
1794,9
1785,7
1776,6
1767,7
1758,8
1750
32008
32200
32392
32583
32775
32967
33158
33350
33542
33733
33925
34117
34308
34500
34692
34883
35075
35267
35458
35650
35842
36033
36225
36417
36608
36800
36992
37183
37375
37567
37758
37950
38142
38333
34104
34283
34463
34642
34822
35002
35181
35361
35542
35722
35902
36083
36264
36444
36625
36806
36988
37169
37350
37532
37713
37895
38077
38259
38441
38623
38805
38987
39170
39352
39535
39718
39900
40083
6401,7
6440
6478,3
6516,7
6555
6593,3
6631,7
6670
6708,3
6746,7
6785
6823,3
6861,7
6900
6938,3
6976,7
7015
7053,3
7091,7
7130
7168,3
7206,7
7245
7283,3
7321,7
7360
7398,3
7436,7
7475
7513,3
7551,7
7590
7628,3
7666,7
TAC
103
109
115
121
127
133
139
145
151
157
163
169
175
181
Annual Demand
Ordering Cost
Holding Cost
35000
2000
10
Base
5000
7000
$3
$2,80
$2,75
EOQ
3741,7
Q
AOC
ACC
3742 $18.707 $18.710
5000 $14.000 $25.000
7000 $10.000 $35.000
Acq Cost
$105.000
$98.000
$96.250
TAC
$142.417
$137.000
$141.250
C0
Cu
Avg Demand
SD Demand
$8
$2
30
12
Service Level
0,8
Z Value
0,84162
Stock Level
40,0995
Operations Management
and Quality Enhancement
Dr. Ronald K. Satterfield
Muma College of Business
University of South Florida
Independent Demand Inventory
Dr. Ronald K. Satterfield
Muma College of Business
University of South Florida
How Much To Order
Plan for This Presentation
Introduction to Inventory Management
Fixed Order Period Models
Fixed Order Quantity Models
Single-Period Models
Economic Order Quantity Models
Summary
Production Order Quantity Models
Your Review
Price Break Models
Plan for This Presentation
Introduction to Inventory Management
Fixed Order Period Models
Fixed Order Quantity Models
Single-Period Models
Economic Order Quantity Models
Summary
Production Order Quantity Models
Your Review
Price-Break Models
Deterministic as opposed to stochastic.
Plan for This Presentation
Introduction to Inventory Management
Fixed Order Period Models
Fixed Order Quantity Models
Single-Period Models
Economic Order Quantity Models
Summary
Production Order Quantity Models
Your Review
Price-Break Models
Independent as opposed to Dependent Demand
Plan for This Presentation
Introduction to Inventory Management
Fixed Order Period Models
Fixed Order Quantity Models
Single-Period Models
Economic Order Quantity Models
Summary
Production Order Quantity Models
Your Review
Price-Break Models
NO SAFETY STOCK
Order of Presentation
Introduction to Inventory
Fixed Order Quantity Models (FOQ)
Economic Order Quantity Model
Production Order Quantity Model
Price Break Model
Fixed Order Period Model
Single Period Model
Definition of Inventory
The stock of any item or resource used in an organization.
Inventory is money that looks like something else.
An illiquid asset.
A necessary evil (usually).
Introduction
FOQ – EOQ
FOQ – POQ
FOQ Price Break
FOP
Single Period
Summary
Your Review
Reasons for Inventory Management
Inventory is Money
Inventory is Illiquid
Inventory Can Slow Down the Supply Chain
Inventory Can Require Large Investment
Inventory Can Have High Cost to Keep
Introduction
FOQ – EOQ
FOQ – POQ
FOQ Price Break
FOP
Single Period
Summary
Your Review
Purposes of Inventory
Maintain Independence of Operations (Decouple)
Meet Variation in Demand (Variation)
Give Flexibility in Production Scheduling (Decouple)
Safeguard Raw Materials Sources (Variation, Decouple)
Take Advantage of Quantity Discounts
Situation-Specific Reasons
Introduction
FOQ – EOQ
FOQ – POQ
FOQ Price Break
FOP
Single Period
Summary
Your Review
Types of Inventory
Raw Materials Inventory
Work-In-Process Inventory (WIP)
Finished Goods Inventory
Maintenance/Repair/Operating (MRO) Inventory
Pipeline Inventory
Introduction
FOQ – EOQ
FOQ – POQ
FOQ Price Break
FOP
Single Period
Summary
Your Review
Definition of Inventory Turns
A measure of the expected number of times inventory is
replaced over a year.
The higher the better, but why?
Introduction
FOQ – EOQ
FOQ – POQ
FOQ Price Break
FOP
Single Period
Summary
Your Review
An Inventory Cycle
Introduction
FOQ – EOQ
FOQ – POQ
FOQ Price Break
FOP
Single Period
Summary
Your Review
The Basic Economic Order Quantity Model
What it is, how it works.
Introduction
FOQ – EOQ
FOQ – POQ
FOQ Price Break
FOP
Single Period
Summary
Your Review
The Basic Economic Order Quantity Model
Annual Demand = 35000 units
Ordering Cost = $2,000/order
Holding Cost = $10/unit/year
An Example
Manual Calculation, Then a Spreadsheet
Introduction
FOQ – EOQ
FOQ – POQ
FOQ Price Break
FOP
Single Period
Summary
Your Review
The Basic Economic Order Quantity Model
A Variant: The Two-Bin System
Introduction
FOQ – EOQ
FOQ – POQ
FOQ Price Break
FOP
Single Period
Summary
Your Review
The Production Order Quantity Model
?????? ??
?? ?????
What it is, how it works.
Introduction
FOQ – EOQ
FOQ – POQ
FOQ Price Break
FOP
Single Period
Summary
Your Review
The Production Order Quantity Model
Annual Demand = 35000 units
Ordering Cost = $2,000/order
Holding Cost = $10/unit/year
Daily Production Rate = 300
An Example
Manual Calculation, Then a Spreadsheet
Introduction
FOQ – EOQ
FOQ – POQ
FOQ Price Break
FOP
Single Period
Summary
Your Review
Price Break (Quantity Discount) Model
Annual Demand = 35000 units
Ordering Cost = $2,000/order
Holding Cost = $10/unit/year
Base Price: $3/unit
Orders > 5000: $2.80/unit
Orders > 7000: $2.75/unit
An Example
Spreadsheet Calculation
Introduction
FOQ – EOQ
FOQ – POQ
FOQ Price Break
FOP
Single Period
Summary
Your Review
The Fixed Order Period Model
? + ??) + z????+?? ? ??
?? = ??(??
What it is, how it works.
Avg Daily Demand
Days Between Reviews
Lead Time to Fill an Order
Service Level and Std Dev.
Std Dev. of Demand
Current Inventory Level
Introduction
FOQ – EOQ
FOQ – POQ
FOQ Price Break
FOP
Single Period
Summary
Your Review
The Fixed Order Period Model
? + ??) + z????+?? ? ??
?? = ??(??
What it is, how it works.
Avg Daily Demand = 25
Days Between Reviews = 30
Lead Time to Fill an Order = 7
Service Level and Std Dev. = 95% (1.64)
Std Dev. of Demand = 10
Current Inventory Level = 100
Introduction
FOQ – EOQ
FOQ – POQ
FOQ Price Break
FOP
Single Period
Summary
Your Review
The Single Period Model
Service Level
What it is, how it works.
????
=
???? +????
Cost per unit overstocked
Cost per unit understocked
Introduction
FOQ – EOQ
FOQ – POQ
FOQ Price Break
FOP
Single Period
Summary
Your Review
The Fixed Order Period Model
Service Level
What it is, how it works.
Chicken Quinoa Salad in a Deli
????
=
???? +????
Co = $8
Cu = $2
Avg Daily Demand = 30
Std Dev Demand = 12
Introduction
FOQ – EOQ
FOQ – POQ
FOQ Price Break
FOP
Single Period
Summary
Your Review
Summary
Introduction to Inventory Management
Fixed Order Quantity Models
Economic Order Quantity Model
Production Order Quantity Model
Price-Break Model
Fixed Order Period Model
Single Period Model
Introduction
FOQ – EOQ
FOQ – POQ
FOQ Price Break
FOP
Single Period
Summary
Your Review
Summary
Feature
FOQ Model
FOP Model
Order Quantity
Constant
Variable
Order Timing
When inventory level drops to reorder
point
Fixed Interval
Record Keeping
Continual
Itermittent
Size of Inventory
Usually less than FOP
Usually greater than FOQ
Effort to Maintain
Usually Higher due to record keeping
Usually more efficient on this metric
Type of Items
Higher cost and importance
Lower cost and of lesser importance
Introduction
FOQ – EOQ
FOQ – POQ
FOQ Price Break
FOP
Single Period
Summary
Your Review
Your Review
Understand How These are Calculated
Understand Inventory Turns
EOQ
Review ABC Classification Systems
TAC and Components
Review Cycle Counting
POQ
Material on Safety Stock Will Be In the
Next Module
Understand Price Break Model Dynamics
Know Fixed Order Period
Know Single Period
Introduction
FOQ – EOQ
FOQ – POQ
FOQ Price Break
FOP
Single Period
Summary
Your Review
Lesson Plan
Independent Demand Inventory
QMB-6603 Operations Management & Quality Enhancement
Time Window
July 7 12, 2020
Focus
This module presents an overview of basic inventory management, focusing
on independent demand cases. The basic EOQ and EPQ models are
discussed and demonstrated. A method for evaluating quantity discount
offers is also shown. The module finishes with a presentation of a simple
Fixed Order Period inventory model as well as a Single Period model. The
primary objective is to show what factors have the greatest impact on
decisions for the purchase, manufacture, and holding of inventory. Examples
of each model are shown.
Video and Other
Materials for
Review
Module lecture file name: 6603 Independent Demand Inventory.html
Unmarked Lecture exhibits: 6603 Independent Demand Inventory Lecture
Exhibits.pptx
Marked Lecture exhibits used in the video presentation: 6603 Independent
Demand Inventory Lecture Exhibits Markup.pptx
The spreadsheet built in the video lecture : 6603 Independent Demand
Inventory Lecture Examples.xlsx
All these materials are available under the Modules section of Canvas.
Reading
Chapter 20: Inventory Management in our textbook, Operations and Supply
Chain Management, by F. Robert Jacobs and Richard B. Chase, 16th edition.
Assignment
Assignment file: 6603 Independent Demand Inventory Assignment.docx
available under the Assignments section of Canvas. This assignment is
due via electronic submission to Canvas by July 12, 2020 at 11:59 PM.
Its funny the way most people love the dead.
Once youre dead, youre made for life.
? Jimi Hendrix
Purchase answer to see full
attachment
