Westport Furniture Company

Module 10

Demonstration Problem 1

Westport Furniture Company

Westport Furniture Company produces tables. The company estimates that it will sell 10,000 tables at $50 each in the first quarter of the year 2001. The company’s beginning inventory has 1,000 tables and the desired ending inventory for the quarter is 1,500 tables.

Each table requires 8 pounds of wood, which costs $2.00 per pound. The beginning inventory of wood is 2,000 pounds, and the company wants to have 3,000 pounds in inventory at the end of the quarter. Each table requires half an hour of direct labor, which is billed at $16 per hour. Manufacturing overhead is allocated on the basis of direct labor hours, and the allocation rate is $12 per hour. The company’s selling expenses are $4 per table, and the administrative expenses are $6,000 per month.

Prepare the following budgets: (a) sales budget, (b) production budget, (c) direct materials purchases budget, (d) direct labor budget, (e) manufacturing overhead budget, (f) selling & administrative expenses budget, (g) budgeted ending finished-goods inventory and cost of goods sold.

a. Sales budget:

First quarter of year 2001 sales = 10,000 tables x $50 per table

= $500,000

b. Production budget:

Beginning inventory + Units produced = Sales in units + Ending inventory.

Thus,

Units to produce = Sales in units + ending inventory units – beginning inventory units

= 10,000 + 1,500 – 1,000

= 10,500 tables.

c: Direct materials budget

Wood required per table = 4 pounds

Thus, wood required for 10,500 tables this quarter = 10,500 x 4 = 42,000 pounds.

Beginning inventory + Purchases = Materials used in production + Ending inventory

Thus,

Pounds of wood to purchase = Pounds of wood used in production + desired ending inventory of wood - Beginning inventory

= 42,000 + 3,000 – 2,000

= 43,000 pounds of wood

Cost of wood to purchase = 43,000 pounds x $2.00 = $86,000.

d. Direct labor budget

Number of tables to produce this quarter = 10,500

Hours required per table = 0.5 hours

Thus, direct labor required this quarter = 10,500 x 0.5 = 5,250 hours.

Direct labor budget = 5,250 hours x $16 per hour = $84,000.

e. Manufacturing overhead budget:

Overhead application rate = $12 per labor hour.

Direct labor hours required this quarter (calculated in “d” above) = 5,250 hours.

Thus, budgeted manufacturing overhead = 5,250 hours x $12 = $63,000.

f. Selling & administrative expenses budget:

Selling expenses = $4 per table

Number of units sold this quarter = 10,000 tables

Thus, selling expenses budget = 10,000 x $4 per table = $40,000.

Administrative expenses each quarter = $6,000 x 3 = $18,000 per quarter

Thus, total selling & administrative expenses budget = $58,000 ($40,000 + $18,000).

g. Ending finished-goods inventory budget:

Cost per unit = cost per unit of (direct material + direct labor + manufacturing overhead)

Each table requires:

8 pounds of wood at $2.00/pound = $16 of direct materials

0.5 hour of labor at $16/hour = $8 of direct labor

0.5 hour of applied manufacturing overhead at $12/hour = $6 of overhead

Thus, cost of ending inventory per unit = $16 + $8 + $6 = $30.

Total budgeted ending inventory = 1,500 tables x $30 per table = $45,000.

Budgeted cost of goods sold = budgeted tables sold x cost per table

= 10,000 x 30

= $300,000

Demonstration Problem 2

Warwick Company

Warwick Company purchases electronic toys from a foreign supplier at $6 each, and sells the toys to retailers in the Northeast region at $10 each. The company expects the following quarterly sales of the toy during the first four months of the year 2001:

Month / January / February / March / April
Sales (in units) / 5,000 / 6,000 / 7,000 / 7,500

The company’s policy is to have 40 percent of the number of toys expected to be sold in the following month as ending inventory in any given month. From past experience, the company expects to collect 30 percent of each month’s sales in the current month, with the remainder being collected in the following month. Warwick company pays for the merchandise bought in any given month during the following month. The beginning balances of cash, accounts receivable and accounts payable are as given below:

Cash balance / $30,000
Accounts receivable / $28,000
Accounts payable / $24,000

The salaries expense is $3,000 per month, other operating expenses are $1,000 each month; both salaries and other operating expenses are always paid on the last day of each month. The depreciation expense each month is $1,500

