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) | 117,405 | ||||
December 2017 unit sales (actual) | 120,896 | ||||
January 2018 unit sales (planned) | 122,000 | ||||
Sales price per unit | $13.00 | ||||
For all months in 2018, unit sales are expected to increase 1.3% over the previous month with the | |||||
exception of March, when a planned unit price increase to $13.25 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.2% 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.66 | |||||
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: | $362,000.00 | ||||
Direct Labor and Costs | |||||
Direct labor time per unit of finished goods | 15 | minutes | |||
Direct labor cost | $10.05 | per hour | |||
Manufacturing Overhead Costs | |||||
Indirect materials | $0.26 | per direct labor hour | |||
Indirect labor | 0.54 | per direct labor hour | |||
Maintenance | 0.25 | per direct labor hour | |||
Utilities | 0.34 | 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% |