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 (2017). The master budget will be based upon the following information:

Fourth quarter sales for 2016 were 50,000 units.

Budget unit sales by quarter (for 2017) are as follows:

First Quarter 48,000

Second Quarter 50,000

Third Quarter 47,000

Fourth Quarter 51,000

The selling price is $320 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.

ABCs finished goods ending inventory policy is to have 25% of next quarters sales on hand at the end of each quarter. This policy was met on January 1, 2017. First quarter sales projections for 2018 are 44,000 units.

Each finished unit uses two 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 next quarters production (production for the first quarter of 2018 is expected to be 48,000 finished units). This policy was met on January 1, 2017. ABC buys direct materials on account. Eighty percent of the purchases are paid for in the quarter of acquisition and the remaining twenty percent are paid for in the following quarter.

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.

Fixed overhead costs total $1,000,000 each quarter. Of this total, $300,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 years total fixed overhead by the years 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.

Variable overhead is budgeted at $6 per direct labor hour. All variable overhead expenses are paid for in the quarter incurred.

Fixed selling and administration expenses are budgeted at $500,000 per quarter, including $200,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.

Variable selling and administration expenses are budgeted at $7 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.

The balance sheet as of December 31, 2016, is as follows:

Assets

Cash $2,300,000

Direct Materials Inventory 1,440,000

Accounts Receivable 2,880,000

Finished Goods Inventory 2,700,000

Plant and Equipment, net 21,500,000

Total $30,820,000

Liabilities

Accounts Payable $2,160,000¹

Capital Stock 15,400,000

Retained Earnings 13,260,000

Total $30,820,000

¹For purchase of direct materials only.

ABC will pay quarterly dividends of $200,000. Each quarter ABC will purchase $700,000 of equipment depreciation on these purchase is already included in the above noted costs.

Required:

Using Excel, prepare the following budgets for ABC for 2017. Prepare the following at the end of the calendar year only:

Ending Finished goods inventory budget (remember that this is the year-end inventory, not each quarters ending inventory summed) need to compute cost of goods sold.

Cost of Goods Sold budget (there is no work in process inventory).

Budgeted income statement using absorption costing.

Budgeted Balance Sheet.