In: Accounting
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% |
jan |
feb |
mar |
total for quarter |
apr |
may |
||
sales budget (a) |
|||||||
sales units |
121000 |
121145 |
120927 |
121072 |
121217 |
||
price |
13 |
13 |
13.75 |
||||
total sales |
1573000 |
1574885 |
1662746 |
4810631 |
|||
production budget (b) |
|||||||
budgeted sales units |
121000 |
121145 |
120927 |
121072 |
121217 |
||
add: planned ending units |
18172 |
18139 |
18161 |
18183 |
|||
less: beginning units |
-18150 |
-18172 |
-18139 |
-18161 |
|||
planned production in units |
121022 |
121112 |
120949 |
363083 |
121094 |
||
Direct Materials budget [c] |
|||||||
planned production in units |
121022 |
121112 |
120949 |
121094 |
|||
direct material required (lbs) /unit |
2 |
2 |
2 |
2 |
|||
DM required for production in (lbs) |
242044 |
242224 |
241898 |
242188 |
|||
add: budgeted ending DM (lbs) |
12111.2 |
12094.9 |
12109.4 |
||||
less: budgeted beginning DM (lbs) |
-9600 |
-12111.2 |
-12094.9 |
||||
budgeted DM purchase (lbs) |
244555.2 |
242207.7 |
241912.5 |
||||
cost per lb |
3.68 |
3.68 |
3.68 |
||||
budgeted DM purchase $ |
899963 |
891324 |
890238 |
2681525 |
|||
Direct labour budget (d) |
|||||||
planned production in units |
121022 |
121112 |
120949 |
||||
* Direct labor hours/unit (hrs) |
0.2 |
0.2 |
0.2 |
||||
budgeted direct labor hours |
24204 |
24222 |
24190 |
||||
cost/ diirect labor hr $ |
12.45 |
12.45 |
12.45 |
||||
budgeted direct labor cost |
301340 |
301564 |
301166 |
904070 |
Excel Formulas
jan |
feb |
mar |
total for quarter |
apr |
may |
||
sales budget (a) |
|||||||
sales units |
121000 |
=ROUND(B3+B3*0.0012,0) |
=ROUND(C3-C3*0.0018,0) |
=ROUND(D3+D3*0.0012,0) |
=ROUND(G3+G3*0.0012,0) |
||
price |
13 |
13 |
13.75 |
||||
total sales |
=B3*B4 |
=C3*C4 |
=ROUND(D3*D4,0) |
=B5+C5+D5 |
|||
production budget (b) |
|||||||
budgeted sales units |
121000 |
121145 |
120927 |
121072 |
121217 |
||
add: planned ending units |
=ROUND(C8*0.15,0) |
=ROUND(D8*0.15,0) |
=ROUND(G3*0.15,0) |
=ROUND(H3*0.15,0) |
|||
less: beginning units |
=-ROUND(B8*0.15,0) |
-18172 |
-18139 |
-18161 |
|||
planned production in units |
=SUM(B8:B10) |
=SUM(C8:C10) |
=SUM(D8:D10) |
=B11+C11+D11 |
=SUM(G8:G10) |
||
Direct Materials budget [c] |
|||||||
planned production in units |
121022 |
121112 |
120949 |
=G11 |
|||
direct material required (lbs) /unit |
2 |
2 |
2 |
2 |
|||
DM required for production in (lbs) |
=B14*B15 |
=C14*C15 |
=D14*D15 |
=G14*G15 |
|||
add: budgeted ending DM (lbs) |
=0.05*C16 |
=0.05*D16 |
=0.05*G16 |
||||
less: budgeted beginning DM (lbs) |
-9600 |
=-B17 |
=-C17 |
||||
budgeted DM purchase (lbs) |
=SUM(B16:B18) |
=SUM(C16:C18) |
=SUM(D16:D18) |
||||
cost per lb |
3.68 |
3.68 |
3.68 |
||||
budgeted DM purchase $ |
=ROUND(B19*B20,0) |
=ROUND(C19*C20,0) |
=ROUND(D19*D20,0) |
=B21+C21+D21 |
|||
Direct labour budget (d) |
|||||||
planned production in units |
121022 |
121112 |
120949 |
||||
* Direct labor hours/unit (hrs) |
=12/60 |
=12/60 |
=12/60 |
||||
budgeted direct labor hours |
=ROUND(B24*B25,0) |
=ROUND(C24*C25,0) |
=ROUND(D24*D25,0) |
||||
cost/ diirect labor hr $ |
12.45 |
12.45 |
12.45 |
||||
budgeted direct labor cost |
=ROUND(B26*B27,0) |
=ROUND(C26*C27,0) |
=ROUND(D26*D27,0) |
=B28+C28+D28 |