Question

1: Prepare a sales budget, including a schedule of expected cash collections.

2: prepare a merchandise purchase budget, including a schedule of expected cash disbursements for merchandise, and a selling and administrative budget.

3: prepare a cash budget.

MOST LIKELY NUMBERS AND ASSUMPTIONS SALES MANAGER PRIVATE INFORMATION October Most likely sales 600,000 910,000 475,000 385,000 PURCHASING MANAGER PRIVATE INFORMATION Most likely cost of merchandise as a % of sales Desired ending inventory as a percentage of next month's cost of sales 45% 20% OPERATION MANAGER PRIVATE INFORMATION Most likely shipping expenses as a percent of sales Most likely other expenses as a percent of sales Salaries and wages (per month) Most likely advertising costs (per month) Most likely insurance costs (per month Depreciation expense (per month) 5.0% 8.0% 85,000 50,000 3,000 25,000 FINANCE MANAGER PRIVATE INFORMATION Percent sales collected in month of sale Percent sales collected in month after sale 30% 70% Percent of inventory purchases paid in month of purchase Percent of inventory purchases paid in month after purchase 50% 50% Percent of operating expenses paid in month of purchase Percent of operating expenses paid in month after purchase 100% 0% Desired minimum ending cash balance each month Borrow in increments of Monthly interest rate on borrowings (not 35,000 1,000 1% compounded) Other planned outlays of cash Capital expenditures 90 50,000 S

Answer 1

Schedule of cash collections:
	July	August	Sept	Quarter
Collection:
June	0			0
July	180000 (30%)	420000 (70%)		600000
Aug		273000 (30%)	637000 (70%)	910000
Sept			142500 (30%)	142500
Total exp. Collections	180000	693000	779500	1652500
Total Sales for the Quarter = $1985000
A/R at the end of the Quarter (Sept sales still not collected) = 475000*0.7=$332500
Inventory Purchase budget:
	July	August	Sept	Quarter
Sales	600000	910000	475000
Cost of Merchandise as % of Sales	45%	45%	45%
Following Month's COGS	409500	213750	173250
Desired Ending Inventory %	20%	20%	20%
Desired Ending Inventory Dollars	81900	42750	34650
Budgeted Cost of Merchandise Sold	270000	409500	213750
Plus Desired Ending Inventory	81900	42750	34650
Total Inventory Needs	351900	452250	248400
Less Beginning Inventory	0	81900	42750
Required Purchases	351900	370350	205650	927900
Schedule of Expected Cash Disbursements - Purchases
	July	August	Sept	Quarter
For Purchases:
June	0			0
July	175950	175950		351900
Aug		185175	185175	370350
Sept			102825	102825
Total Disbursements	175950	361125	288000	825075
Cost of Merchandise Sold for the Quarter = 1985000 * 45% = $893250
Ending Inventory at the end of the Quarter = Ending of Sept = $34650
Ending A/P at the end of the Quarter = 50% of Sept purchases = 205650/2 = $102825
Schedule of Selling and Administrative Budget:
	July	August	Sept	Quarter
Sales	600000	910000	475000
Shipping as a Percentage of Sales	5%	5%	5%
Other Expenses as a Percentage of Sales	8%	8%	8%
Variable Expenses:
Shipping	30000	45500	23750	99250
Other Expenses	48000	72800	38000	158800
Total Variable Expenses	78000	118300	61750	258050
Fixed Expenses:
Salaries and Wages	85000	85000	85000	255000
Advertising	50000	50000	50000	150000
Prepaid Insurance	3000	3000	3000	9000
Depreciation	25000	25000	25000	75000
Total Fixed Expenses	163000	163000	163000	489000
Total Selling and Admin Expenses	241000	281300	224750	747050
Less: Non cash items (Dep. & Prepaid Ins.)	28000	28000	28000	84000
Total Cash Disbursements	213000	253300	196750	663050
Prepaid Insurance that is expensed during the quarter = 3000*3 = $9000
Depreciation Expense recognized during the Quarter = 25000 * 3 = $75000
CASH BUDGET :
	July	August	Sept	Quarter
Cash Balance Beginning	0	35000	35000	0
Add: Cash Collections	180000	693000	779500	1652500
Total Cash Available	180000	728000	814500	1652500
Less: Cash Disbursements:
For Inventory	175950	361125	288000	825075
For Operating Expenses	213000	253300	196750	663050
For Equipment	200000	90000	0	290000
For Cash Dividends	50000	0	0	50000
For Interest	0	411.63	421.49	833.12	(493950*1%*1/12)
Total Cash Disbursements	638950	704836.625	485171.49	1828958.1
Excess (Deficiency) of Cash	-458950	23163.375	329328.51	-176458.12
Financing:
Borrowing	493950	11836.625	0	505786.63
Repayment	0	0	294328.51	294328.51
Total Financing	493950	11836.625	294328.51	211458.12
Cash Balance Ending	35000	35000	35000	35000
Outstanding loan balance = $505786.63 - 294328.51 = 211458.12
Outstanding interest on borrowing (due on the following quarter) = (505786.63*1%*1/12 = $421.49
(the repayment is being made at the end of sept after earning declaration)

Answer 2