You are a relatively recent hire to the Hartz & Co., a local manufacturer of plumbing supply products. You have been asked to prepare for a presentation to the company’s management a condensed cash-flow statement for the months of November and December, 2013.
The cash balance at November 1st was $51,000. It is the company’s policy to maintain a minimum cash balance of $51,000 at the end of each month. Cash receipts (from cash sales and collection of accounts receivable) are projected to be $563,700 for November and $455,000 for December. Cash disbursements (sales commissions, advertising, delivery expense, wages, utilities, etc.), prior to financing activity, are scheduled to be $554,000 in November and $500,700 in December.
Borrowing, when needed, is done at the beginning of the month – in increments of $1,000. The annual interest rate on any such loans is estimated to be 13.00%. Interest on any outstanding loans is paid in cash at the end of the month. Interest on any outstanding loans is paid in cash at the end of the month. Repayments of principal (if any, in whole dollars) are assumed to occur at the end of the month. As of November 1st, the company has a $51,000 short-term loan from the local bank.
RequiredUse the preceding information to prepare the cash budget for November and December. (Hint: The December 31st cash balance should be $51,483.)
Data Input
Cash balance, November 1st
$51,000
Minimum required cash balance
$51,000
Budgeted cash receipts:
November
$563,700
December
$455,000
Budgeted cash disbursements:
November
$554,000
December
$500,700
Interest rate on borrowings
13.0%
per year
Short-term loan payable, as of November 1st
$51,000
Borrowings in increments of
$1,000
Solution
Select from dropdown
Select from dropdown
November
December
Cash balance, beginning
Formula
Formula
Plus: Cash receipts
Formula
Formula
Total Cash Available
Formula
Formula
Cash disbursements, prior to financing
Formula
Formula
Plus: Minimum cash balance (given)
Formula
Formula
Total Cash Needed
Formula
Formula
Excess (deficiency of) cash, before financing effects
Formula
Formula
Financing:
Balance Short term loan beginning of month
Formula
Formula
Short-term borrowing, beginning of the month*
Formula
Formula
Repayments (loan principal), end of the month
Formula
Formula
Balance Short term loan end of month
Formula
Formula
Interest (@13.00%), paid in cash @ end of the month
Formula
Formula
Total effects of financing
Formula
Formula
Ending cash balance
Formula
Formula
* Hint: the borrowing is at the beginning of the month
therefore your algebra needs the following:
what you borrow needs to include the interest to be paid
interest to be paid needs to be a function of the amount borrowed and any existing balance
therefore a circular reference problem
so, use High School algebra
amount to borrow = AM
amount of interest = I
formulate two linear equations
AM = f()
→
AM =
-I35+I
=
-I35+(H38+AM)*H18/12
IF(I35-H38*H18/12-(I29-I33)*H18/12<0, Borrow, Not Borrow)
I = f()
→
I =
(H38+I39)*H18/12
Borrow = Roundup in multiples of borrowing amounts
solve for AM and I
Not Borrow = 0
The repayment is at the end of the month
You need two IF statements:
IF (There is a loan balance , IF (Excess cash, Make a repayment , Do not make repayment) , Do not make repayment)
Make a repayment =MIN (Amount borrowed or The Amount of excess cash available)
Make the repayment in whole dollars – use INT
Test your model’s algebra by entering various amounts for Cash receipts:
e.g.
0
100,000
500,000
750,000
"If this is not the paper you were searching for, you can order your 100% plagiarism free, professional written paper now!"
https://topgradeprofessors.com/wp-content/uploads/2021/06/156383151285149120.png00adminhttps://topgradeprofessors.com/wp-content/uploads/2021/06/156383151285149120.pngadmin2022-10-09 14:53:452022-10-09 14:53:45You are a relatively recent hire to the hartz & co.
