Question

ABCD Corp prepares its master budget on a quarterly basis and has a September 30 year end. The following data and information has been prepared to help you prepare the master budget for the first quarter of the fiscal year (October thru December). Info provided as follows:

1.

Actual Sales for September, 2019 and projected sales for October, November and December, 2019 and January 2020 are as follows:

September $280,000

October $400,000

November $600,000

December $1,800,000

January $700,000

2.

Monthly sales are 32% on cash and 68% on credit. Customer payments on credit sales are collected as follows: 27% in the month of sale and 73% in the month following the month of sale.

3.

At September 30 ABCD Corp had outstanding accounts receivable of $190,400 and are a result of September credit sales. This amount will be collected as follows: 100% in October.

4.

ABCD Corp’s Gross Profit Percentage is 63%. Therefore Cost of Goods Sold comprise 37% of monthly sales.

5.

Monthly Expenses are budgeted as follows. All expense amounts are paid in the month they are incurred.

a. Sales and Wages: $31,000 per month

b. Advertising: $74,000 per month

c. Shipping: 7% of Sales

d. Miscellaneous Expenses: $10,000 + 6% of sales

6.

Depreciation Expense is expected to be $42,000 each quarter.

7.

ABCD’s Ending Inventory at September 30 is $44,400. Ending Inventory for October, November and December should equal 30% of the following month’s Cost of Goods Sold.

8.

60% of inventory purchases are paid for in the month of purchase; the remaining 40% paid for in the following month. At September 30 the Company still owed $72,000 to vendors for their September inventory purchases---this amount will be paid in October.

9.

During October, the company plans to purchase a new copy machine for $200,000 cash. During December, other equipment will be purchased for cash at a cost of $30,000.

10.

During November, the company will declare and pay $100,000 in cash dividends.

11.

ABCD’s Cash balance at September 30 is $40,000.

12.

Has an agreement with a local bank that allows them to borrow in increments of $1,000 at the beginning of each month. The interest rate on the on loans is 1% per month and for simplicity we’ll assume the loan is not compounded. The company would, as far as it is able, repay the loan plus accumulated interest at the end of each quarter to the extent it has available funds exceeded the $40,000 minimum balance amount.

Prepare the following Schedules for October, November and December.

a. INVENTORY PURCHASES CASH DISBURSEMENTS BUDGET

b. CASH (RECEIPTS AND DISBURSEMENTS) BUDGET

Answer 1

Part a)

Month	Sep	Oct	Nov	Dec	Jan
Sales (Given)	280,000	400000	600000	1800000	700000
COGS (Sales*37%)		148,000	222,000	666,000	259,000
Add: Closing Inventory (Next month COGS*30%)	44,000	66,600	199,800	77,700
Less: Opening Inventory		44,000	66,600	199,800
Purchases (COGS+Closing-Opening)		170,600	355,200	543,900
Cash Payment (Purchases*60%)		102,360	213,120	326,340
Balance payment in subsequent month		72,000	68,240	142,080
Total payment to vendors		174,360	281,360	468,420

Part b)

Month	Sep	Oct	Nov	Dec	Jan
Opening cash balance		40,000	480	840
Sales (Given)	280,000	400,000	600,000	1,800,000	700,000
Receipts:
Cash sales (Sales*32%)		128,000	192,000	576,000
Receipt from debtors:
In the month of sales (Sales*68%*27%)		73,440	110,160	330,480
In the following month of sales (Sales*68%*73%)		190,400	198,560	297,840
Loan from bank (Note 1)		110,000	74,000
Total Receipts		501,840	574,720	1,204,320
Payments:
Payment to vendors (Given)		174,360	281,360	468,420
Salaries & wages (Given)		31,000	31,000	31,000
Advertisement (Given)		74,000	74,000	74,000
Shipping (Sales*7%)		28,000	42,000	126,000
Miscellaneous expenses (10000+Sales*6%)		34,000	46,000	118,000
Purchase of assets (Given)		200,000		30,000
Dividend (Given)			100,000
Loan repayment along with interest (Note 2)				188,780
Total Payments		541,360	574,360	1,036,200
Closing cash balance		480	840	168,960

Note 1)

Amount to be borrowed in Oct month = cash payment-Opening cash balance-Cash receipt without loan amount = 541,360-40,000-391,840 = 109,520 = 110,000 (Multiples of 1,000 only)

Amount to be borrowed in Nov month = cash payment-Opening cash balance-Cash receipt without loan amount = 574,360-480-500,720 = 73,160 = 74,000 (Multiples of 1,000 only)

Note 2)

Loan Repayment = Principal amount+Interest = (110,000+74,000)+(110,000*1%*3months)+(74,000*1%*2month) = 184,000+3,300+1,480 = 188,780

Note 3)
Depreciation (Non-cash expenditure) is not required for cash budget. December cash balance is more than 40,000 hence loan along with interest repaid)