Question

Problem 3. (Chapter 12: Storage and Handling Decisions, hint: similar to the sizing problem example in class, watch Module3Ch12Part1 Video on D2L) The Acme Manufacturing Company is concerned about its warehouse needs and how they can be best met. The company produces a line of spare parts for appliances. Due to the combination of production policies and demand patterns, warehousing space requirements vary considerably throughout the year. Space requirements are known with a great deal of certainty because the product line satisfies a replacement market. Growth, or decline, in production and sales is not anticipated in the near future. Warehouse inventory turns at the rate of two times per month. A dollar’s worth of merchandise occupies 0.1 cubic feet of warehouse space and can be stacked 10 ft. high. The product density is $5 per lb. Given aisles, administrative space, and normal operating efficiency, only 40 percent of the total warehouse space is actually used for storage. A private warehouse can be constructed and equipped for $35 per sq. ft. and can be amortized over 20 years. The cost of operation is $0.04 per lb of throughput. Annual fixed costs amount to $10 per sq. ft. of total space. Space may also be rented for a storage charge on inventory of $0.06 per lb per month and a handling charge of $0.05 per lb of throughput. Monthly sales rates for a typical year are as follows:


Consider the following two options:
(1) Construct a 10,000 sq. ft. of private warehouse and rent public warehouse as needed.
(2) Do not construct a private warehouse, and use only public warehouse.
What are the annual costs for each option, and which one has a lower annual cost?

Sales (S) Month Jan 5,000,000 Feb 4,000,000 Mar 3,000,000 Apr 2,000,000 Мay 1,000,000 250,000 June July Aug. Sept Oct 1,250,000 2,250,000 3,000,000 3,500,000 Nov 4,000,000 Dec 4,500,000 Total 33,750,000

Answer 1

Handling Decisions, hint: similar to the sizing problem example in class, watch Module3Ch12Part1 Video on D2L) The Acme Manufacturing Company is concerned about its warehouse needs and how they can be best met. The company produces a line of spare parts for appliances. Due to the combination of production policies and demand

