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 HCSs are sold to retailers for $8 each and they are flying off the shelves. Recent forecasted sales in units are provided below:

The increased sales volume before and during June is due to Fathers Day with HCS being a favorite. Ending inventories are supposed to be equal to 90% of the next months 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 months 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	$12,000
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. HCSs balance sheet at the end of the first quarter is shown below:

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 showing Excel formula. Include the following budget schedules and financial statements:

A.) Merchandise purchases budget in units and in dollars. Show the budget by month and total.

B) Schedule of expected cash disbursements for merchandise purchases, by month and total.

January (actual) February (actual) March (actual) April May June July August September 60,000 40,000 36,000 32,000 24,000 28,000 35,000 45,000 Assets $14,000 216,000 Cash Accounts receivable ($48,000 February sales: S168,000 March sales Inventory (31,500 units) Prepaid insurance Fixed assets, net of depreciation Total Assets 157,500 14,400 172,700 74.600 Liabilities and Stockholders Equity Accounts payable Dividends payable Capital Stock Retained earnin Total Liabilities and Stockholders Equit $85,750 12,000 300,000 S574600 January (actual) February (actual) March (actual) April May June July August September 60,000 40,000 36,000 32,000 24,000 28,000 35,000 45,000 Assets $14,000 216,000 Cash Accounts receivable ($48,000 February sales: S168,000 March sales Inventory (31,500 units) Prepaid insurance Fixed assets, net of depreciation Total Assets 157,500 14,400 172,700 74.600 Liabilities and Stockholders Equity Accounts payable Dividends payable Capital Stock Retained earnin Total Liabilities and Stockholders Equit $85,750 12,000 300,000 S574600