STF&WCM: Excel Case Assignment #2 Account Analysis Statement - 2017 3 Account Analysis Case Given the information below for the Patterson Manufacturing Company's bank accounts in the month of September 2017, produce an Account Analysis Statement similar in format to the one in the text in Chapter 7. (Note: Please calculate Collected Balances required, rather than Investable balances - Your formulas will then match to the ones in the text.) Item - Services Performed Number Cost or Unit Price Account Maintenance $25.00 Debits - paper 3,589 $0.23 Debits - electronic 395 $0.20 Credits - electronic 5,829 $0.15 Addenda Records Processed 3,102 $0.45 Controlled Disbursements Items Paid 8,150 $0.12 Paid Item Reject 355 $1.00 Teller Cash Float 1,088 $0.55 Paid Item Images 8,099 $0.25 Wholesale Lockbox Monthly Maintenance 2 $100 Standard Items 9,875 $0.50 Deposit Tickets 20 $1.50 Data Capture 125.847 $0.005 Document Images $0.65 Wire Transfers Domestic Wires - Out 125 $15.00 Domestic Wires - In 12 $10.00 International Wires - Out 78 $25.00 International Wires - In 45 $15.00 Info Services -- Maintenance 1 $150.00 Info Services - Data Transfers 87,459 $0.002 In addition for the month in question, the company had an Average Ledger Balance of $8,653,976, with Deposit Float of $928,237. You may assume an ECR of 2.5% and Reserve Requirement of 10% Determine: The Collected Balance, the breakout of service charges for the month, the overall required balance and the required balance for each of the items above. Also compute the amount of either surplus earnings credits or deficit balance that must be charged to the account for the month in question, 984 Page 1 STF&WCM: Excel Case Assignment #2 Account Analysis Statement - 2017 3 Account Analysis Case Given the information below for the Patterson Manufacturing Company's bank accounts in the month of September 2017, produce an Account Analysis Statement similar in format to the one in the text in Chapter 7. (Note: Please calculate Collected Balances required, rather than Investable balances - Your formulas will then match to the ones in the text.) Item - Services Performed Number Cost or Unit Price Account Maintenance $25.00 Debits - paper 3,589 $0.23 Debits - electronic 395 $0.20 Credits - electronic 5,829 $0.15 Addenda Records Processed 3,102 $0.45 Controlled Disbursements Items Paid 8,150 $0.12 Paid Item Reject 355 $1.00 Teller Cash Float 1,088 $0.55 Paid Item Images 8,099 $0.25 Wholesale Lockbox Monthly Maintenance 2 $100 Standard Items 9,875 $0.50 Deposit Tickets 20 $1.50 Data Capture 125.847 $0.005 Document Images $0.65 Wire Transfers Domestic Wires - Out 125 $15.00 Domestic Wires - In 12 $10.00 International Wires - Out 78 $25.00 International Wires - In 45 $15.00 Info Services -- Maintenance 1 $150.00 Info Services - Data Transfers 87,459 $0.002 In addition for the month in question, the company had an Average Ledger Balance of $8,653,976, with Deposit Float of $928,237. You may assume an ECR of 2.5% and Reserve Requirement of 10% Determine: The Collected Balance, the breakout of service charges for the month, the overall required balance and the required balance for each of the items above. Also compute the amount of either surplus earnings credits or deficit balance that must be charged to the account for the month in question, 984 Page 1