Question

Davis Dry Goods distributes silk ties. You are in charge of creating Davis master budget for the upcoming second quarter, April-June 2018.

Davis desires a minimum ending cash balance each month of $10,000. The ties are sold to retailers for $9 each. Recent and forecasted sales in units are as follows:

January (actual) . . . . . . . . . 21,000

February (actual) . . . . . . . . 25,000

March (actual) . . . . . . . . . . 27,000

April . . . . . . . . . . . . . . . . . . 32,000

May . . . . . . . . . . . . . . . . . . 45,000

June . . . . . . . . . . . . . . . . . . 65,000

July . . . . . . . . . . . . . . . . . . 43,000

August . . . . . . . . . . . . . . . 40,000

September . . . . . . . . . . . . . 36,000

The large build-up in sales before and during June is due to Father's Day. Ending inventories are supposed to equal 90% of the next month's sales in units. The ties cost the company $5 each.

Purchases are paid for as follows: 50% in the month of purchases and the remaining 50% in the following month. All sales are on credit, with no discount, and payable within 15 days. The company has found, however, that only 25% of a month's sales are collected by month-end. An additional 75% is collected in the following month. Bad debts have been negligible.

The company's monthly selling and administrative expenses are given below:

Variable:

Sales Commissions…...$1 per tie

Fixed:

Wages and Salaries....$25,200

Utilities………..$16,000

Insurance……….$1,300

Depreciation……….$1,500

Miscellaneous……….$3,600

All selling and administrative expenses are paid during the month, in cash with the exception of depreciation and insurance expired.

Davis has an agreement with a bank that allows it to borrow in increments of $1,000 at the beginning of each month. The interested rate on these loans is 1% per month, and for simplicity, we will assume that interest is not compounded. At the end of the quarter, the company would the bank all of the accumulated interest on the loan and as much of the loan as possible (in increments of $1,000), while still retaining at least $10,000 in cash.

Land will be purchased during May for $21,000 cash. Davis declares and pays dividends of $10,000 in the first month each quarter. The company's balance sheet at March 31 is given below:

                                Assets March 31st

Cash------------------------------------------------------------- $11,000

Accounts receivable------------------------------------------$182,250

Inventory (31,500 ties) ---------------------------------------$144,000

Prepaid insurance--------------------------------------------..$15,600

Fixed assets, net--------------------------------------------$217,100

Total Assets--------------------------------------------------569,950

                        Liabilities & Stockholders Equity

Accounts Payable-------------------------------------------$ 78,750

Capital Stock------------------------------------------------$310,000

Retained Earnings----------------------------------------$181,200

Total Liabilities and Stockholders Equity---------$569,950

Required:

Prepared a master budget for Davis Dry Goods for the quarter ending June 30, 2015. Include the following detailed budgets.

1. a. A sales budget by month and in total

   b. A schedule of expected cash collections from sales, by month, and in total.

   c. A merchandise purchases budget. Show the budget by month and in total

   d. A schedule of expected cash disbursements for merchandise purchases, by month and in

          total.

2. A cash budget by month, but not in total

3. A budgeted income statement for the three-month period ending June 30.

4. A budgeted balance sheet as of June 30.

Cash ending is 10050

Net income 281400

Total assets 887600

Answer 1

1) a) Sales Budget for the Quarter (Amounts in $)

	April	May	June	Total
Budgeted units sold (A)	32,000	45,000	65,000	142,000
Selling Price per unit (B)	$9	$9	$9	$9
Budgeted Sales (A*B)	288,000	405,000	585,000	1,278,000

b)  Schedule of Expected Cash Collections (Amounts in $)

	April	May	June	Total
Cash Collected for:
Accounts receivable (March)	182,250			182,250
April (25% in Apr and 75% in May)	72,000	216,000		288,000
May (25% in May and 75% in Jun)		101,250	303,750	405,000
June (25% in Jun and 75% in July)			146,250	146,250
Expected cash collections for sales	254,250	317,250	450,000	1,021,500

c) Merchandise Purchases budget

	April	May	June	Total
Budgeted units sold	32,000	45,000	65,000	142,000
Add: Desired Ending Inventory (90% of next month sales)	40,500	58,500	38,700	38,700
Total units required	72,500	103,500	103,700	180,700
Less: Beginning Inventory	31,500	40,500	58,500	31,500
Unit required to purchase (A)	41,000	63,000	45,200	149,200
Cost per unit (B)	$5	$5	$5	$5
Total Purchase Costs (A*B)	$205,000	$315,000	$226,000	$746,000

d) Expected Cash Disbursements for Purchases (Amounts in $)   

	April	May	June	Total
Cash disbursements for:
Accounts Payable (Mar)	78,750			78,750
April (50% in Apr and 50% in May)	102,500	102,500		205,000
May (50% in May and 50% in Jun)		157,500	157,500	315,000
June (50% in Jun and 50% in July)			113,000	113,000
Expected Cash Disbursements	181,250	260,000	270,500	711,750

2) Cash budget for the Quarter ending June 30 (Amounts in $)

	April	May	June
Beginning Cash Balance	11,000	10,200	10,650
Add: Expected cash collections	254,250	317,250	450,000
Total cash available (A)	265,250	327,450	460,650
Expected cash disbursements for:
Purchases	181,250	260,000	270,500
Sales Commission ($1 per tie sold)	32,000	45,000	65,000
Wages and Salaries	25,200	25,200	25,200
Utilities	16,000	16,000	16,000
Miscellaneous	3,600	3,600	3,600
Purchase of Land	0	21,000	0
Dividends paid	10,000	0	0
Total cash disbursements (B)	268,050	370,800	380,300
Excess(or Deficit) of Cash (C =A-B)	(2,800)	(43,350)	80,350
Financing:
Borrowings	13,000 (2,800 deficit+10,000 min. cash rounded to next 1,000)	54,000 (43,350 deficit+10,000 min. cash rounded to next 1,000)	0
Repayments	0	0	(67,000)
Interest	0	0	(1,470)(390+1,080)
Total Financing (D)	13,000	54,000	(68,470)
Ending Cash Balance (C+D)	10,200	10,650	11,880

Interest expense = (13,000*1%*3 months)+(54,000*1%*2 months)

= 390+1,080 = 1,470

3) Budgeted Income Statement for the Quarter Ending June 30 (Amounts in $)

Sales		1,278,000
Cost of Goods Sold:
Beginning Inventory	144,000
Purchases	746,000
Less: Ending Inventory (38,700*$5)	193,500
Cost of goods sold		(696,500)
Gross Profit (E)		581,500
Operating Expenses:
Sales commissions (32,000+45,000+65,000)	142,000
Wages and Salaries (25,200*3)	75,600
Utilities (16,000*3)	48,000
Miscellaneous (3,600*3)	10,800
Insurance (1,300*3 months)	3,900
Depreciation (1,500*3 months)	4,500
Interest Expense (390+1,080)	1,470
Total Operating Expenses (F)		286,270
Net Income		295,230

4) Budgeted Balance Sheet as on June 30 (Amounts in $)

Assets
Cash	11,880
Accounts Receivable (585,000*75%)	438,750
Inventory (38,700*$5)	193,500
Prepaid Insurance (15,600-3,900 expired during quarter)	11,700
Fixed Assets, net (217,100+21,000 purchase-4,500 dep)	233,600
Total Assets	889,430
Liabilities and Stockholders Equity
Accounts Payable (50% of June Purchases)	113,000
Capital Stock	310,000
Retained Earnings (181,200+295,230 Net income- 10,000 dividends)	466,430
Total Liabilities and Stockholders Equity	889,430