INSTRUCTIONS FOR EVALUATION OF A LOCKBOX SYSTEM: Global Manufacturing is considering using a lockbox system to speed up collections of accounts receivable from its customers in the new year. They have two Lockbox proposals to consider: one from U.S. Bank, and one from Well Fargo Bank. The information you will need to evaluate each bank's proposal is presented on this worksheet. HELP: Also see the file named "09-SAMPLE COMPANY LOCKBOX ANALYSIS OF ACCOUNTS RECEIVABLES.pdf" provided to help you setup your spreadsheet for these LOCKBOX calculations. Annual interest rate (T-Bill rate) = Average value of each payment = Average # of payments per day = Annual fixed charge = Lockbox fee per payment = Reduction in collection time (# days) = DATA INPUTS U. S. Bank Wells Fargo 13.54% 13.54% $4,215 $4,215 52 52 $8,500 $35,700 $0.41 2 $0.22 The questions that the CEO needs you to answer are as follows: ALWAYS USE CELL REFERENCES to the correct values on your worksheets to do your calculations. NOTE: Cells shaded in YELLOW are the cells you must provide the calculations or analysis. (a) What is the Net Present Value (NPV) of each of the new lockbox system proposals (including the annual fixed charge)? (Round to the nearest whole dollar) Similar to Practice Problem 27-11. Part b. U.S Bank = Wells Fargo = $314,312 $580,180 (b) What will be the annual net savings? Assume that the Annual interest rate (T-bill rate) is 13.54 percent annually. (Do not round intermediate calculations and enter your answer in dollars, not millions of dollars, rounded to the nearest whole number, e.g., 1,234,567.) Similar to Practice Problem 27-10. U.S Bank= Wells Fargo = $42,558 $78,556 (c) How many customers are needed, on average each day, to make each of the lockbox systems break-even? (Round to the nearest whole customer) Similar to Practice Problem 27-12. U.S Bank = Wells Fargo = (d) Which is the Preferred Lockbox System for Global Manufacturing, Inc.? U.S Bank or Wells Fargo 0 0 (e) What is the reduction in accounts receivable balance as a result of implementing the Preferred Lockbox System in 2024? (Round to the nearest whole dollar) U.S. Bank Wells Fargo Daily Interest Rate PV (Cash Inflow) 0.03480% 0.03480% S 438,360.00 $ 876,720.00 PV (Transaction cost) S 61,270.59 $ 32,876.90 NPV (without annual fixed charge) PV(Annual fixed charge) S 377,089.41 $ 843,843.10 $62,776.96 263,663.22 NPV (With annual fixed charge) $ 314,312 $ 580,180 = Reduction in collection time (Preferred System) x Average value of each payment x Average number of payments per day