Question

Developing a Master Budget for a Merchandising Organization

Dils Brother Department Store prepares budgets quarterly. The following information is available for use in planning the second quarter budgets for 2017.

Dils Brother Department Store Balance Sheet March 31, 2017
Assets		Liabilities and Stockholders' Equity
Cash	$ 4,000	Accounts payable	$31,000
Accounts receivable	31,000	Dividends payable	15,000
Inventory	36,000	Rent payable	3,000
Prepaid Insurance	3,000	Stockholders' equity	50,000
Fixtures	25,000
Total assets	$99,000	Total liabilities and equity	$99,000

Actual and forecasted sales for selected months in 2017 are as follows:

Month	Sales Revenue
January	$ 70,000
February	60,000
March	50,000
April	60,000
May	70,000
June	80,000
July	100,000
August	90,000

Monthly operating expenses are as follows:

Wages and salaries	$ 27,000
Depreciation	100
Utilities	1,500
Rent	3,000

Cash dividends of $15,000 are declared during the third month of each quarter and are paid during the first month of the following quarter. Operating expenses, except insurance, rent, and depreciation are paid as incurred. Rent is paid during the following month. The prepaid insurance is for five more months. Cost of goods sold is equal to 50 percent of sales. Ending inventories are sufficient for 120 percent of the next month's cost of sales. Purchases during any given month are paid in full during the following month. All sales are on account, with 50 percent collected during the month of sale, 40 percent during the next month, and 10 percent during the month thereafter. Money can be borrowed and repaid in multiples of $1,000 at an interest rate of 12 percent per year. The company desires a minimum cash balance of $4,000 on the first of each month. At the time the principal is repaid, interest is paid on the portion of principal that is repaid. All borrowing is at the beginning of the month, and all repayment is at the end of the month. Money is never repaid at the end of the month it is borrowed.

(a) Prepare a purchases budget for each month of the second quarter ending June 30, 2017.

Dils Brothers Department Store Monthly Purchase Budget Quarter Ending June 30, 2017
	April	May	June	Total
Budgeted purchases

(b) Prepare a cash receipts schedule for each month of the second quarter ending June 30, 2017. Do not include borrowings.

Dils Brothers Department Store Schedule of Monthly Cash Receipts Quarter Ending June 30, 2017
	April	May	June	Total
Total cash receipts

(c) Prepare a cash disbursements schedule for each month of the second quarter ending June 30, 2017. Do not include repayments of borrowings.

Dils Brothers Department Store Schedule of Monthly Cash Disbursements Quarter Ending June 30, 2017
	April	May	June	Total
Total cash disbursements

(d) Prepare a cash budget for each month of the second quarter ending June 30, 2017. Include budgeted borrowings and repayments.

Only use negative signs, if needed, for: excess receipts over disbursements, balance before borrowings and cash balances (beginning and ending).

Dils Brothers Department Store Monthly Cash Budget Quarter Ending June 30, 2017
	April	May	June	Total
Cash balance, beginning
Receipts
Disbursements
Excess receipts over disb.
Balance before borrowings
Borrowings
Loan repayments
Cash balance, ending

(e) Prepare an income statement for each month of the second quarter ending June 30, 2017.

Only use negative signs to show net losses for income.

Dils Brothers Department Store Budgeted Monthly Income Statements Quarter Ending June 30, 2017
	April	May	June	Total
Sales
Cost of sales
Gross profit
Operating expenses:
Wages and salaries
Depreciation
Utilities
Rent
Insurance
Interest
Total expenses
Net income

(f) Prepare a budgeted balance sheet as of June 30, 2017.

Dils Brothers Department Store Budgeted Balance Sheet June 30, 2017
Assets		Liabilities and Equity
Cash		Merchandise payable
Accounts receivable		Dividend payable
Inventory		Rent payable
Prepaid insurance		Loans payable
Fixtures		Interest payable
Total assets		Stockholders' equity
		Total liab. & equity

Answer 1

Ans-(a)- Dils Brothers Department Store

Monthly Purchase Budget

Quarter Ending June 30,2017

	April	May	June	Total
Budgeted Cost of Goods Sold (50% of sales )	30,000 (60,000*50%)	35,000 (70,000*50%)	40,000 (80,000*50%)	105,000
Add: Desired Ending Inventory (120% of next month's Cost of Goods Sold)	42,000 (35,000*120%)	48,000 (40,000*120%)	60,000 (50,000*120%) (100,000*50%)	60,000
Total Needs	72,000	83,000	100,000	165,000
Less: Beginning Inventory	36,000	42,000	48,000	36,000
Budgeted Purchases	36,000	41,000	52,000	129,000

Ans-(b)- Dils Brothers Department Store

Schedule of Monthly Cash Receipts

Quarter Ending June 30,2017

	April	May	June	Total
From February Sales	6,000			6,000
From March Sales	20,000	5,000		25,000
From April Sales	30,000	24,000	6,000	60,000
From May Sales		35,000	28,000	63,000
From June Sales			40,000	40,000
Total Cash Receipts	56,000	64,000	74,000	194,000

Ans-(a)- Dils Brothers Department Store

Schedule of Monthly Cash Disbursements

Quarter Ending June 30,2017

	April	May	June	Total
For Inventory Purchase	31,000	36,000	41,000	108,000
Payment of Dividend	15,000			15,000
Payment of Wages and Salaries	27,000	27,000	27,000	81,000
Payment of Utilities	1,500	1,500	1,500	4,500
Rent Payment	3,000	3,000	3,000	3,000
Toatl Cash Disbursements	77,500	67,500	72,500	217,500

Ans-(d)- Dils Brothers Department Store

Store Monthly Cash Budget

Quarter Ending June 30,2017

	April	May	June	Total
Cash Balance, Beginning	4,000	4,500	4,000	4,000
Receipts	56,000	64,000	74,000	194,000
Total Cash Available	60,000	68,500	78,000	198,000
Disbursements	77,500	67,500	72,500	217,500
Excess Receipts over Disbursements	-17,500	1,000	5,500	-19,500
Borrowing	22,000	3,000		25,000
Cash Balance , Ending	4,500	4,000	5,500	5,500

Income Statement

	April	May	June	Total
Sales	60,000	70,000	80,000	210,000
Less: Cost of Goods Sold	30,000	35,000	40,000	105,000
Gross Profit	30,000	35,000	40,000	105,000
Less: Operating Expenses:-
Wages and Salaries	27,000	27,000	27,000	81,000
Depreciation	100	100	100	300
Utilities	1,500	1,500	1,500	4,500
Rent	3,000	3,000	3,000	9,000
Insurance	600	600	600	1,800
Interest	600	120		720
Total Expenses	32,800	32,320	32,200	97,320
Net Income	-2,800	2,680	7,800	7,680

Budgeted Balance Sheet

As at June 30, 2017

Assets		Liabilities and Equity
Cash	5,500	Merchandise Payable	52,000
Accounts Receivable	47,000	Dividend Payable	15,000
Inventory	60,000	Rent Payable	3,000
Prepaid Insurance (3,000-1,800)	1,200	Loans Payable	25,000
Fixtures (25,000-300)	24,700	Interest Payable	720
		Stockholders; equity (50,000-15,000+7,680)	42,680
Total Assets	138,400	Total Liabilities and Equity	138,400