Project: REG D Spring 2017
Problem 1:
At the close of business on Wednesday, Monroe National Bank is concerned if it met its legal reserve requirements. The following information is available:
Daily Average Balances during 2-week Reserve Computation period:
Gross Transaction accounts $7,675.000 million
Demand deposits due from U.S. depository institutions 387.000 million
Cash items in the process of collection 520.000 million
Vault cash 37.500 million
Requirement:
Use EXCEL to complete the following assignment. Follow examples in my powerpoint lecture as to how to set the project up in EXCEL. I expect you to link worksheets and to use the IF-THEN, MAX, and MIN functions in EXCEL.
Answer the following questions, showing all work. (Carry all numbers out 3 decimal places.)
1) What is the amount of Net transaction accounts?
2) What is the amount of the Gross reserve requirement?
3) What is the amount of the Reserve Balance Requirement?
4) What is the amount of the Top penalty-free band?
5) What is the amount of the Bottom penalty-free band?
For each of the 4 scenarios below, calculate the following:
a) The balance maintained to satisfy Reserve Balance Requirement.
b) The excess balance.
c) The reserve deficiency amount.
d) Amount if interest earned on DAB at Fed, if any. Assume IORR and IOER rates = 0.75% over the period.
e) Amount of penalty paid, if any. Assume penalty rate = 2.25% over period.
Scenario 1: Daily Average Balances during 2-week Reserve Maintenance period:
Reserves at the Fed $ 650.000 million
Scenario 2: Daily Average Balances during 2-week Reserve Maintenance period:
Reserves at the Fed $ 727.200 million
Scenario 3: Daily Average Balances during 2-week Reserve Maintenance period:
Reserves at the Fed $ 505.600 million
Scenario 4: Daily Average Balances during 2-week Reserve Maintenance period:
Reserves at the Fed $ 601.200 million
Problem 2:
At the close of business on Tuesday, Madison National Bank is concerned if it will meet its legal reserve requirements. The following information is available:
Daily Average Balances during 2-week Reserve Computation period from 12/6/16 – 12/19/16:
Gross Transaction accounts $1,732.000 million
Demand deposits due from U.S. depository institutions 100.000 million
Cash items in the process of collection 270.000 million
Vault cash 21.470 million
Daily Balances at Fed during 2-week Maintenance period from 1/5/17-1/18/17:
Requirement:
Use EXCEL to complete the following assignment. Follow examples in my powerpoint lecture as to how to set the project up in EXCEL. I expect you to link worksheets and to use GOAL SEEK or SOLVER.
Answer the following questions, showing all work. (Carry all numbers out 3 decimal places.)
1) What is the amount of Net transaction accounts?
2) What is the amount of the Gross reserve requirement?
3) What is the amount of the Reserve Balance Requirement?
4) What is the amount of the Top penalty-free band?
5) What is the amount of the Bottom penalty-free band?
6) a) Using SOLVER or GOAL SEEK find the January 18, 2017 minimum balance that Madison Bank must have in reserves at the Fed to avoid any penalties. Print a screen shot of your use of GOAL SEEK or SOLVER
b) Suppose Madison Bank desires to have a DAB at the Fed = $1 million in excess of the Reserve balance requirement. Using SOLVER or GOAL SEEK find the January 18, 2017 minimum balance that Madison Bank must have in reserves at the Fed to meet this criteria.
Other Instructions:
1. This project MUST be done in EXCEL.
2. Use “size to fit” when printing to conserve on paper.
3. When printing your solution, include row and column headings.
4. You do NOT need to print your formula sheet. I will look at it in your EXCEL workbook that you submit online via BlackBoard.
5. Highlight your answers. Make your answers easy to find.
6. Carry computations out 4 decimal places, unless otherwise specified. Final prices should be rounded to 2 decimal places.
7. Some useful functions in Excel to complete the project: IF-THEN, MAX, MIN, SUM, GOALSEEK, SOLVER
8. Link Cells so that if you make a change, other appropriate cells will change.
9. Be Careful. Sometimes numbers are given in millions, other times in thousands, etc.
10. Pint a screen shot of your GOALSEEK or SOLVER inputs and outputs.
11. Take pride in your work. Your name is on it.