Required:

Prepare for each month (a) sales budget, (b) budgeted cash collection, (c) budgeted ending inventory in units, (d) budgeted purchases (in units), (e) budgeted cash payments for purchases, (f) budgeted total cash payments, and (g) cash budget.

Solution:

Budgeted sales revenue = $50,000 in January (5,000 units at $10 each)

= $60,000 in February, (6,000 units at $10 each), and

= $70,000 in March (7,000 units at $10 each).

Budgeted Cash Collection:

Collections in
January 2001 / February 2001 / March 2001
From December 2000 sales / $28,000
From January 2001 sales / $15,000 / $35,000
From February 2001 sales / $18,000 / $42,000
From March 2001 sales / $21,000
Total / $43,000 / $53,000 / $63,000

Note: Cash collected in January 2001 = Accounts receivable from December 2000 + 30 percent of sales in January 2001 = $28,000 + 0.3 x 5,000 x 10

Budgeted ending inventory schedule:

Desired Ending Inventory
(40 percent of next month’s sales in units)
January 2001 / February 2001 / March 2001
From February 2001 sales / 2,400
From March 2001 sales / 2,800
From April 2001 sales / 3,000

Note: January 2001 ending inventory = 40 percent of February 2001 sales

= 0.4 x 6,000

= 2,400 units

Similarly, February and March budgeted ending inventories are calculated.

Budgeted Purchases:

Beginning inventory units + Units Puchased = Sales units + Ending inventory units

Thus,

Units to purchase each month = Sales + Desired ending inventory – Beginning inventory

Budgeted Purchases
(Sales + Desired ending inventory – Beginning inventory)
January 2001 / February 2001 / March 2001
Units / 5,400
(5,000 + 2,400 – 2,000) / 6,400
(6,000 + 2,800 – 2,400) / 7,700
(7,500 + 3,000 – 2,800)
Dollars / $32,400 (5,400 x 6) / $38,400 (6,400 x 6) / $46,200 (7,700 x 6)

Budgeted Payments for purchases:

Cash Payments in
January / February / March
From December 2000 purchases / $24,000
From January 2001 purchases / $32,400
From February 2001 purchases / $38,400

Budgeted total cash payments:

Payments for / January / February / March
Purchases / $24,000 / $32,400 / $38,400
Salaries / 3,000 / 3,000 / 3,000
Operating expenses / 1,000 / 1,000 / 1,000
Total / $28,000 / $36,400 / $42,400

Cash Budget:

January / February / March
Beginning cash balance / $30,000 / 45,000 / 61,600
Add cash collections / 43,000 / 53,000 / 63,000
Less cash payments / (28,000) / (36,400) / (42,400)
Ending cash balance / $45,000 / $61,600 / $82,200

Note: First, we calculate the cash balance for January. The ending cash balance for January becomes the beginning cash balance for February; similarly, and the ending cash balance for February becomes the beginning cash balance for March.

Practice Problem 1

Plymouth Company

Plymouth Company manufactures a single product, which it sells for $100 each. The company’s budget for the the period April through July of the year 2001 is as follows:

April / May / June / July
Sales in units / 30,000 / 40,000 / 35,000 / 50,000

Each unit of the product requires 6 pounds of raw material, which the company buys at $5.00 per per pound. The company wants to have in beginning inventory each month (a) 30 percent of a month’s expected sales of finished goods, and (b) 50 percent of the raw material required for production.

Each unit requires two hours of direct labor, which is billed at $14 per hour. Manufacturing overhead is allocated on the basis of direct labor hours, and the allocation rate is $12 per hour. The company’s selling expenses are $5 per unit, and the administrative expenses are $20,000 per month. The beginning inventory has 9,000 units of finished goods and 99,000 pounds of raw materials. The company wants to have 120,000 pounds of raw material at the end of June.

a. Sales budget

April / May / June
Sales in units / 30,000 / 40,000 / 35,000
Sales in dollars / $3,000,000 / $4,000,000 / $3,500,000

b. Production budget:

Beginning inventory of a given month = Ending inventory of previous month.

In any month,

Beginning inventory + Units produced = Sales in units + Ending inventory, or

Units to produce = Sales in units + ending inventory units – beginning inventory units

First, fill out the sales in units for each month. This gives the desired beginning inventory for each month, which is the same as the ending inventory of the previous month. Then, calculate the units to produce each month.

