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:
Click on the “Place Your Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
Fill in your paper’s requirements in the “PAPER DETAILS” section.
Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.
About AcademicWritersBay.com
AcademicWritersBay.com is an easy-to-use and reliable service that is ready to assist you with your papers 24/7/ 365days a year. 99% of our customers are happy with their papers. Our team is efficient and will always tackle your essay needs comprehensively assuring you of excellent results. Feel free to ask them anything concerning your essay demands or Order.
AcademicWritersBay.com is a private company that offers academic support and assistance to students at all levels. Our mission is to provide proficient and high quality academic services to our highly esteemed clients. AcademicWritersBay.com is equipped with competent and proficient writers to tackle all types of your academic needs, and provide you with excellent results. Most of our writers are holders of master’s degrees or PhDs, which is an surety of excellent results to our clients. We provide assistance to students all over the world. We provide high quality term papers, research papers, essays, proposals, theses and many others. At AcademicWritersBay.com, you can be sure of excellent grades in your assignments and final exams.
