Question

Case Study 2 Master Budgeting and Pro-Forma Financial Statements

You have just been assigned to a new manager who believes you have exceptional budgeting skills. Since you began your job last summer, you have been showing management your latest spreadsheets and how you use your new-found knowledge of Managerial Accounting to make sound business decisions. Your new manager is responsible for the nationwide distribution of designer handkerchief sets (HCS) and, through multiple franchise agreements, sales have grown very rapidly, and the timing is right for you to join her team and to show your skills. You have just been given responsibility for all planning and budgeting of the entire HCS division. Your first assignment is to prepare a master budget for the next three months, starting April 1, 2019. You accept this responsibility with enthusiasm and you are anxious to impress your new manager and the president of the company, who has a very high regard for you. To commence your new role, you have assembled the following pertinent information:

Note: The company desires a minimum ending cash balance each month on $10,000. The HCS’s are sold to retailers for $8 each and they are flying off the shelves. Recent forecasted sales in units are provided below:

January (actual)	20,000	June	60,000
February (actual)	24,000	July	40,000
March (actual)	28,000	August	36,000
April	35,000	September	32,000
May	45,000

The increased sales volume before and during June is due to Father’s Day with HCS being a favorite. Ending inventories are supposed to be equal to 90% of the next month’s sales in units. The cost of each HCS is $5.00.

Purchases are paid for in the following manner: 50% in the month of the purchase and the remaining 50% paid in the month following the purchase. All sales to the distributors are made on credit terms with no discount (for now), and payable within 15 days. The HCS division has determined that only 25% of a month’s sales are collected by the end of the month in which the sale occurred. An additional 50% is collected in the month following the sale, and the remaining 25% is collected in the second month following the sale. Bad debts have been negligible, supporting the credit terms as favorable.

Below is a display of the HCS division monthly selling and administrative expenses:

Variable:
Sales Commissions	$ 1 per HCS
Fixed:
Wages and Salaries	$22,000
Utilities	$14,000

Insurance	$1,2000
Depreciation	$1,500
Miscellaneous	$3,000

Selling and administrative expenses are all paid during the month, in cash, with the exception of depreciation (of course) and insurance is pre-paid for the duration of the policy. HCS will make a purchase of a parcel of land during the month of May for $25,000 cash. HCS contributes to the corporate dividend at a rate of $12,000 each quarter, payable in the first month of the following quarter. HCS’s balance sheet at the end of the first quarter is shown below:

Assets
Cash	$14,000
Accounts receivable ($48,000 February sales: $168,000 March sales)	216,000
Inventory (31,500 units)	157,500
Prepaid insurance	14,400
Fixed assets, net of depreciation	172,700
Total Assets	$574,600
Liabilities and Stockholders Equity
Accounts payable	$85,750
Dividends payable	12,000
Capital Stock	300,000
Retained earnings	176,850
Total Liabilities and Stockholders Equity	$574600

An agreement with Bank of the West allows HCS to borrow in increments of $1,000 at the beginning of each month, up to a total loan amount of $150,000. The interest rate on these loans is 1% per month (pretty high but convenient nonetheless) but the interest is not compounded, meaning this is simple interest only. At quarter end, HCS would pay Bank of the West all of the accumulated interest on the loan and as much of the balance of the loan as possible (in $1,000 increments) while retaining the minimum $10,000 cash balance.

Required:

Prepare a master budget for the three- months ending June 30, 2019. Include the following budget schedules and financial statements:

5) Cash Budget. Show the cash budget by month and in total.

Answers to previous questions to help with answering question # 5

Part 1 – Sales Budget by month and total for the quarter
Sales Budget
	April	May	June	Quarter End
Expected Unit Sales	35,000	45,000	60,000	140,000
Unit Selling Price	$8	$8	$8	$8
Budgeted Sales in dollars	$280,000	$360,000	$480,000	$1,120,000

Part 2 –Schedule of expected cash collections from sales, by month and total.

Schedule of Cash Collection
	April	May	June	Quarter
February Sales (24,000 Units x $8)	$48,000 (24,000*8*25% collected in second month following the sales)
March Sales (28,000 Units x $8)	$112,000 (28,000*$8*50% collected in the following month of sale)	$56,000 (28,000*8*25% collected in second month following the sales)
April Sales	$70,000 ($280,000*25% collected in sales month)	$140,000 ($280,000*50% collected in the following month of sale)	$70,000 ($280,000*25% collected in second month following the sales)
May Sales		$90,000 ($360,000*25% collected in sales month)	$180,000 ($360,000*50% collected in the following month of sale)
June Sales			$120,000 ($480,000*25% collected in sales month)
Total Cash Collections	$230,000	$286,000	$370,000	$886,000

Part 3 – Merchandise purchases budget in units and in dollars. Show the budget by month and total

Merchandise Purchase Budget
	April	May	June	Quarter Ending	July
Expected Unit Sales	35000	45000	60000		40000
Plus: Desired Ending Inventory (90% of Next Month's Sales Unit)	40500 (45,000*90%)	54000 (60,000*90%)	36000 (40,000*90%)
Total Needs	75500	99000	96000
Less: Estimated Beginning Inventory (Ending Inventory of Previous Month)	31500	40500	54000
Required Merchandise Purchases in Units	44000	58500	42000
Cost per unit	$5	$5	$5
Merchandise Purchase Budget in dollars	$220,000	$292,500	$210,000	$722,500

Part 4 - Schedule of expected cash disbursements for merchandise purchases, by month and total

	April	May	June	Quarter Ending
Schedule of Expected Cash Disbursements for Purchases
Accounts Payable March	$85,750
April Purchases (50% in April and 50% in May)	$110,000	$110,000
May Purchases (50% in April and 50% in May)		$146,250	$146,250
June Purchases (50% in April and 50% in May)			$105,000
Total Expected Cash Disbursements for Purchases	$195,750	$256,250	$251,250	$703,250

Answer 1

Cash Budget	April	May	June	Quarter End
Beginning cash balance	14000	10250	10000	14000
Collections from customers	230000	286000	370000	886000
Cash available	244000	296250	380000	900000
Less payments
For inventory purchases	195750	256250	251250	703250
Selling and administrative expenses	74000	84000	99000	257000
Purchase of land		25000		25000
Pay dividend	12000			12000
Total budgeted payments	281750	365250	350250	997250
Cash balance before borrow/repay	-37750	-69000	29750	-97250
Financing activity
Borrowing (repayment)	48000	79000	-16000	111000
Interest			-3020	-3020
Ending cash balance	$10250	$10000	$10730	$10730

Selling and Administrative expense budget	April	May	June	Quarter End
Budgeted Sales unit	35000	45000	60000	140000
Variable :
Sales Commission $1 per unit	35000	45000	60000	140000
Total Variable Selling and Administrative expense	35000	45000	60000	140000
Fixed :
Wages and Salaries	22000	22000	22000	66000
Utilities	14000	14000	14000	42000
Insurance	12000	12000	12000	36000
Depreciation	1500	1500	1500	4500
Miscellaneous	3000	3000	3000	9000
Total Fixed Expenses	52500	52500	52500	157500
Total Selling and Administrative expense	87500	97500	112500	297500
Less: Depreciation	1500	1500	1500	4500
Less: Insurance	12000	12000	12000	36000
Cash Disbursement for Selling and administrative expenses	$74000	$84000	$99000	$257000