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}