Question

You are an managerial accountant for Blackmore Industries, and you are preparing the 2018
budget. Consider the following information, and prepare the required budgets according to
the instructions that follow:
Sales Information
November 2017 unit sales (actual)		118,729
December 2017 unit sales (actual)		120,896
January 2018 unit sales (planned)		121,000
Sales price per unit		$13.00
For all months in 2018, unit sales are expected to increase 1.2% over the previous month with the
exception of March, when a planned unit price increase to $13.75 is expected to decrease March
unit sales (compared to February) by 1.8%. The price increase will remain in effect for the rest of
the year.
Finished Goods Inventory Planning
Blackmore plans to keep 15% of the following month's unit sales on hand in finished goods
inventory at the end of any given month. Blackmore has that percentage of January's planned
sales (above) on hand at December 31, 2017.
Accounts Receivable and Collections
All sales are on account. Generally, 44% of each month's sales are collected in the month after
the sale, while 1.4% are never collected, and eventually written off. All other sales are collected
in the month of the sale.
Net (collectible) accounts receivable balance at December 31, 2017:				$691,525.00
Material Inventory Costs and Planning
Each unit of finished product is made from 2 pounds of a metallic raw material that costs $3.68
per pound. Blackmore plans to keep 5% of the following month's raw materials production
needs in inventory at the end of any given month, and has 9,600 pounds of raw material on
hand at December 31, 2017.
Accounts Payable and Disbursements
All material purchases are on account. 32% of purchases are paid for in the month following the
purchase, with the remainder paid for in the month of purchase.
Accounts payable balance at December 31, 2017:				$358,500.00
Direct Labor and Costs
Direct labor time per unit of finished goods			12	minutes
Direct labor cost			$12.45	per hour
Manufacturing Overhead Costs
Indirect materials			$0.25	per direct labor hour
Indirect labor			0.49	per direct labor hour
Maintenance			0.27	per direct labor hour
Utilities			0.39	per direct labor hour
Depreciation			$9,700	per month
Insurance			4,800	per month
Property taxes			2,100	per month
All items except depreciation are paid in the month incurred.
Selling and Administrative Costs
Advertising			$8,900	per month
Insurance			4,800	per month
Salaries			74,200	per month
Depreciation			5,400	per month
Other fixed costs			3,200	per month
All items except depreciation are paid in the month incurred.
Other Budgeting Items
Income tax expense is recorded at 25% of pretax net income. The company makes estimated
payments monthly for these amounts.
A budgeted purchase of fixed assets in the amount of $475,000 is planned for February, 2017.
Because the company uses a mid-year convention for depreciation calculations, this purchase
will not affect budgeted depreciation expense in the first quarter.
At December 31, 2017, Blackmore has $297,500 in cash. Hendrix maintains a minimum balance of
$250,000 in cash at all times, and any projected cash shortfall will be covered via a borrowing on
a line of credit. The line of credit accrues interest at 6% annualy (0.5% per month), and is repaid
as soon as Hendrix has sufficient cash to repay it while staying above the $250,000 minimum.
For the first quarter of 2018, do the following.
(a) Prepare a sales budget. This is similar to Illustration 21-3 on page 1088 of your textbook.
(b) Prepare a production budget. This is similar to Illustration 21-5 on page 1089 of your textbook.
(c) Prepare a direct materials budget. (Round to nearest dollar) This is similar to Illustration 21-7
on page 1091 of your textbook.
(d) Prepare a direct labor budget. (For calculations, round to the nearest hour.) This is similar to
Illustration 21-9 on page 1094 of your textbook.
(e) Prepare a manufacturing overhead budget. (Round intermediate amounts to the nearest
dollar.) This is similar to Illustration 21-10 on page 1094 of your textbook.
(f) Prepare a selling and administrative budget. This is similar to Illustration 21-11 on page 1095
of your textbook.
(g) Prepare a budgeted income statement. (Round intermediate calculations to the nearest
dollar.) This is similar to Illustration 21-13 on page 1096 of your textbook.
(h) Prepare a cash budget. This is similar to Illustration 21-17 on page 1100 of your textbook.
(You will need to prepare schedules for expected collections from customers and expected
payments to vendors first. See Illustrations 21-15 and 21-16 on page 1099 of your textbook
for guidance.)
Rules:
* Use Excel's functionality to your benefit. Points are lost for lack of formula.
* Use proper formats for schedules, following the referenced textbook examples.
* Use dollar-signs and underscores where appropriate.
* Double-check your work! Verify your formula and logic!
Grading Guidelines:
Effective Use of Excel	40%
Facts, Logic	20%
Completeness	30%
Spelling, Punctuation, Value Format	10%

