Monthly Profit & Loss Report and KPIs (3 Stores)

	A	B	C	D	E	F	G
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	Cincinnati Seasonings with Three Stores - Monthly Profit & Loss Report plus KPIs
4
5	SALES REVENUE	Total Company	Seasonings Factory	Seasonings DC	Louisville Store	Indianapolis Store	Ft Wayne Store
6	Products Sold (Demand at Stores)	5,890			3,100	2,170	620
7	** Retail Markup % (enter manually)				30.00	30.00	30.00
8	Retail Sales Price				1,300	1,300	1,300
9	Total Income	$7,657,000			$4,030,000	$2,821,000	$806,000
10
11	OPERATING EXPENSE
12	Cost of Products Sold	5,890,000			3,100,000	2,170,000	620,000
13	Facility Storage & Operating Cost	3,230,200	1,364,000	1,240,000	232,500	170,500	223,200
14	Transportation Cost	49,239	10,962	38,277
15	Total Expenses	9,169,439	1,374,962	1,278,277	3,332,500	2,340,500	843,200
16
17	MONTHLY PROFIT OR LOSS
18	Gross Profit before Admin & Tax	-$1,512,439			$697,500	$480,500	-$37,200
19	Gross Profit %	-19.75%			17.31%	17.03%	-4.62%
20
21
22	SUPPLY CHAIN PERFORMANCE	Total Company	Seasonings Factory	Seasonings DC	Louisville Store	Indianapolis Store	Ft Wayne Store
23	** Spicy Cube: Product Cost	$1,000
24	Beginning Inventory On-Hand	6,150	940	3800	400	230	780
25	Ending Inventory On-Hand	11,060	2860	6040	200	520	1440
26	Percent Change	79.84%	204.26%	58.95%	-50.00%	126.09%	84.62%
27	Avg On-Hand Inventory Amount	8,667	1,888	4,937	330	388	1,124
28	Avg On-Hand Inventory Value	$8,667,000	$1,888,000	$4,937,333	$330,000	$387,667	$1,124,000
29	Product Daily Demand	190			100	70	20
30	Inventory Days of Supply	45.62			3.30	5.54	56.20
31	Vehicle Carbon Footprint (kg)	68,562
32
33	NOTES:
34	** Accept default numbers for Product Markup and Product Price, or enter other numbers manually in cells outlined in orange.
35
36	-- There is only so much cost cutting that can be done at Cincinnati Seasonings. Expenses can definitely be reduced, but the company cannot use cost cutting alone to become profitable.
37	The company also needs to increase revenue by opening new stores. It has the infrastructure in place to support a much larger business. See what happens as you add new stores
38	in Chicago, Columbus and elsewhere. First see how much you can reduce expenses in the existing 3-store supply chain. Then add more stores to your supply chain and to this
39	reporting template. How many new stores and how much revenue increase do you need to become profitable? What happens if facility rent costs are reduced by 50% or 75%?
40
41	-- 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
42
43	-- Expand this template to accomodate new products, facilities, and vehicles as you add them to your supply chain. See how cells in this reporting tab read data in the simulation data tab:
44	* Add new columns to read and display data for new facilities - repeat column G for each new store
45	* Add new rows to read and display data for new products - repeat rows 24 - 31 for each new product
46	* See "Modify P&L Reporting Template for New Facilities – A Short Tutorial" at botton of Analyzing Simulation Data section in Onlne Guide
47
48
49
50
51
52
53
54
55
56
57
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