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:

Fourth quarter sales for 2015 were 50,000 units.Budgetunitsalesbyquarter(for2016)areasfollows:

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.

• ABCs finished goods ending inventory policy is to have 30% of next quarters 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.

• Eachfinishedunitusesthreepiecesofplastic.Eachpieceofplasticcosts$60.AttheendofeachquarterABCplanstohave30%ofthedirectmaterialsneededforthecurrentquarterssalesquantity.ThispolicywasmetonJanuary1,2016.ABCbuysdirectmaterialsonaccount.Seventypercentofthepurchasesarepaidforinthequarterofacquisitionandtheremainingthirtypercentarepaidforinthefollowingquarter.
• Eachunitusestwohoursofdirectlabortofinish.Directlaborersarepaid$20perhourandallwagesarepaidinthesamequarterasincurred.
• Fixedoverheadcoststotal$1,500,000eachquarter.Ofthistotal,$40,000representsdepreciation.Allotherfixedexpensesarepaidforincashinthequarterincurred.Thefixedoverheadrate(baseisunits)iscomputedbydividingtheyearstotalfixedoverheadbytheyearsexpectedactualunitsproducedwhencomputingthecostofafinishedunitforendingfinishedgoodsinventory.Roundtheoverheadratetothenearesttwodecimalpoints.Rememberthatdepreciationisnotpaidfor.
• Variableoverheadisbudgetedat$10perdirectlaborhour.Allvariableoverheadexpensesarepaidforinthequarterincurred.
• Fixedsellingandadministrationexpensesarebudgetedat$500,000perquarter,including$30,000ofdepreciation.Rememberagainthatdepreciationisnotpaidfor.Thefixedsellingandadministrationexpensesotherthandepreciationarepaidinthequarterincurred.
• Variablesellingandadministrationexpensesarebudgetedat$10perunitsold.Also,foreachquarterthereisa$100expenseinwhichyouentitleyournameexpense.Forexample,foreachquarterIwouldshowaShadboltexpenseof$100onalineseparatefromothervariablesellingandadministrationexpense.Allsellingandadministrativeexpensesarepaidinthequarterincurred.

ThebalancesheetasofDecember31,2015,isasfollows:

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,000¹

Capital Stock 15,400,000

Retained Earnings 17,080,000

Total $35,288,000

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:

• Sales budget.
• Production budget.
• Direct Materials purchases budget.
• Direct Labor budget.
• Overhead budget.
• Selling and Administration expense budget.
• Cashbudget.
• Preparethefollowingattheendofthecalendaryearonly:
• 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.

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. g. Variable overhead is budgeted at $10 per direct labor hour. All variable overhead expenses are paid for in the quarter incurred. 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 Capital Stock Retained Earnings Total $2,808,0001 15,400,000 17,080,000 $35,288,000 1 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. 2. 3. 4. 5. 6. 7. Sales budget. Production budget. Direct Materials purchases budget. Direct Labor budget. Overhead budget. Selling and Administration expense budget. 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