April / May / June / July
Beginning inventory (a) / 9,000 / 12,000 / 10,500
Sales in units (b) / 30,000 / 40,000 / 35,000 / 50,000
Desired ending inventory (c) / 12,000
(0.3*40,000) / 10,500
(0.3*35,000) / 15,000
(0.3*50,000)
Units to produce
(b + c – a) / 33,000 / 38,500 / 39,500

c: Direct materials budget

Raw materials per unit = 6 pounds

Beginning inventory + Purchases = Materials used in production + Ending inventory

Pounds of material to purchase = Pounds of raw material used in production + desired ending inventory of raw material - Beginning inventory

From “b” above, we have units to produce each month. From this, we first get the raw mateial used in production each month. We then get the beginning inventory of raw material required each month, which must be 50 percent of the raw material used in production each month (this also is the ending inventory of raw material for the previous month). We can then calculate the raw materials to purchase each month.

April / May / June
Beginning inventory of raw materials (a) / 99,000
(given) / 115,500
(0.5*231,000) / 118,500
(0.5*237,000)
Raw material used in production (b) / 198,000
(6*33,000) / 231,000
(6*38,500) / 237,000
(6*39,500)
Desired ending raw material inventory (c) / 115,500 / 118,500 / 120,000
(given)
Quantity of raw materials to purchase
(b + c – a) / 214,500 / 234,000 / 238,500
Cost of raw materials to purchase / $1,072,500
(5*214,500) / $1,170,000
(5*234,000) / $1,192,500
(5*238,500)

d. Direct labor budget

April / May / June
Units to produce / 33,000 / 38,500 / 39,500
Direct labor hours required (2 hours per unit) / 66,000 hours / 77,000 hours / 79,000 hours
Direct labor cost ($14 per hour) / $924,000 / $1,078,000 / $1,106,000

e. Manufacturing overhead budget:

April / May / June
Direct labor hours / 66,000 / 77,000 / 79,000
Overhead applied ($12 per hour) / $792,000 / $1,078,000 / $1,106,000

f. Selling & administrative expenses budget:

April / May / June
Sales in units / 30,000 / 40,000 / 35,000
Selling expenses ($5 per unit) / $150,000 / $200,000 / $175,000
Administrative expenses / $20,000 / $20,000 / $20,000
Selling & administrative expenses / $170,000 / $220,000 / $195,000

g. Budgeted ending inventory:

Cost per unit of inventory = raw materials + direct labor + manufacturing overhead

= (6 x 5) + (2 x 14) + (2 x 12)

= $82

April / May / June
Ending inventory in units / 12,000 / 10,500 / 15,000
Cost of ending inventory ($82 per unit) / $984,000 / $861,000 / $1,230,000
Sales in units / 30,000 / 40,000 / 35,000
Cost of goods sold ($82 per unit) / $2,460,000 / $3,280,000 / $2,870,000

h. Budgeted income statement:

April / May / June
Budgeted sales in dollars (a) / $3,000,000 / $4,000,000 / $3,500,000
Budgeted cost of goods sold (b) / $2,460,000 / $3,280,000 / $2,870,000
Budgeted gross margin (c = a – b) / $540,000 / $720,000 / $630,000
Budgeted selling & administrative expenses (d) / $170,000 / $220,000 / $195,000
Budgeted net income (e = c – d) / $370,000 / $500,000 / $435,000

Practice Problem 2

Almeida Company

Almeida Company, a merchandising firm, had sales revenue of $400,000 in December 2000, and expects the following sales during January to April of 2001:

January / February / March / April
Sales in units / $300,000 / $500,000 / $600,000 / $700,000

All sales are on credit, and from past experience the Company expects the following collection pattern for accounts receivable: 60 percent of in the month of sale, 38 percent in the following month, and 2 percent uncollectible.

The company’s gross margin is 40 percent of sales, and the company buys all of its inventory on credit. The company pays 75 percent of the cost of purchases in the month of purchase, and pays the remainder in the following month. The company’s policy is to have ending inventories equal to 30 percent of the amounts required for the following month’s sales.

The operating expenses are as follows: variable selling expenses, 10 percent of sales revenue; fixed selling expenses, $20,000 per month (which includes $4,000 per month of depreciation); fixed administrative expenses, $10,000 per month.