Question

Cartwright Inc produces wiring harnesses used in semi-trailers. The wiring harnesses sales for the next 4 months is projected to be:

January  15,000. February  17,000, March  22,000, April 24,000, May 26,000.

1. Finished goods inventory on January 1 is expected to be 5000 units. The desired ending FGI for any month (except for the Dec 31 inventory as noted previously) is expected to be 20% of the following month’s sales.
2. Data for materials used are: Part A87  4 parts per unit             $26.50 per part   Part D33   3 parts per unit             $10.00 per part

            Raw materials inventory is always budgeted to equal 25% of the following month’s production needs. On March 31, the ending inventory of A87 is expected to be 18,000 parts and for D33 is expected to be 12,000 parts.

3. Direct labor is made up of two types: Leather work is done by a Leather worker who is paid $20 per hour. The leather worker spends 1.75 hours on each harness. Assembly work is done by an Assembler who is $15 per hour. The Assembler spends 0.75 hour on each harness.
4. Overhead each month is estimated at:

The cost driver is DLH, which consists of Leather worker DLH plus Assembler DLH.

5. Selling and Admin is estimated each to be:

6. Selling price per harness is $330.00.
7. Sales are all on account. 25% of sales are collected in the month of sale and 75% in the month following the sale. Accounts receivable on January 1 is $150,000.
8. February is a planned purchase of land for $237,500.

Borrowings are at 6% per year interest, and borrowings are assumed to be at the beginning of the month required and at the end of the month of repayment. Beginning cash is $20,000. Interest on borrowings is paid before any principal can be paid. Principal and any accumulated interest are assumed to be paid at the end of any month when excess funds are available. Minimum cash balance at the end of any month is $10,000

1. Minimum cash balance at the end of any month is $10,000.

SALES INFORMATION Projected sales in units January 15000 February 17000 march 22000 April 24000 26000 May Selling price of each harness (finished product outptut) 330 Collections predition collected in month of sale 25% of current month sales 75% of prior month sales collected in subsequent month $150,000 Accounts receivable as at January 1 PRODUCTION INFORMATION Standard production predictions Finished good inventory, Jan 1 5000 units Finished Goods Inventory policy Desired ending FGI 20% percent of next months sales volun Direct materials usage - two types quantity per unit of ourput (per harness) A87 D33 3 Raw Materials inventory policy 25% percent of the follow Desired ending of the month RMI Assume beginning RMI (on Jan 1) is A87 18000 D33 12000 Direct labor two types Leather worker each harness takes 1.75 hours of direct labor 20 dollars per hour wage rate for direct labor is Assembler each harness takes 0.75 hour of direct labor wag rate for Assembler 15 dollars per hour Overhead estimates Fixed portion (per month) variable cost ($ per D Supplies 1 Power 0.2

REQUIRED:

Please prepare the following budgets in an Excel spreadsheet for each month of the first quarter and for the quarter in total. The first six budgets are to be on one tab, separate from the original data, and the two last budgets (Cash and Income Statement) are to be on a third, separate, tab.

1. Sales budget
2. Production budget
3. Direct materials purchases budget
4. Direct labor budget
5. Overhead budget
6. Selling and Admin budget
7. Cash Budget
8. Income Statement

Answer 1

Since, the question has multiple subparts in the form of different budgets, I have prepared the first four budget.

_______

Part 1)

The sales budget for the first quarter is given as follows:

	Sales Budget
	January	February	March	Total
Projected Sales in Units (A)	15,000	17,000	22,000	54,000
Unit Price (B)	330.00	330.00	330.00	330.00
Projected Sales Revenue (A*B)	$4,950,000	$5,610,000	$7,260,000	$17,820,000

_____

Part 2)

The production budget for the frst quarter is prepared as below:

	Production Budget
	January	February	March	Total
Projected Sales in Units	15,000	17,000	22,000	54,000
Add Desired Ending Inventory	3,400 (17,000*20%)	4,400 (22,000*20%)	4,800 (24,000*20%)	4,800
Total Units Needed	18,400	21,400	26,800	58,800
Less Opening Inventory	5,000 (given)	3,400 (17,000*20%)	4,400 (22,000*20%)	5,000
Total Units to be Produced	13,400	18,000	22,400	53,800

_____

Part 3)

The direct material purchases budget is given as follows:

	Direct Material Purchases Budget
	January		February		March		Total
	A87	D33	A87	D33	A87	D33	A87	D33
Total Units to be Produced (C)	13,400	13,400	18,000	18,000	22,400	22,400	53,800	53,800
Material Needed Per Unit (D)	4	3	4	3	4	3	4	3
Total Material Needed for Production (C*D)	53,600	40,200	72,000	54,000	89,600	67,200	215,200	161,400
Desired Ending Inventory	18,000 (72,000*25%)	13,500 (54,000*25%)	22,400 (89,600*25%)	16,800 (67,200*25%)	24,400 (24,400*4*25%)	18,300 (24,400*3*25%)	24,400	18,300
Total Material Needed	71,600	53,700	94,400	70,800	114,000	85,500	239,600	179,700
Less Opening Inventory	18,000 (given)	12,000 (given)	18,000 (72,000*25%)	13,500 (54,000*25%)	22,400 (89,600*25%)	16,800 (67,200*25%)	18,000	12,000
Total Material to be Purchased (E)	53,600	41,700	76,400	57,300	91,600	68,700	221,600	167,700
Cost Per Unit (F)	26.50	10	26.50	10	26.50	10	26.50	10
Total Cost of Direct Materials	$1,420,400	$417,000	$2,024,600	$573,000	$2,427,400	$687,000	$5,872,400	$1,677,000

__

Notes:

1) The production for the month of April is calculated as below:

Projected Sales in Units	24,000
Add Desired Ending Inventory (26,000*20%)	5,200
Total Units Needed	29,200
Less Opening Inventory (24,000*20%)	4,800
Total Units to be Produced	24,400

_____

Part 4)

The direct labor budget is prepared as follows:

	Direct Labor Budget
	January	February	March	Total
Total Units to be Produced (G)	13,400	18,000	22,400	53,800
Hours Per Unit (H)	1.75	1.75	1.75	1.75
Total Hours Needed (I=G*H)	23,450	31,500	39,200	94,150
Rate Per Hour (J)	20	20	20	20
Total Direct Labor Cost (I*J)	$469,000	$630,000	$784,000	$1,883,000