Copy of P&L Reporting Template plus EOQ and Dashboard - Beta Test Ver1.2 Opt Test
 Share
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

 
View only
 
 
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Download a copy of this spreadsheet to your computer by clicking on "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 - Month 1
4
5
INCOMETotal Company
Seasonings Factory
Seasonings 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 Cost
2,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
22
SUPPLY CHAIN PERFORMANCE
Total Company
Seasonings Factory
Seasonings DCLouisville StoreIndianapolis StoreFt Wayne Store
23
**Spicy Cube: Product Price1,000
24
Beginning Inventory On-Hand6,0677003957400230780
25
Ending Inventory On-Hand1,8677508041905271
26
Percent Change-69.23%7.14%-79.68%-52.50%-77.39%-90.90%
27
Avg On-Hand Inventory Amount
3,9447182,428296141361
28
Avg On-Hand Inventory Value$23,929,866$718,333$2,427,800$296,000$141,133$361,000
29
Product Daily Demand1901901901007020
30
Inventory Days of Supply 20.763.7812.782.962.0218.05
31
Apply EOQ Formula
32
**Ordering Cost $35.00$35.00$35.00
33
**Pct.of Price for Holding Cost202020
34
Annual Holding Cost$200.00$200.00$200.00
35
Product Annual Demand36,50025,5507,300
36
EOQ Delivery Amount (Drop qty)
1139551
37
EOQ Delivery Frequency (Hrs)273261
38
39
NOTES:
40
** Two asterisks mean user is required to enter data in orange boxes, all other data is from downloaded simulation results.
41
42
-- 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
43
read data in the simulation data tab. Expand this reporting logic as needed to cover additional facilities and products in your supply chain model.
44
* Add more columns to accomodate new facilities
45
* Add more rows to accomodate new products. Repeat rows 25 - 39 for each new product added. Also add new rows under Income and Expense
46
sections for each new product added.
47
48
-- Apply Economic Order Quantity (EOQ) equation to calculate best product delivery amounts and frequencies for each facility where products are consumed or sold.
49
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"
50
EOQ = √ 2 x (annual product demand) x (ordering cost) / (annual holding cost)
51
52
-- 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
53
54
-- 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
55
"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
56
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
57
spreadsheet in the columns where that data should be if all the data had exported correctly.
58
59
60
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
Loading...
Main menu