ScholarMatic: Explanation & Answer
Your ready answer from a verified tutor is just a click away for as little as $14.99
Click Order Now to get 100% Original Answer Customized to your instructions!
Cash Flow Complete the statement of cash flow for January
through June on the CashFlow worksheet by completing the following
tasks. As part of the process you will need to use a circular
reference. You will need to set Excel to handle circular
references. Task Description Calculate the total cash inflows for
the six months. The total cash inflows are sum of the cash sales
and customer payments. Calculate the total expenses for the six
months. The total expenses are sum of all of the expenses incurred
for each the month in which they are incurred. Calculate the net
cash flows for the six months. The net cash flows are calculated as
the difference between the total cash inflows and the total
expenses for each month. Calculate the Shortfall/Surplus Cash for
the six months. The shortfall/surplus cash is the beginning cash
balance plus the net cash flows for each month. Calculate the
additional cash needed for the six months. When there is a cash
shortfall (the shortfall/surplus cash calculation is less than
zero), additional cash must be obtained from a loan to cover all of
the monthly expenses. Therefore, additional cash needed is equal to
any cash shortfall. It is zero if there is a cash surplus. The
additional cash needed should be a positive number even though a
shortfall will be a negative number in the model (row 26).
Calculate the cash available to retire debt for each of the six
months. There is cash available to retire debt if there is a cash
surplus at the end of a month (shortfall/surplus cash > 0).
Therefore cash available to retire debt is equal to any cash
surplus at the end of a month. If there is not a surplus, cash
available to retire debt is 0. Calculate the cash used to retire
debt for each of the six months. Since the company can’t pay down
more debt than there is cash available to retire the debt, the cash
used to retire debt cannot exceed the cash available to retire
debt. Likewise, the company won’t pay more money to retire debt
than it has debt from the prior month to retire. Therefore, the
cash used to retire debt will be equal to the cash available to
retire debt if that total is less than the loan balance from the
prior month. Otherwise, the cash used to retire debt will be the
loan total from the prior month. Calculate the loan balance for
each of the six months. The loan balance is calculated as the
additional cash needed for a given month minus any cash used to
retire debt for that month plus the loan balance carried from the
prior month. Calculate the ending cash balance for each month. The
ending cash balance is equal to the cash available to retire debt
minus any cash used to retire debt. Calculate the beginning cash
balance for February through June. The beginning cash balance is
equal to the ending cash balance from the prior month. Calculate
the loan interest for each month. The loan interest is equal to the
loan balance for that month times the monthly interest rate in cell
C3. This will create a circular reference. Configure Excel to
appropriately handle the circular reference. Calculate the line of
credit needed. The line of credit needed is the credit limit the
company needs to negotiate with the bank. It is calculated as the
maximum loan balance that the company expects to carry over the
course of the six month period.
| Cash Flow Budget Worksheet |
|||||||||||||||||||||||||||||||||||||||||||||||||
| Monthly Interest Rate | 1.5% | ||||||||||||||||||||||||||||||||||||||||||||||||
| Line of Credit Needed | |||||||||||||||||||||||||||||||||||||||||||||||||
| December | January | February | March | April | May | June | |||||||||||||||||||||||||||||||||||||||||||
| Beginning Cash Balance | $5,000.00 | ||||||||||||||||||||||||||||||||||||||||||||||||
| Cash Inflows (Income): | |||||||||||||||||||||||||||||||||||||||||||||||||
| Customer Payments | $35,000.00 | $45,000.00 | $53,000.00 | $64,680.00 | $76,540.00 | $90,540.00 | |||||||||||||||||||||||||||||||||||||||||||
| Cash Sales | $52,000.00 | $62,000.00 | $73,500.00 | $86,000.00 | $100,600.00 | $117,700.00 | |||||||||||||||||||||||||||||||||||||||||||
| Total Cash Inflows | |||||||||||||||||||||||||||||||||||||||||||||||||
| Cash Outflows (Expenses): | |||||||||||||||||||||||||||||||||||||||||||||||||
| Advertising | $20,000.00 | $30,000.00 | $35,000.00 | $40,000.00 | $50,000.00 | $60,000.00 | |||||||||||||||||||||||||||||||||||||||||||
| Loan Interest | |||||||||||||||||||||||||||||||||||||||||||||||||
| Insurance | $4,500.00 | $4,500.00 | $4,500.00 | $4,500.00 | $4,500.00 | $4,500.00 | |||||||||||||||||||||||||||||||||||||||||||
| Inventory Purchases | $46,000.00 | $53,000.00 | $54,500.00 | $59,800.00 | $71,900.00 | $83,300.00 | |||||||||||||||||||||||||||||||||||||||||||
| Office Supplies | $3,200.00 | $3,200.00 | $3,200.00 | $3,500.00 | $4,000.00 | $4,000.00 | |||||||||||||||||||||||||||||||||||||||||||
| Payroll | $11,000.00 | $12,000.00 | $13,500.00 | $18,100.00 | $17,700.00 | $23,200.00 | |||||||||||||||||||||||||||||||||||||||||||
| Rent or Lease | $12,000.00 | $12,000.00 | $12,000.00 | $12,000.00 | $12,000.00 | $12,000.00 | |||||||||||||||||||||||||||||||||||||||||||
| Utilities & Telephone | $2,700.00 | $3,000.00 | $3,100.00 | $3,200.00 | $3,500.00 | $5,000.00 | |||||||||||||||||||||||||||||||||||||||||||
| Total Expenses | |||||||||||||||||||||||||||||||||||||||||||||||||
| Cash Flows (Net): | |||||||||||||||||||||||||||||||||||||||||||||||||
| Shortfall/Surplus Cash | $6000.00* | ||||||||||||||||||||||||||||||||||||||||||||||||
| Loan Calculatations | |||||||||||||||||||||||||||||||||||||||||||||||||
| Additional Cash Needed | $0.00 | ||||||||||||||||||||||||||||||||||||||||||||||||
| Cash Available to Retire Debt | $6,000.00 | ||||||||||||||||||||||||||||||||||||||||||||||||
| Cash Used to Retire Debt | $1,000.00 | ||||||||||||||||||||||||||||||||||||||||||||||||
| Loan Balance** | $0.00 | ||||||||||||||||||||||||||||||||||||||||||||||||
| Ending Cash Balance | $5,000.00 | ||||||||||||||||||||||||||||||||||||||||||||||||
| * Note: there was a loan balance of $1000 in November which was paid to $0 with the cash surplus in December. This is why the beginning cash value in January is $1000 less than the ending cash in December. |
|||||||||||||||||||||||||||||||||||||||||||||||||
|
** A loan taken out in any given month cannot be paid off in
|
|||||||||||||||||||||||||||||||||||||||||||||||||
ScholarMatic: Explanation & Answer
Your ready answer from a verified tutor is just a click away for as little as $14.99
Click Order Now to get 100% Original Answer Customized to your instructions!
