Reference no: EM132238105
Using Microsoft Excel, construct a supply chain finance model to determine the redelivery/rehandling cost, lost sales, invoice deduction cost, and net income for the following two cases:
a. On-time delivery increases from 90 percent to 95 percent with a 5 percent increase in transportation cost.
b. Order fill rate decreases from 96 percent to 92 percent with inventory reduced by 5 percent.
Selling price/order = $150/order
Gross profit/order = $35/order
Lost sales rate:
On-time delivery failure = 15%
Order fill failure = 20%
Annual orders = 200,000
Rehandling cost = $125/order
Invoice deduction/service failure = $150/order
Transportation cost = $1,000,000
Average inventory = $1,000,000
Interest cost = $1,500,000
Inventory carrying cost rate = 25%/$/yr.
Warehousing cost = $750,000
Other operating cost = $500,000
Cash = $3,000,000
Accounts receivable = $4,000,000
Fixed assets = $30,000,000
Tax rate = 40%
Stockholder’s equity = $45,000,000