Estimated reading time: 2 minutes, 55 seconds

EDI Costing Analysis Spreadsheet Explanation

currencyOver the last couple months we have been discussing uncovering the actualized financial benefit of an EDI program to the bottom line of an organization. In our webinar held on May 15, we discussed the subject in depth, and shared the spreadsheet model that I have developed in order to answer this question. In order to supplement that webinar, I would also like to review the various pieces of the model and how you could use it for future analysis.


As you open the model, you will notice that there have been four categories defined for the data points used. The General set will calculate costs that are inherent to the order processing function regardless of delivery type. The Manual set will assist you in determining costs needed to process orders with a data entry department, and the EDI set denotes costs that are incurred only when an EDI function is needed. Each of these data sets are subtotaled and combined to provide the overall impact presented in the Summary data set.

Within the subsets, I have color coded cells based on function so they would be easy and quick to identify. Cells that are highlighted green indicate data that needs to be manually entered into the model. Yellow cells highlight calculated values from the manually entered cells. Orange cells show the subtotal values for each of the subsets, and red with white font indicate overall result of the analysis (It is important to note here that yellow, orange, and red indicate there is a formula in that field and should not be modified with hard data) .

In the model provided (that you can download HERE), I have included some very generic data points in each of the categories. Generally these have been grouped so calculations are directly manual data points so category builds upon itself from top to bottom. The template has been designed with flexibility in mind, so you may add categories to the body of the calculation, but remember to change the formulas in dependent calculations.

Following is a guide to the calculations and their dependencies.

General

KC to Transaction

Per TX Charge = Monthly CK Charge(M)/Monthly TX Volume(M)

Avg Order Value = Order Count(M)/Monthly Sales(M)

Penetration Breakdown

Manual Orders = Order Count(M) * (1-EDI Penetration(M))

EDI Orders = Order Count(M) * EDI Penetration(M)

Error Rates – Goal is to get $ cost per order

Manual = Rate(M)* Manual Order Value (PB Calc)

EDI = Rate(M)* EDI Order Value (PB Calc)

Cost Per order = Monthly Cost (Calc)/ Number of Orders(PB Calc)

Chargebacks

Cost per order = Monthly Chargeback Cost/Number of EDI Orders (PB Calc)

Cost per order subtotals

Manual = Supporting totals + Error Rate Totals

EDI = Supporting Totals + Chargeback Totals + Error Rate Totals + Per TX Charge

Monthly Costs is cost per order + total number of orders in that category

Cost No EDI = Cost per order + total number of orders

Sub total savings is Monthly costs with EDI – Cost No EDI

Manual

Misc

Orders/Day = Orders/Hour*8

Orders/Month = Orders/Day*Workdays

Personnel Needed

With EDI = (Orders/Month)/Manual Orders {manual orders count from General column}

No EDI = (Orders/Month)/Total Orders {Total orders from General column}

Salary

Monthly Salary = Annual Salary/12

Salary Sub-total

With EDI = Personnel needed * Monthly Salary {from Manual column} + Technical Monthly Salary {from EDI column}

EDI

Hardware

Periods = Amortization/12

Cost/Month = Cost/Periods

Software

Periods = Amortization/12

Cost/Month = Cost/Periods

Monthly Salary

Technical = Technical Salary/12

Business = Business Salary/12

Total = Technical + Business

Monthly Costs

EDI Sub-total = Hardware cost/month + Software cost/month + Total Salary + SaaS Charges

Summary

Monthly Savings = No EDI Subtotal – with EDI Subtotal

As % of Sales = Monthly Savings/Total Order Sales {from Manual column}
Read 5881 times
Rate this item
(0 votes)

Visit other PMG Sites:

PMG360 is committed to protecting the privacy of the personal data we collect from our subscribers/agents/customers/exhibitors and sponsors. On May 25th, the European's GDPR policy will be enforced. Nothing is changing about your current settings or how your information is processed, however, we have made a few changes. We have updated our Privacy Policy and Cookie Policy to make it easier for you to understand what information we collect, how and why we collect it.