Case Study 4: Annual Budget and Budgeted Financial Statements

This case study provides practice in preparing an annual budget for a company, including a cash budget and budgeted financial statements.

Facts

Lux Enterprises Inc. (Lux) is a wholesale distributor of earrings. At the start of its second quarter (Q2), Lux hires you to manage its operations. Lux has not prepared budgets in the past, but you decide to create a budget for Q2. Lux sells many styles of earrings, all at a selling price of $10 per pair. Actual unit sales for Q1 and budgeted unit sales for Q2 and Q3 are as follow:

Actual Unit Sales

Budgeted unit Sales

January

20,000

April

65,000

July

30,000

February

26,000

May

100,000

August

28,000

March

40,000

June

50,000

September

25,000

The concentration of sales in the spring is due to Mother's Day. All inventory sales are made on credit and bad debts are negligible. Lux collects 20% in the month of sale, 70% in the following month, and 10% in the second month following the sale. Lux's only cash collections are from inventory sales.

Lux maintains an ending inventory balance sufficient to supply 40% of the budgeted unit sales in the following month. Lux pays its suppliers $4 for a pair of earrings. All inventory purchases are paid 50% in the month of purchase and 50% in the following month.

Lux's monthly operating expenses, other than cost of goods sold, are as follows:

Monthly Expenses

Variable expenses:

Sales commissions

3% of sales

Fixed expenses:

Advertising expense

$200,000

Rent expense

$18,000

Salary expense

$106,000

Utility expense

$7,000

Insurance expense

$3,000

Depreciation expense

$14,000

All expenses, other than insurance and depreciation, are paid in cash in the month the expense is incurred. Lux pays its annual insurance premium of $36,000 on November 1. Lux plans to purchase for cash $26,000 of new equipment in May and $30,000 of new equipment in June. In March, Lux declared a $15,000 dividend which is payable on April 15.

Lux's balance sheet on March 31 is as follows:

Assets

Cash

$224,000

Accounts receivable [a]

346,000

Inventory [b]

104,000

Prepaid insurance

21,000

PP&E, net of depreciation

950,000

Total assets

$1,645,000

Liabilities and Stockholders' Equity

Accounts payable [c]

$100,000

Dividends payable

15,000

Common stock

800,000

Retained earnings

730,000

Total liabilities and stockholders' equity

$1,645,000

[a]Includes $26,000 of February sales (10%), plus $320,000 of March sales (80%)

[b] 26,000 units (40% of budgeted sales in April) at $4 per unit

[c]50% of inventory purchases in March

Required

Master budget for the second quarterApril, May, and June. Include the following detailed schedules:

1.Sales budget, by month and in total

2.Merchandise purchases budget, by month and in total

3.Budgeted cash collections from inventory sales, by month and in total

4.Budgeted cash payments for inventory purchases, by month and in total

5.Comprehensive cash budget, by month and in total

6.Budgeted contribution margin income statement for three months ending June 30

7.Budgeted balance sheet as of June 30

8.Budgeted statement of cash flows for three months ending June 30

Templates, check figures and some hints to help you get started can be found on the following pages. You can perform the calculations by hand, or you can create the budget in Excel. In either case, you must use the format indicated by the templates. Upload your submission to Canvas by the due date indicated in the course schedule.

1. Sales Budget, Q2

April

May

June

Quarter

Budgeted unit sales

65,000

Selling price per unit

$10

Total sales revenue

$650,000

2. Merchandise Purchases Budget, Q2

April

May

June

Quarter

Budgeted unit sales

65,000

+ Desired ending inventory

40,000

Total units needed

105,000

- Beginning inventory

26,000

Required purchases

79,000

Per-unit cost

$4

Merchandise purchases

$316,000

3. Budgeted Cash Collections from Inventory Sales, Q2

April

May

June

Quarter

February sales(10%, 0%, 0%)

$ 26,000

March sales(70%, 10%, 0%)

280,000

April sales(20%, 70%, 10%)

130,000

May sales(0%, 20%, 70%)

0

June sales(0%, 0%, 20%)

0

Total cash collections

$436,000

4. Budgeted Cash Payments for Inventory Purchases, Q2

April

May

June

Quarter

Accounts payable, April 1

(100%, 0%, 0%)

$100,000

April purchases(50%, 50%, 0%)

158,000

May purchases(0%, 50%, 50%)

0

June purchases(0%, 0%, 50%)

0

Total cash payments

$258,000

5. Comprehensive Cash Budget, Q2

April

May

June

Quarter

Beginning cash balance

$ 224,000

+ Collections from customers

436,000

- Cash payments

Inventory purchases

(258,000)

Advertising expense

(200,000)

Rent expense

(18,000)

Salary expense

(106,000)

Utility expense

(7,000)

Sales commissions

(19,500)

Purchases of PP&E

0

Dividends paid

(15,000)

Ending cash balance

$ 36,500

6. Budgeted Income Statement, Q2 (3 months)

Sales

?

Variable costs

Cost of goods sold

?

Sales commissions

?

Contribution margin

?

Fixed costs

Advertising expense

?

Rent expense

?

Salary expense

?

Utility expense

?

Insurance expense

?

Depreciation expense

?

Net income

?

7. Budgeted Balance Sheet, June 30

Assets

Cash

Accounts receivable

Inventory

Prepaid insurance

PP&E, net of depreciation

Total assets

Liabilities and Stockholders' Equity

Accounts payable

Common stock

Retained earnings

Total liabilities and stockholders' equity

8. Budgeted Statement of Cash Flows, Q2

Cash provided by operating activities

Net income

?

+ Depreciation

?

- Increase in accounts receivable

?

+ Decrease in inventory

?

+ Decrease in prepaid insurance

?

- Decrease in accounts payable

?

Total

?

Cash used in investing activities

Equipment purchases

?

Cash used in financing activities

Dividends paid

?

Increase in cash

?

Check figures:

Budgeted sales revenue in Q2 is $2,150,000

Budgeted inventory purchases in Q2 are $804,000

Budgeted cash collections in Q2 are $1,996,000

Budgeted cash payments for inventory purchases in Q2 are $820,000

Budgeted cash balance on June 30 is $271,500

Budgeted net income in Q2 is $181,500

Budgeted total assets on June 30 is $1,795,500

Cash provided by operating activities in Q2 is $118,500