All cells open for user input are highlighted in yellow Sheet name: Value Model Enter trading partners in column A Enter number of connections per partner in column B. Some partners may have different divisions requiring separate connections Enter annual order count in column C Enter gross purchases by buyer in column D Enter connection method in column E. Explanations of different options are in the "Connection options and costs" sheet. Enter connected business messages in columns F thru K with an "X". You may choose to connect to a particular trading partner for only certain messages. Connection options and costs Enter AEC project cost or reimbursement in cell B8. See cell comment. Enter initial costs in cells B11 thru B14. See cell comment. You will likely not use all different methods, and the costs may or may not apply depending upon if you are the seller or buyer Enter initial cost amortization schedule in cell B16 Enter annual maintenance costs in cells B19 thru B22. See cell comment. You will likely not use all different methods, and the costs may or may not apply depending upon if you are the seller or buyer Enter per transaction fees in cells B25 thru F28. See cell comment. You will likely not use all different methods, and the costs may or may not apply depending upon if you are the seller or buyer and the type of connection used. Enter annual number of inventory and product movement reports in cells E30 to F30 Enter variables Order to invoice process: Enter cost per hour for employees involved in order management in cell B2. You should include "fully loaded" cost, including salary, benefits, etc. Enter estimate of time saved per transaction in cells B3 thru B6. This estimate can be based on experience of others, statistical modeling, or your best guess. Inventory reporting process (administrative costs): Enter cost per hour for employees involved in inventory reporting (if applicable) in cell B13. You should include "fully loaded" cost, including salary, benefits, etc. Enter number of employees involved in inventory reporting in cell B14. It may be fractional, i.e. 1.5 - one full time, one half of another person's role Enter estimate of time saved in cell B16. This estimate can be based on experience of others, statistical modeling, or your best guess. Inventory reporting process (inventory reduction):
This section only applies if you believe that reporting inventory will help reduce inventory levels. For example, a supplier may provide extended inventory protection for automated reporting, allow for a different business model such as consignment, or provide opportunities for exchanging "long" inventory for other products.
Enter internal rate of working capital in cell B20 Enter current inventory levels in cell B22 Enter estimate of inventory reduction (as a % of total inventory, not as a % of purchases) in cell B26 Product movement process: If you are currently sending sales reports via EDI and incurring VAN charges, enter VAN charges that can be eliminated in cell B29 Enter cost per hour for employees involved in sales reporting (if applicable) in cell B31. You should include "fully loaded" cost, including salary, benefits, etc. Enter number of employees involved in sales reporting in cell B32. It may be fractional, i.e. 1.5 - one full time, one half of another person's role Enter estimate of time saved in cell B34. This estimate can be based on experience of others, statistical modeling, or your best guess.
eConnectivity Value Assessment
tio ns
rC ou nt
Nu m be
Or d
Supplier 1 Supplier 2 Supplier 3 Supplier 4 Supplier 5 Supplier 6 Supplier 7 Supplier 8 Supplier 9 Supplier 10 Supplier 11 Supplier 12 Supplier 13 Supplier 14 All others - enter # of partners =>
1 1 1 1 1 1 1 1 1 1 1 1 1 1 6 20
1,000 900 800 700 600 500 400 300 200 100 100 100 100 100 100
$1,000,000 $900,000 $800,000 $700,000 $600,000 $500,000 $400,000 $300,000 $200,000 $100,000 $100,000 $100,000 $100,000 $100,000 $100,000
Point to Point (PTP) Hub Connection XML Hub Connection XML Hub Connection - added Hub Connection - added Hub Connection XML Point to Point (PTP) Hub Connection - added Point to Point (PTP) Hub Connection XML Point to Point (PTP) Hub Connection - added Hub Connection XML Hub Connection XML Web Portal
service service
service
service
X X X X X X X X X X X X X X
X X X X X X X X X X X X X X X
X X X X X X X X X X X X X X
Grand Total
6,000
$6,000,000
e9e1c4af-66a4-42f9-8d0f-567fb4454d1c.xls Value model
PAGE 2 OF 7
Sh ee t rd s Sh ers ip No tic eI es nv In oi ve ce nt s or y Pr Re od po uc t M rti ov ng em en t eO
am e
on ne c
se s
er N
um be
ha
ro fc
to m
Pu rc
tio n
M
et ho d
$
Co nn ec
er N
Cu s
Gr
os s
eP r
i ce
X X X X X X X X X X X X X X
X X X X X X X X X X X X X X
X X X X X X X X X X X X X X
8/12/2008
eConnectivity Value Assessment
hr s) hr s) (m an hr s) lu e Va ct (m an
Cu st Shom ip er N o Na tic m eI es e nv In oi ve ce nt s or y Pr Re od po uc t M rti OT ov ng em IR en es ou t rc e Im pa ct (m an
lu e
pa ct
Im
In ve nt or y
Im pa
Va
OT I
ur c
Re so
In ve nt or y
Supplier 1 Supplier 2 Supplier 3 Supplier 4 Supplier 5 Supplier 6 Supplier 7 Supplier 8 Supplier 9 Supplier 10 Supplier 11 Supplier 12 Supplier 13 Supplier 14 All others - enter # of partners =>
350 315 280 245 210 175 140 105 70 35 35 35 35 35 8
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $
17,500 15,750 14,000 12,250 10,500 8,750 7,000 5,250 3,500 1,750 1,750 1,750 1,750 1,750 417
139 125 111 97 83 69 55 42 28 14 14 14 14 14 -
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $
9,437 8,493 7,549 6,606 5,662 4,718 3,775 2,831 1,887 944 944 944 944 944 -
PM
17 16 14 12 10 9 7 5 3 2 2 2 2 2 -
Re so
ur c
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $
1,283 1,155 1,027 898 770 642 513 385 257 128 128 128 128 128 -
Grand Total
2,073
$ 103,667
819
$
55,676
102
$
7,572
e9e1c4af-66a4-42f9-8d0f-567fb4454d1c.xls Value model
PAGE 3 OF 7
PM
e
Va
e
lu e
8/12/2008
eConnectivity Value Assessment
hr s) hr s) no tic e -i nv oi ce tio n fe e ra ns ac
m or tiz ed
an
(m an
lu e
-o rd
m
ce
er
pa ct (
nt en an
va
Im pa
er N
lu e
-a
fe e
ct
Im
co nn ec t
re as e
to m
m ai
tio n
Va
PM
es ou r
An nu al
ra ns ac
ur c
Cu s
Re so
pe rt
ra ns ac
In c
tt o
tio n
ce
e
fe e pe rt
in
PM
Supplier 1 Supplier 2 Supplier 3 Supplier 4 Supplier 5 Supplier 6 Supplier 7 Supplier 8 Supplier 9 Supplier 10 Supplier 11 Supplier 12 Supplier 13 Supplier 14 All others - enter # of partners =>
To ta
489 440 391 342 293 244 195 147 98 49 49 49 49 49 8
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $
28,220 25,398 22,576 19,754 16,932 14,110 11,288 8,466 5,644 2,822 2,822 2,822 2,822 2,822 417
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $
(1,111) (2,500) (2,222) (278) (1,389) 556 833 1,111 (278) 1,389 1,389 (278) (278) (278)
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $
5,000 3,333 3,333 5,000 5,000 3,333 5,000 5,000 5,000 3,333 5,000 5,000 3,333 3,333 -
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $
175 150 75 25 -
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $
-
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $
-
Grand Total
2,892
$
166,915
$
(3,333) $
60,000
$
425
$
-
$
-
e9e1c4af-66a4-42f9-8d0f-567fb4454d1c.xls Value model
PAGE 4 OF 7
pe rt
Co s
lR
-s hi p
am e
8/12/2008
eConnectivity Value Assessment
ra ns a Cuctio st n om fe er e Na sh m ip n pe e o tic rt e ra ns ac tio n fe e -i pe nv rt oi ra ce ns ac tio n fe e -i nv pe en rt to ra ry ns ac tio n fe e -P M Pe rt ra ns ac tio n fe e -T ot al
Supplier 1 Supplier 2 Supplier 3 Supplier 4 Supplier 5 Supplier 6 Supplier 7 Supplier 8 Supplier 9 Supplier 10 Supplier 11 Supplier 12 Supplier 13 Supplier 14 All others - enter # of partners =>
pe rt
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $
16 16 16 16 -
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $
63 63 63 63 -
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $
254 229 154 104 -
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $
24,331 24,565 21,465 14,778 11,704 12,166 5,732 2,479 (467) (234) (3,567) (3,670) (234) (234) 694
Grand Total
$
64
$ 250
$
739
$
109,509
e9e1c4af-66a4-42f9-8d0f-567fb4454d1c.xls Value model
PAGE 5 OF 7
Ne tI
nc
re as
e
in
va lu e
8/12/2008
Connection options:
Point to Point (PTP) Hub Connection XML Hub Connection - added service Web Portal
Costs:
AEC project cost/offset Initial connection costs Point to Point (PTP) Hub Connection XML Hub Connection - added service Web Portal Amoritization of initial cost Ongoing maintence costs (annual) Point to Point (PTP) Hub Connection XML Hub Connection - added service Web Portal Per transaction fee Point to Point (PTP) Hub Connection XML Hub Connection - added service Web Portal Frequency of reporting (# of messages per year per connection) $ (50,000) one time
$ $ $ $
20,000 20,000 -
one one one one
time time time time
3 years
$ $ $ $
20,000 20,000 20,000 Inventory $ $ $ 0.25 $ 64 ProductMovement $ $ $ 0.25 $ 250
OrderCreate ShipNotice Invoice $ $ $ $ $ $ $ 0.25 $ $ $ $ $ N/A N/A N/A
Order to Invoice Processes Employee cost/hr $USD ePrice Sheets eOrders eShip notices eInvoices ePrice Sheets eOrders eShip notices eInvoices
$
50.00 1 5 5 10 0.83 4.17 4.17 8.33
minutes minutes minutes minutes $ $ $ $ per per per per
saved saved saved saved
per per per per
$ $ $ $
transaction transaction transaction transaction
Inventory Reporting Process Employee cost/hr $USD Inventory Protection Program Admin
$
Inventory Protection Program Admin Working capital rate Total purchases $USD Current inventory levels Current inventory cost Inventory Reduction target Inventory Savings Product Movement Process
$
50.00 0.5 1040 80% 832 41,620 10% 6,000,000 25% 150,000 10% 15,000
Number of employees involved in reporting inventory Current inventory reporting manhours % Manhours saved Manhours saved Net resource savings
$ $ $
% Inventory current Current cost of inventory % Inventory reduction Net inventory cost savings
Assuming conversion from EDI 867:
VAN charge elimination $ $ 2,500 Current VAN charges (annually) 50.00 0.5 1,040 10% 104 5,200
Productivity improvement:
Employee cost/hr $USD Reporting function Number of employees involved in reporting sales Current sales reporting manhours % Manhours saved Sales Rep/Acct Mgr manhours saved Net resource savings
$