Answer 1

(a). Sales Budget
	January	February	March	Total
Budgeted unit sales	121000	122452	120248	363700
Unit sales price	13	13	13.75
Budgeted Sales	1573000	1591876	1653410	4818286
Schedule of cash collections
	January	February	March	Total
December Sales	691525			691525
January Sales	858858	692120		1550978	22022
February Sales		869164	700425	1569590
March Sales			902762	902762
Total Collections	1550383	1561284	1603187	4714855
Bad Debts	22022	22286	23148	67456
Receivables	692120	700425	727500	727500
(b) Production Budget
	January	February	March	Total	April
Budgeted unit sales	121000	122452	120248	363700	122413
Plus: Desired ending inventory	18368	18037	18362	18362	18692
Total Units needed	139368	140489	138610	382062	141105
Less: Beginning inventory	18150	18368	18037	18150	18362
Budgeted production units	121218	122121	120573	363912	122743
(c) Direct material budget
	January	February	March	Total
Budgeted production units	121218	122121	120573	363912	122743
pounds per unit of production	2	2	2	2	2
Total pounds required	242436	244242	241146	727824	245486
Plus: Desired ending inventory	12212	12057	12274	12274
Total pounds neded	254648	256299	253420	740098
Less: Beginning inventory	9600	12212	12057	9600
Budgeted purchases -- pounds	245048	244087	241363	730498
Cost per pound - $	3.68	3.68	3.68	3.68
Budgeted purchases - $	901777	898240	888216	2688233
Schedule of payments for raw materials
	January	February	March	Total
December purchases	358500			358500
January purchases	613208	288569		901777
February purchases		610803	287437	898240
March purchases			603987	603987
Total payments for raw material	971708	899372	891424	2762504
Accounts Payable	288569	287437	284229	284229
(d) Direct labor budget
	January	February	March	Total
Budgeted production units	121218	122121	120573	363912
Direct labor hours per unit	0.2	0.2	0.2	0.2
Direct labor hours for production	24244	24424	24115	72782
Direct labor rate per hour - $	12.45	12.45	12.45	12.45
Budgeted direct labor cost	301833	304081	300227	906141
(e) Manufacturing overheads budget
	January	February	March	Total
Budgeted direct labor hours	24244	24424	24115	72782
Indirect material ($0.25 per hour)	6061	6106	6029	18196
Indirect labor ($0.49 per hour)	11879	11968	11816	35663
Maintenance ($0.27 per hour)	6546	6595	6511	19651
Utilities ($0.39 per hour)	9455	9525	9405	28385
Depreciation (per month)	9700	9700	9700	29100
Insurance (per month)	4800	4800	4800	14400
Property Taxes (per month)	2100	2100	2100	6300
Total manufacturing overheads	50541	50794	50360	151695
Cash payment for mfg. Overheads	40841	41094	40660	122595
(f) Selling and administrative expenses
	January	February	March	Total
Advertising	8900	8900	8900	26700
Insurance	4800	4800	4800	14400
Salaries	74200	74200	74200	222600
Depreciation	5400	5400	5400	16200
Other fixed costs	3200	3200	3200	9600
Total selling and admn.expenses	96500	96500	96500	289500
Cash payment for S&A expenses	91100	91100	91100	273300
BUDGETED INCOME STATEMENT
	January	February	March	Total
Sales Revenue	1573000	1591876	1653410	4818286
Cost of goods sold	1242300	1257084	1234667	3734052
Gross profit	330700	334792	418743	1084234
Selling and admn. Expenses
Advertising	8900	8900	8900	26700
Insurance	4800	4800	4800	14400
Salaries	74200	74200	74200	222600
Depreciation	5400	5400	5400	16200
Other fixed costs	3200	3200	3200	9600
total S & A expenses	96500	96500	96500	289500
Net income	234200	238292	322243	794734
Cost of goods sold
		January	February	March	Total
Direct material cost	A	892164.5	898811	887417	2678392
Direct labor	B	301832.8	304081	300227	906140.9
Maufacturing overheads	C	50541	50794	50360	151695.4
Total manufacturing cost(A+B+C)	D	1244538	1253686	1238004	3736229
Production units	E	121218	122121	120573	363912
Cost per unit (D / E)	F	10.27	10.27	10.27	10.27
Beginning inventory	G	18150	18368	18037	18150.00
Ending inventory units	H	18368	18037	18362	18362
Cost of beginning inventory (G x F)	I	186345	188565	185198	186343
Cost of ending inventory (H X F)	J	188583.2	185167	188535	188520
Cost of goods sold (D+E-J)	K	1242300	1257084	1234667	3734052