Question

AudioFile Products Ltd. is a retailer that sells sound systems. The company is planning its cash needs for the month of January, 2018. In the past, AudioFile has had to borrow money during the post-Christmas season to offset a significant decline in sales. The following information has been assembled to assist in preparing a cash flow forecast for January.

January 2018 Forecasted Income Statement

Sales		$200,000
Cost of goods sold		150,000
Gross profit		50,000
Variable selling expenses	$10,000
Fixed administrative expenses	20,000	30,000
Forecasted net operating income		$ 20,000

(1)	Sales are 10% for cash and 90% on credit.
(2)	Credit sales are collected over a three-month period with 40% collected in the month of sale, 30% in the following month, and 20% in the second month following sale. November 2017 sales totaled $300,000 and December 2017 sales totaled $500,000.
(3)	40% of a month’s inventory purchases are paid for in the same month. The remaining 60% are paid in the following month. Accounts payable relate solely to inventory purchases. At December 31, these totaled $400,000.
(4)	The company maintains its ending inventory levels at 60% of the cost of the merchandise to be sold in the following month. The merchandise inventory at December 31, 2017 was $90,000. February 2018 sales are budgeted at $150,000. Gross profit percentage is expected to remain unchanged.
(5)	The company pays a $10,000 monthly cash dividend to shareholders.
(6)	The cash balance at December 31, 2017 was $30,000; the company must maintain a cash balance of at least this amount at the end of each month.
(7)	The company can borrow on its operating loan in increments of $10,000 at the beginning of each month, up to a total loan balance of $500,000. The interest rate on this loan is 1% per month payable in the month following the loan. There is no operating loan at December 31, 2017.

Required:

Prepare a Cash Flow Forecast for AudioFile for the month of January 2018. Include appropriate supporting schedules. Refer to Problem 3-2 on page 127 of Selected Topics in Management Accounting keeping in mind that AudioFile has been in business for awhile and the operating budget worksheet is not required.

Answer 1

Cash Flow Forecast for January 2018
Beginning Cash balance	30000
Add: Collections from sales	310000
Cash available for disbursements	340000
Cash disbursements:
For purchases	451000
For variable selling expenses	10000
For fixed administrative expenses	20000
For dividend	10000
Total cash disbursements	491000
Cash surplus / (deficit)	-151000
Minimum cash balance	30000
Excess / (shortage)	-181000
Borrowing	190000
Ending Cash balance	39000

Working:

Sales Budget
Estimated Sales	200000
Cash Sales (10%)	20000
Credit Sales	180000
Schedule of collections:
Collections from
Cash sales	20000
Credit sales:
January sales (40%)	80000
December sales (30%)	150000
November sales (20%)	60000
Cash collections	310000
Cost of goods sold is 75% (150,000 / 200,000) of Sales
	January	February
Sales	200000	150000
Cost of goods sold (75%)	150000	112500
Add: 60% of February cost	67500
Total merchandise required	217500
Less: Beginning mechandise inventory	90000
Purchase of merchandise	127500
Payment for purchases:
December purchases	400000
January purchases (40%)	51000
Total payment for purchases	451000