ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
DOWNLOAD a copy of this spreadsheet to your computer by clicking on the word "File" in top left corner of this screen. Then click "Download as" in the submenu and select download format
2
3
 Business and Supply Chain Performance Optimization - Month 1
4
5
INCOMETotal CompanySeasonings FactorySeasonings DCLouisville StoreIndianapolis StoreFt Wayne Store
6
 Products Sold (Demand at Stores)5,8903,1002,170620
7
**Retail Markup %30.030.030.0
8
 Product Sales Price1,3001,3001,300
9
 Total Income$7,657,000$4,030,000$2,821,000$806,000
10
11
EXPENSE
12
 Cost of Products Sold5,890,0003,100,0002,170,000620,000
13
 Facility Storage & Operating Cost2,005,5001,117,500570,000111,00081,000126,000
14
 Vehicle Operating Cost24,6119,01815,593
15
 Total Expenses 7,920,1111,126,518585,5933,211,0002,251,000746,000
16
17
MONTHLY PROFIT OR LOSS
18
 Gross Profit -$263,111$819,000$570,000$60,000
19
 Gross Profit %-3.44%20.32%20.21%7.44%
20
21
 Vehicle Carbon Footprint35,768
22
23
SUPPLY CHAIN PERFORMANCETotal CompanySeasonings FactorySeasonings DCLouisville StoreIndianapolis StoreFt Wayne Store
24
**Spicy Cube: Product Price1,000
25
 Beginning Inventory On-Hand6,0677003957400230780
26
 Ending Inventory On-Hand1,8677508041905271
27
 Percent Change-69.23%7.14%-79.68%-52.50%-77.39%-90.90%
28
 Avg On-Hand Inventory Amount3,9447182,428296141361
29
 Avg On-Hand Inventory Value$3,944,267$718,333$2,427,800$296,000$141,133$361,000
30
 Product Daily Demand1901901901007020
31
 Inventory Days of Supply 20.763.7812.782.962.0218.05
32
 Apply EOQ Formula
33
 **Ordering Cost $35.00$35.00$35.00
34
 **Pct.of Price for Holding Cost202020
35
 Annual Holding Cost$200.00$200.00$200.00
36
 Product Annual Demand36,50025,5507,300
37
 EOQ Delivery Amount (Drop qty)1139551
38
 EOQ Delivery Frequency (Hrs)273261
39
40
NOTES:
41
** Two asterisks mean user is required to enter data in orange boxes, all other data is from downloaded simulation results.
42
43
-- This template can be expanded as needed to add new facilities and products so it will work with any other case study or supply chain model. See how cells in this reporting tab
44
read data in the simulation data tab. Expand this reporting logic as needed to cover additional facilities and products in your supply chain model.
45
* Add more columns to accomodate new facilities
46
* Add more rows to accomodate new products. Repeat rows 25 - 39 for each new product added. Also add new rows under Income and Expense
47
sections for each new product added.
48
49
-- Apply Economic Order Quantity (EOQ) equation to calculate best product delivery amounts and frequencies for each facility where products are consumed or sold.
50
EOQ delivery amounts are affected by estimates for Ordering Cost and Holding Cost as % of Price. See more in online guide section "Cutting Inventory and Operating Costs"
51
EOQ = √ 2 x (annual product demand) x (ordering cost) / (annual holding cost)
52
53
-- Default rent costs at facilities are set higher than market rates. If you wish to use market rates, find them at a commercial real estate website such as www.cityfeet.com
54
55
-- Sometimes all the vehicle cost data does not download when you export your simulation results to a spreadsheet. At the bottom of the exported simulation data you will see a header titled
56
"Vehicles Cost Report" and below that you see the names of the vehicles used in your simulation. If you do not see all of the vehicles listed it means all the vehicle data did not
57
export correctly. You can find this same data displayed on your Simulate screen when you click on the "Vehicles" tab on the right side of the screen. Copy that data and type it into the
58
spreadsheet in the columns where that data should be if all the data had exported correctly.
59
60
This optimizing template with tabs for Performance Dashboard and Facility Detail was contributed by Robert Scanlon, adjunct professor at Catholic University of America, Busch School of Business (scanlonro@cua.edu)
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100