Assignment - Preparing and analyzing a cash budget
Select assumptions for the following values that fall between the minimum and maximum indicated.
|
Assumption
|
Minimum
|
Maximum
|
|
a. Sales in month 1
|
$150,000
|
$250,000
|
|
b. Increase/decrease in sales month 2
|
-$5,000
|
$10,000
|
|
c. Increase/decrease in sales month 3
|
-$5,000
|
$10,000
|
|
d. Increase/decrease in sales month 4
|
$40,000
|
$60,000
|
|
e. Increase/decrease in sales month 5
|
$10,000
|
$30,000
|
|
f. Increase/decrease in sales month 6 (compared to month 3)
|
-$5,000
|
$10,000
|
|
g. Portion of sales that are cash sales or are collected in month of sale
|
25%
|
35%
|
|
h. Portion of sales for which receivables are collected in the first month following the month of sale
|
40%
|
50%
|
|
i. Fixed monthly expenses
|
$60,000
|
$70,000
|
|
j. Variable monthly expenses (percent of following month's sales)
|
60%
|
70%
|
|
k. Portion of variable expenses that are paid in cash or in month of purchase
|
35%
|
45%
|
|
l. Portion of variable expenses that are paid in the first month following the month of purchase
|
40%
|
50%
|
|
m. Interest payment in months 2 and 5
|
$20,000
|
$30,000
|
|
n. Dividend payment in months 3 and 6
|
$10,000
|
$20,000
|
|
o. Semi-annual insurance premium payment in month 4
|
$15,000
|
$35,000
|
Use the following assumptions, in addition to those selected above:
Total accounts receivable from sales prior to month 1 collected in month 1: $140000
Total accounts receivable from sales prior to month 1 collected in month 2: $50000
Total accounts payable from purchases prior to month 1 paid in month 1: $45000
Total accounts payable from purchases prior to month 1 paid in month 2: $11250
Expected sales in month 7: $200,000
Beginning cash balance for month 1: $5000
Assume also
1. all non-cash sales not collected in either the month of sale or the following month are collected in the second month after the sale,
2. all variable costs not paid in cash or in the month after purchase or the following month are paid during the second month after purchase.
Using the assumptions above, prepare a six-month cash budget for the company.
Your cash budget should include the following lines with appropriate calculation of amounts for each of the six months:
Sales
Cash sales
Accounts receivable collected from the previous month's sales
Accounts receivable collected from sales two months ago
Total cash inflow
Fixed expenses
Variable expenses
Variable expenses paid in month incurred
Accounts payable paid in month following purchase
Accounts payable paid in second month after purchase
Interest payments
Insurance payments
Dividend payments
Total cash outflow
Net cash flow for the month
Beginning cash balance
Ending cash balance
Explain how the information in the cash budget helps the company's financial manager in planning for the next six months.