abe X2 X Normal No Spacing Heading 1 Hooding 2 Using a spreadsheet computer software program, construct a supply chain finance model to determine the redelivery/rehandling cost, lost sales, invoice dedution cost, and net income for the following two cases: 1. On-time delivery increases from 90 percent to 95 percent with a 5 percent increase in transportation cost. 2. 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 Attach a table obtained from the spreadsheet and two figures separately for part a and part b. abe X2 X Normal No Spacing Heading 1 Hooding 2 Using a spreadsheet computer software program, construct a supply chain finance model to determine the redelivery/rehandling cost, lost sales, invoice dedution cost, and net income for the following two cases: 1. On-time delivery increases from 90 percent to 95 percent with a 5 percent increase in transportation cost. 2. 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 Attach a table obtained from the spreadsheet and two figures separately for part a and part b