total cost calculation, using the following data. You are required to calculate the total cost for operating one DC and two DCs. This is most effectively done in excel.

Sales per year: $2,500,000

Property tax: 12% (both locations)

Operating days per year: 260

Staff per DC: 90

Warehouse fixed cost: $1,800,000

Warehouse variable cost per case: $0.45

TL per year with 1 DC: $175,000

Increase in TL with a new DC: $70,000

LTL per year with 1 DC: $250,000

Supplier incoterms: EXW

Lead time: Supplier to DC = 1 day

Customer service level target: 98% fill rate

Increase in lead time with each additional DC: 30%

Return on assets target: 11%

Base inventory with 1 DC: $900,000

SS with 1 DC: $ 150,000

Inventory Carrying Cost: 28%

Reorder cost per DC: $300

Total LTL cost decreased by 1 DC to 2 DCs: 29%