К21 C G A E H TIT Private Warehouse + Rented Plan Area -Volume/Are Volume required (in cubic fe Total Space Required Total Lb throughput Wareshouse space (sq. ft) Month Sales ($ Rented Space (sq. ft.)L 5 50000 5000000 500000 12S000 1000000 000 46000 40000 800000 Feb 4000000 400000 100000 4000 36000 7 March 3000000 300000 30000 75000 600000 4000 26000 April 2000000 50000 400000 4000 8 200000 20000 16000 9 10 May 1000000 100000 10000 200000 25000 6000 50000 June 250000 25000 2500 6250 -1500 11 July 250000 4000 1250000 125000 12500 31250 8500 12 2250000 225000 56250 450000 4000 Aug 22500 18500 Sept 75000 600000 4000 26000 13 3000000 300000 30000 Oct 35000 700000 4000 31000 14 3500000 350000 87500 Nov 4000000 400000 100000 800000 15 40000 36000 16 Dec 4500000 450000 45000 112500 900000 41000 17 18 19 20

Total sales data is given from E5 to E16

we need to calculate volume required each month, as given in the question, that product needs 0.1 cubit ft per dollar sold, therefore volume required will be = total sales in dollar * 0.1

Value in F5= 0.1*E5, F6= 0.1*E6 and so on till F16=0.1*E16

As the products are stacked to 10ft height, therefore space required in ft for each month will be = Volume/Height

Value in G5 = F5/10, G6= F6/10,and so on till G16 = F16/10

Because the question says that only 40% of total warehouse space is utilized, hence the total space required will be = Area for product/0.4

Value in H6=G6/0.4, H7=G7/0.4 and so on till H16=G16/0.4

As given in question that product density is $5 per lb, that means 1 lb throughput gives 5 dollar sales, therefore total lb throuhput handled per month = Total sales/ 5

Value in I5=E5/5, I6= E6/5, I7=E7/5 and so on I16=E16/5

Warehouse space = 10000 sq. ft, but because as given in the question, only 40% of total warehouse space is utilized, hence we have only 4000 sq. ft. of storing space

Rented space= actual required area - 4000

Value in K5=G5-J5, K6= G6-J6 and so on till K16= G16-J16. Value in K10 will be 0 because space can be handled in the warehouse

Now lets focus on other costs involved, and analyse the excel sheet below

K M N O 1 3 4 Wareshouse space (sq. ft) Fixed Cost In house handling charges Rent handling charges Rented Space (sq. ft.) Lb handled in-house Lb handled on rent Constrution cost Rent Charger 46000 80000 920000 4000 6 4000 36000 80000 7 4000 26000 80000 4000 80000 8 16000 80000 4000 6000 10 000 4000 18500 80000 17 4000 26000 80000 14 4000 31000 80000 15 4000 36000 80000 16 4000 41000 80000 17

Now we need to find lb handled inside warehouse

Formula will be = (( total lb throughput)/(actual area required by product))*space available in warehouse

Therefore

L5 =(I5/G5)*J5

L6= (I6/G6)*J6

and so on till L16=(I16/G16)*J16

Lb handled on rent= Total lb throughput- lb handled in house

Therefore, M5=I5-L5, M6=I6-L6 and so on till M16=I16-L16. Value in M10 will be 0 because lb required can be handled in house

Fixed cost = $10 per sq.ft of total space annually, or $10/12 per sq. st monthly

Total space is 10000 sq. ft. in warehouse, therefore fixed cost per month = ($10/12)*10000

Value in N5= (10/12) * 10000, and will be same in all the months

Construction cost is given to be $35 per sq. ft, amortized over 20 years or 20*12 = 240 months

Total space = 10000 sq. ft

Therefore const. cost monthly =(35*10000)/240

Value in O5 = (35*10000)/240, and will be same for all months

In house handling charges= cost of handling ( $.04 per lb of throughput- given) * total lb handled in house

Value in P5= .04*L5, P6= .04*L6 and so on till P16 = .04*L16

Rent Charges = 0.06 per lb charge*total lb stored on rent

Value in Q5= .06*M6, Q6=.06*M7 and so on till Q16=.06*M16

Rent handling charges = .05*lb handled on rent

Value in R5= .05*M5, R6=.05*M7 and so on till R16

Then find the total cost of all- fixed, construction, In house handling charges, Rent charges, rental handling charges

Then find the total cost of all- fixed+construction+In house handling+Rent+rental handling

The final cost will be = $796100 annually

See the excel below

м O O THHIT 4 Wareshouse space (sq. ft) Rented Space (sq. ft.) Lb handled -house Lb handled on rent Fixed Cost Constrution cost In house handling charges Rent Charger Rent handling charges 4000 3200 55200 46000 5 46000 80000 920000 8333.333333 1458.333333 8333.333333 43200 4000 36000 80000 720000 1458.333333 3200 36000 6 4000 80000 8333.333333 3200 31200 26000 520000 1458.333333 26000 16000 320000 8333.333333 1458.333333 .. 120 72 601 A000 80000 3200 10 8333.333333 1458.333333 4000 80000 11 8500 170000 8333.333333 1458.333333 3200 10200 8500 4000 80000 18500 12 18500 370000 8333.333333 1458.333333 3200 22200 4000 13 26000 80000 520000 8333.333333 1458.333333 3200 31200 26000 4000 80000 3200 37200 31000 14 31000 620000 8333.333333 1458.333333 4000 80000 3200 36000 15 36000 720000 8333.333333 1458.333333 43200 4000 41000 80000 820000 8333.333333 3200 49200 41000 16 1458,333333 17 18 Total= 17500 100000 38400 349200 291000 Total Cost 19 796100 20

Value in N18= SUM(N5:N16)

Value in O18= SUM (O5:O16) and so on till R18=SUM(R5:R16)

Value in N19 = SUM(N18:R18)

Option 2: only use a public warehouse

In this, same excel sheet and explanations can be used easily.

The changes will be=

All lb will be handled on rent

No fixed cost of warehouse as there is no private warehouse

No in-house handling charges as there are no inhouse operation

So, make J5:J16 = 0 (private warehouse space)

L5:L16=0 (In house lb handled)

N5:N16=0 ( Fixed cost)

O5:O16=0 ( Construction cost)

P5:P16=0 (in-house handling cost)

The excel would like

D O N M 1 2 In house handling charges Constrution cost 3 Fixed Cost Lb handled on rent Lb handled in-house Total Lb throughput Wareshouse space (sq. ft) 0 Rented Space (sq. ft.) 4 Total Space Required 0 1000000 0 50000 1000000 0 0 125000 800000 40000 0 800000 0 0 100000 6 600000 0 0 30000 0 600000 0 75000 7 400000 0 20000 400000 0 50000 0 8 200000 0 10000 200000 0 25000 0 9 50000 0 2500 50000 0 6250 0 10 250000 0 12500 0 250000 31250 0 11 450000 0 22500 450000 0 56250 0 12 600000 0 30000 600000 0 75000 0 13 0 700000 0 35000 700000 0 87500 0 14 0 800000 0 40000 800000 100000 0 15 0 900000 45000 0 900000 112500 16 0 17 Total 0 18 742500 Total Cost- 19 20

The complete lb throughput will be handled on rent

THe values in the Column: Lb handled on rent (M5:M16)+ Rent Charges (Q5:Q16) + Rental handling charges (P5:P16) will change automatically.

The total cost figure would be = $742500

N M Rent handling charges Rent Charger In house handling charges Constrution cost Fixed Cost 4 Wareshouse space (sq. ft) Rented Space (sq. ft.) Lb handled in-house 0 Lb handled on rent 50000 60000 0 0 1000000 40000 50000 0 48000 0 0 800000 0 30000 40000 0 36000 6 0 0 0 600000 0 20000 30000 24000 0 7 0 0 0 400000 0 20000 10000 0 12000 8 200000 2500 10000 3000 C 0 0 50000 12500 2500 15000 C 10 0 0 250000 0 12500 22500 C 27000 11 0 450000 0 30000 22500 C 36000 12 0 0 0 600000 0 35000 30000 42000 0 13 0 0 700000 0 35000 40000 C 48000 14 C 800000 0 45000 40000 54000 15 0 0 900000 45000 0 337500 405000 17 0 Total 18 742500 Total Cost- 19 20