You are an managerial accountant for Blackmore Industries, and you are preparing
ID: 2531898 • Letter: Y
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% 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%Explanation / Answer
(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
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.