The following data were recently collected by the cash manager at RAYCO Department stores, Inc.

Day                                                 Dolar Deposits

Monday                                           $1,500

Tuesday                                          $2,000

Wednesday                                    $1,000

Thursday                                         $10,000

Friday                                                $25,000

Saturday                                            $35,000

Sunday                                              $10,000

These daily deposits are typical of RAYCO's ten regional stores. RAYCO is headquartered in Oklahoma city and concentrates cash from the ten local deposit banks to its concentration bank in Oklahoma city. You may assume that RAYCO's opportunity investment rate is 10 percent, the ECR is 8 percent and the reserve requirement is 12 percent. Further assume that RAYCO uses EDT's to concentrate the cash balances and that the EDT's is initiated on the day of deposit and clears the next day. Deposits are made before the daily ledger cut off time. Also half of each deposit is cash and half is in checks with one day availability. Finally assume that no bank clearing activity is conducted on the weekends and the beginning ledger and collected balance for the first week are equal to zero.

Create or write the formulas in the spreadsheet and show your work at arriving at your solutions

a) Assuming that the deposit schedule is typical of each week, create two weeks of deposits and transfer clearing activity .

b) Calculate the average collected balance at the typical deposit bank.

c) Assuming that each EDT costs $1.00, calculate the dollar amount of collected balances required to compensate the deposit bank

d) What is the total cost of the cash concentration system for one bank for one week?

e) Can you suggest any improvements in RAYCO's cash concentration system?