Question

ABC Company manufactures and sells a single product. The following information is available concerning the operations for 1999.

a. The company's single product sells for $60 per unit. Budgeted sales in units for the next four quarters are:

1999    Quarter 1        3,000 Budgeted sales in units

1999    Quarter 2        3,500 Budgeted sales in units               

1999    Quarter 3        4,000 Budgeted sales in units

1999    Quarter 4        4,500 Budgeted sales in units

2000    Quarter 1        5,000 Budgeted sales in units

b. Sales are collected in the following pattern: 80% in the quarter in which the sale is made, 19% in the following quarter. On January 1, 1999, the company's balance sheet showed $60,000 in account receivables, all of which will be collected in the first quarter of the year 1999. Bad debts are projected at 1% of quarterly sales. There is a -0- balance in the AFDA account.

c. The company requires an ending inventory of finished units on hand at the end of each quarter equal to 20% of the budgeted sales for the next quarter. This requirement was met on December 31, 19x8. (The company had 600 units on hand to start the new-year).

d. Two pounds (2lbs) of raw materials are required to complete one unit of product. The company requires an ending inventory of raw materials on hand at the end of each quarter equal to 10% of the production needs of the following quarter. This requirement was met on December 31, 19x8. (The company had 620 lbs of raw materials on hand to start the new-year). Quarter 1 of the next year (year 2000) is estimated at 10,200 lbs needed for production.

e. The raw material costs $4.00 per pound. Purchases of raw material are paid for in the following pattern: 50% paid in the quarter in which the purchase was made, and the remaining 50% is paid in the following quarter. On January 1, 1999, the company's balance sheet showed $10,600 in accounts payable for raw material purchases. All of which will be paid for in the first quarter of the year 1999.

f. Manufacturing overhead and selling & administrative expenses are paid in the quarter incurred. The only exception is depreciation.

g. The manufacturing overhead budget distinguishes between variable and fixed overhead costs. Variable costs fluctuate with production volume on the basis of the following rates per direct labor hour: indirect materials $1.00, indirect labor $1.40, utilities $0.40, and maintenance $0.20. Fixed costs per quarter are: Supervisory Salaries $20,000, Depreciation $3,800, Property Taxes & Insurance $9,000 and Maintenance $5,700. Overhead is applied to production on the basis of direct labor hours. The annual rate is $8 per hour. (Hint: Total Manufacturing Overhead for 1999 $246,400 / Direct Labor hours 30,800 hours = $8/direct labor hour).

h. Selling & administrative expense budget distinguishes between variable and fixed overhead costs. Variable costs are Sales Commissions of $3.00 and Freight-Out $1.00. Variable expenses per quarter are based on the unit sales projected in the sales budget. Fixed costs, per quarter, are: Advertising $5,000, Sales Salaries $15,000, Office Salaries $7,500 Depreciation $1,000 and Property Taxes & Insurance $1,500.

i. January 1, 1999, cash balance is expected to be $38,000.

j. Marketable securities are expected to be sold for $2,000 cash in the first quarter.

k. 2 hours of direct labor are required to produce each unit of finished goods and the anticipated hourly wage rate is $10. Direct Labor is paid 100% in the quarter incurred.

l. Management plans to purchase new factory equipment in the second quarter for $50,000.

m. Management plans to purchase new office computers in the third quarter for $12,000.

n. Assume depreciation on new purchases is accounted for quarterly budgeted depreciation amounts.

o. Management plans to sell old equipment at the end of the fourth quarter for $3,000. Purchase price is $20,000, on January 1, 1996. Depreciation is calculated using the straight-line method, useful life estimated at five years, with no residual value.

p. The company makes equal quarterly payments of its estimated annual income taxes in the amount of $3,000 per quarter.

q. Loans are repaid in the first subsequent quarter in which there is sufficient cash (incuring 8% interest if funds are borrowed.)

r. A minimum cash balance of $20,000 is maintained per quarter.

s. Budgeted balance sheet information as of December 31, 1998 were: Building & Equipment $ 182,000, Common Stock $ 225,000, Accumulated Depreciation$ 28,800 and Retained Earnings of $ 46,480.

Requirements:

You must follow the posted lecture on Budgets that I authored.

Using Excel and the information above, prepare the following budgets and schedules for the year 1999, showing both quarterly and the year total figures:
1. Sales budget & schedule of cash collections from customers
2. Production budget
3. Direct materials budget & schedule of expected payments for direct materials
4. Direct labor budget
5. Manufacturing overhead budget
6. Selling & administrative expense budget
7. Cash budget
In addition, complete the following:

A. Finished goods inventory budget (Schedule)
B. Budgeted income statement (Budgeted financial statement)
C. Budgeted balance sheet (Budgeted financial statement)

Answer 1

Requirement 1	Sales Budget
		First Quarter	Second Quarter	Third Quarter	Fourth Quarted	Year
	Sales in Units	3,000	3,500	4,000	4,500	15,000
	Sale price per pair	$                         60	$                         60	$                         60	$                         60	$                         60
	Sales in dollars	$              180,000	$              210,000	$              240,000	$              270,000	$              900,000
		(3,000*$60)	(3,500*$60)	(4,000*$60)	(4,500*$60)	(15,000*$60)
	Total sales	$              180,000	$              210,000	$              240,000	$              270,000	$              900,000
	Less: Bad debt 1% of sales	$                   1,800	$                   2,100	$                   2,400	$                   2,700	$                   9,000
	Net amout to be collected	$              178,200	$              207,900	$              237,600	$              267,300	$              891,000
	Schedule for expected colllection from customer
	From sales	First Quarter	Second Quarter	Third Quarter	Fourth Quarted	Year
	Beginning	$                 60,000				$                 60,000
	First Quarter	$              144,000	$                 34,200			$              178,200
	Second Quarter		$              168,000	$                 39,900		$              207,900
	Third Quarter			$              192,000	$                 45,600	$              237,600
	Fourth Quarted				$              216,000	$              216,000
	Total Collection	$              204,000	$              202,200	$              231,900	$              261,600	$              899,700
	Current month collection =	80% of current month sale+19% of pervious month sale
Requirement 2	Production Budget	First Quarter	Second Quarter	Third Quarter	Fourth Quarted	Year	First Quarter
	Sales in Units	3,000	3,500	4,000	4,500	15,000	5000
	Add: Required Ending inventory (20% of next quarter sales)	700	800	900	1,000	1,000	-
		3,700	4,300	4,900	5,500	16,000	5,000
	Less: Beginning Inventory	600	700	800	900	600	1,000
	Units to be produced	3,100	3,600	4,100	4,600	15,400	4,000
Requirement 3	Material Purchase budget					1999	2000
		First Quarter	Second Quarter	Third Quarter	Fourth Quarted	Year	First Quarter
	Units to be produced	3,100	3,600	4,100	4,600	15,400	4,000
	Material Required per unit	2	2	2	2	2	2
	Total Material required	6,200.00	7,200.00	8,200.00	9,200.00	30,800.00	8,000.00
	Add: Required Ending inventory (10% of next quarter sales)	720.00	820.00	920.00	1,020.00	1,020.00
		6,920.00	8,020.00	9,120.00	10,220.00	31,820.00
	Less: Beginning Inventory	620.00	720.00	820.00	920.00	620.00
	Material to be purchased-Units	6,300.00	7,300.00	8,300.00	9,300.00	31,200.00
	Rate per pound	$                     4.00	$                     4.00	$                     4.00	$                     4.00	$                     4.00
	Material to be purchased-Dollars	$           25,200.00	$           29,200.00	$           33,200.00	$           37,200.00	$        124,800.00
		(6,300*$4)	(7,300*$4)	(8,300*$4)	(9,080*$4)	($31,230*$4)
	Schedule for payment for direct material purchases
						1999
	From Purchases	First Quarter	Second Quarter	Third Quarter	Fourth Quarted	Year
	Beginning	$                 10,600				$                 10,600
	First Quarter	$                 12,600	$                 12,600			$                 25,200
	Second Quarter		$                 14,600	$           14,600.00		$                 29,200
	Third Quarter			$                 16,600	$           16,600.00	$                 33,200
	Fourth Quarted				$                 18,600	$                 18,600
	Total Collection	$                 23,200	$                 27,200	$                 31,200	$                 35,200	$              116,800
Requirement 4	Direct labour budget					1999
		First Quarter	Second Quarter	Third Quarter	Fourth Quarted	Year
	Units to be produced	3,100	3,600	4,100	4,600	15,400
	Labour Required per unit	2.00	2.00	2.00	2.00	2.00
	Total Direct labour	6,200	7,200.00	8,200.00	9,200.00	30,800.00
	Labour cost per hour	$                   10.00	$                   10.00	$                   10.00	$                   10.00	$                   10.00
	Labour cost in Dollars	$                 62,000	$                 72,000	$                 82,000	$                 92,000	$              308,000
		(6,200*$10)	(7,200*$10)	(8,200*$10)	(9,200*$10)	(30,800*$10)