Please fill out the spreadsheet, all the inputs are given. checks dont need to be filled out, but should equal 0
| Checks | Y0 | Y1 | Y2 | Y3 | Y4 | Y5 | Y6 | Y7 | |
| C1: NI- Dividends - Change in RE | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| C2: Net cash flow - Change in cash | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| C3: Assets - Liabilities -Equities | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| Inputs | Y0 | Y1 | Y2 | Y3 | Y4 | Y5 | Y6 | Y7 | |
| Ceiling on the level of paid-in capital available | 40.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| Floor on the level of EQUITY as % of level of loans [assets] outstanding | 6.00% | 6.00% | 6.00% | 6.00% | 6.00% | 6.00% | 6.00% | 6.00% | |
| Ceiling on the level of possible deposits [liabilities] and its growth | 1000.00 | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | |
| Floor on the level of cash as % of the level of deposits | 2.00% | 2.00% | 2.00% | 2.00% | 2.00% | 2.00% | 2.00% | 2.00% | |
| Ceiling on level of loans outstanding and their growth | 750.00 | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | |
| Interest income as % of beginning balance of loans outstanding | 7.00% | 7.00% | 7.00% | 7.00% | 7.00% | 7.00% | 7.00% | 7.00% | |
| Lending administration expense as % of beginning balance of loans outstanding | 0.35% | 0.35% | 0.35% | 0.35% | 0.35% | 0.35% | 0.35% | 0.35% | |
| Interest expense as % of beginning balance of deposits outstanding | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | |
| Retail branch expense as % of beginning balance of deposits outstanding | 0.25% | 0.25% | 0.25% | 0.25% | 0.25% | 0.25% | 0.25% | 0.25% | |
| Interest yield on beginning balance of cash | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | |
| Tax rate | 25.00% | 25.00% | 25.00% | 25.00% | 25.00% | 25.00% | 25.00% | 25.00% | |
| Dividends as % of net income | 60.00% | 60.00% | 60.00% | 60.00% | 60.00% | 60.00% | 60.00% | 60.00% | |
| Cost of equity | 10.00% | ||||||||
| Income Statement | Y0 | Y1 | Y2 | Y3 | Y4 | Y5 | Y6 | Y7 | |
| Interest income from loans | |||||||||
| Interest income from cash | |||||||||
| Interest expense for deposits | |||||||||
| Lending administration expense | |||||||||
| Retail branch expense | |||||||||
| = Earnings before tax | |||||||||
| Total tax expense | |||||||||
| = Net income | |||||||||
| Ending Balance Sheet | Y0 | Y1 | Y2 | Y3 | Y4 | Y5 | Y6 | Y7 | |
| Cash | |||||||||
| Loans made [A] | |||||||||
| Deposits taken [L] | |||||||||
| Paid-in capital | |||||||||
| Retained earnings | |||||||||
| Cash Flow Statement | Y0 | Y1 | Y2 | Y3 | Y4 | Y5 | Y6 | Y7 | |
| Net income | |||||||||
| = Operating cash flow | |||||||||
| (Change) in loans | |||||||||
| = Investing cash flow | |||||||||
| Change in deposits | |||||||||
| Capital contribution | |||||||||
| (Capital distribution) | |||||||||
| Dividends (paid) | |||||||||
| = Financing cash flow | |||||||||
| = Net cash flow | |||||||||
| Cash | Y0 | Y1 | Y2 | Y3 | Y4 | Y5 | Y6 | Y7 | |
| Beginning balance | |||||||||
| + Net cash flows | |||||||||
| = Ending balance | |||||||||
| Loans | Y0 | Y1 | Y2 | Y3 | Y4 | Y5 | Y6 | Y7 | |
| Beginning balance | |||||||||
| + Loans borrowed (repaid) | |||||||||
| = Ending balance | |||||||||
| B + Net = E | |||||||||
| Net = E - B | |||||||||
| Deposits | Y0 | Y1 | Y2 | Y3 | Y4 | Y5 | Y6 | Y7 | |
| Beginning balance | |||||||||
| + Deposits taken (repaid) | |||||||||
| = Ending balance | |||||||||
| B + Net = E | |||||||||
| Net = E - B | |||||||||
| Equity computations | Y0 | Y1 | Y2 | Y3 | Y4 | Y5 | Y6 | Y7 | |
| Beginning balance | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| + Contributions | |||||||||
| + Net income | |||||||||
| - Dividends | |||||||||
| = Equity available before capital distribution | |||||||||
| Less: Equity needed to support loans | |||||||||
| = Capital distributions to shareholders | |||||||||
| Equity after capital distribution | |||||||||
| Paid-in capital | Y0 | Y1 | Y2 | Y3 | Y4 | Y5 | Y6 | Y7 | |
| Beginning balance | |||||||||
| + Capital contributions | |||||||||
| - Capital distributions | |||||||||
| = Ending balance | |||||||||
| Retained earnings | Y0 | Y1 | Y2 | Y3 | Y4 | Y5 | Y6 | Y7 | |
| Beginning balance | |||||||||
| + Net income | |||||||||
| - Dividends | |||||||||
| = Ending balance | |||||||||
| Loan Computations | Y0 | Y1 | Y2 | Y3 | Y4 | Y5 | Y6 | Y7 | |
| Supply side constraints | |||||||||
| Loan ceiling 1: Level of loans based on loan to equity ratio | |||||||||
| Loan celling: Equity available before capital distribution / Floor on equity-to-loan ratio | |||||||||
| Loan ceiling 2: Equity + level of deposits available - required liquidity | |||||||||
| Equity available before capital distribution | |||||||||
| Ceiling on balance sheet level of deposits available from depositors | 1000.00 | ||||||||
| = Total balance sheet level of funding available | |||||||||
| Less: Level of cash needed for liquidity if all deposits are accepted | |||||||||
| Upper limit on loans based on funding | |||||||||
| Demand side constraint | |||||||||
| Loan ceiling 3: Demand for loans by borrowers | |||||||||
| Upper limit on loans based on demand | 750.00 | ||||||||
| Lowest of the three ceilings on loans | |||||||||
| Level of loans made based on all three constraints | |||||||||
| Deposit Computations | Y0 | Y1 | Y2 | Y3 | Y4 | Y5 | Y6 | Y7 | |
| Level of loans made based on all three constraints | |||||||||
| Less: Level of equity required to support these loans | |||||||||
| = Deposits needed net of cash needed for liquidity | |||||||||
| The computations below avoid the need for iterations | |||||||||
| Deposits for lending = Gross - level of cash = Gross - Gross * % cash | |||||||||
| Gross = Deposits for lending/(1- % cash needed) | |||||||||
| Gross level of deposits |
We need at least 10 more requests to produce the answer.
0 / 10 have requested this problem solution
The more requests, the faster the answer.