Assignment: Cost Accounting Project
ABC produces high-tech storage systems. The company is in its fifth year of operations and is preparing to build its master budget for the coming year (2016). The master budget will be based upon the following information:
a. Fourth quarter sales for 2015 were 50,000 units.
b. Budget unit sales by quarter (for 2016) are as follows:
|
First Quarter
|
52,000
|
|
Second Quarter
|
50,000
|
|
Third Quarter
|
48,000
|
|
Fourth Quarter
|
52,000
|
The selling price is $400 per unit. All sales are credit sales. ABC collects 70% of credit sales in the same quarter the sales are made and the remaining 30% is collected in the following quarter. There are no bad debts.
c. ABC's finished goods ending inventory policy is to have 30% of next quarter's sales on hand at the end of each quarter. This policy was met on January 1, 2016. First quarter sales projections for 2017 are 54,000 units.
d. Each finished unit uses three pieces of plastic. Each piece of plastic costs $60. At the end of each quarter ABC plans to have 30% of the direct materials needed for the current quarter's sales quantity. This policy was met on January 1, 2016. ABC buys direct materials on account. Seventy percent of the purchases are paid for in the quarter of acquisition and the remaining thirty percent are paid for in the following quarter.
e. Each unit uses two hours of direct labor to finish. Direct laborers are paid $20 per hour and all wages are paid in the same quarter as incurred.
f. Fixed overhead costs total $1,500,000 each quarter. Of this total, $40,000 represents depreciation. All other fixed expenses are paid for in cash in the quarter incurred. The fixed overhead rate (base is units) is computed by dividing the year's total fixed overhead by the year's expected actual units produced when computing the cost of a finished unit for ending finished goods inventory. Round the overhead rate to the nearest two decimal points. Remember that depreciation is not paid for.
h. Fixed selling and administration expenses are budgeted at $500,000 per quarter, including $30,000 of depreciation. Remember again that depreciation is not paid for. The fixed selling and administration expenses other than depreciation are paid in the quarter incurred.
i. Variable selling and administration expenses are budgeted at $10 per unit sold. Also, for each quarter there is a $100 expense in which you entitle "your name expense." For example, for each quarter I would show a Shadbolt expense of $100 on a line separate from other variable selling and administration expense. All selling and administrative expenses are paid in the quarter incurred.
j. The balance sheet as of December 31, 2015, is as follows: Assets
Cash $2,300,000
Direct Materials Inventory 2,808,000
Accounts Receivable 6,000,000
Finished Goods Inventory 4,180,000
Plant and Equipment, net 20,000,000
Total $35,288,000
Liabilities
Accounts Payable $2,808,0001
Capital Stock 15,400,000
Retained Earnings 17,080,000
Total $35,288,000
For purchase of direct materials only.
k. ABC will pay quarterly dividends of $200,000. Each quarter ABC will purchase $600,000 of equipment - depreciation on these purchase is already included in the above noted costs.
Required:
Using Excel, prepare the following budgets (1 through 7 below) for ABC for each quarter of 2016. The following component budgets must be included on a quarterly basis:
1. Sales budget.
2. Production budget.
3. Direct Materials purchases budget.
4. Direct Labor budget.
5. Overhead budget.
6. Selling and Administration expense budget.
7. Cash budget.
Prepare the following at the end of the calendar year only:
8. Ending Finished goods inventory budget (remember that this is the year-end inventory, not each quarter's ending inventory summed) need to compute cost of goods sold.
9. Cost of Goods Sold budget (there is no work in process inventory).
10. Budgeted income statement using absorption costing.
11. Budgeted Balance Sheet.
Note: The examples in the text are a great place to go for a sample of how the budgets would look. Excel is a great tool and numbers computed will consistently be used within other parts of the budget and the copy (=) function will be a great tool.