Question

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.

ABC’s finished goods ending inventory policy is to have 25% of next quarter’s 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 quarter’s 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 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.

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 quarter’s 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.

Answer 1

Sales Budget
Quarters	2017 1st	2nd	3rd	4th	Total	2018 1st
SalesUnits	48000	50000	47000	51000	196000	44000
Selling price/unit	320	320	320	320	320
Sales $	15360000	16000000	15040000	16320000	62720000
Sales collection budget
70%*current qtr. Sales $	10752000	11200000	10528000	11424000	43904000
30% *Last qtr. sales $	2880000	4608000	4800000	4512000	16800000
Total collections for the Qtr.	13632000	15808000	15328000	15936000	60704000
Production Budget(Units)
Sales budgeted	48000	50000	47000	51000	196000	44000
Desired Ending units(25%*Next qtr. Sales)	12500	11750	12750	11000	11000
Total needed	60500	61750	59750	62000	207000
Less: Available in op. units	12000	12500	11750	12750	12000
Production reqd.	48500	49250	48000	49250	195000
Direct materials Budget
Production reqd.	48500	49250	48000	49250	195000	48000
Direct materials reqd. (pieces)	2	2	2	2	2	2
Total pieces reqd.	97000	98500	96000	98500	390000	96000
Add:Desired Ending inventory	29550	28800	29550	28800	28800
Total needed	126550	127300	125550	127300	418800
Less: Op. Inv.available	29100	29550	28800	29550	29100
Purchases reqd.	97450	97750	96750	97750	389700
Cost /piece	60	60	60	60	60
Total purchases	5847000	5865000	5805000	5865000	23382000
Payment for purchases
80%*current qtr.	4677600	4692000	4644000	4692000	18705600
20%*Last qtr.	2160000	1169400	1173000	1161000	5663400
Total payment for purchases	6837600	5861400	5817000	5853000	24369000
Direct labor Budget
Production reqd.	48500	49250	48000	49250	195000
D/L hrs.at 2 hrs./unit	97000	98500	96000	98500	390000
D/L $ at $ 20/hr.	1940000	1970000	1920000	1970000	7800000
Cash Budget
Beginning Balance	2300000	4336300	9471800	14257700	2300000
Total sales collections	13632000	15808000	15328000	15936000	60704000
1.Total cash available	15932000	20144300	24799800	30193700	63004000
Less: Payments for:
Purchases	6837600	5861400	5817000	5853000	24369000
D/L	1940000	1970000	1920000	1970000	7800000
Cash Fixed OH	700000	700000	700000	700000	2800000
Variable OH at $ 6*D/l hrs.	582000	591000	576000	591000	2340000
Cash Fixed S&A	300000	300000	300000	300000	1200000
Variable s&A at $7/unit sold	336000	350000	329000	357000	1372000
Dividends	200000	200000	200000	200000	800000
Equipment purchase	700000	700000	700000	700000	2800000
XYZ exp.	100	100	100	100	400
2.Total disbursements	11595700	10672500	10542100	10671100	43481400
Ending cash balance(1-2)	4336300	9471800	14257700	19522600	19522600

Ending Finished goods Inventory
Direct materials	11000*2*60=	1320000
Direct labor	11000*2*20=	440000
Fixed OH	4000000/195000*11000=	225641
Variable OH	11000*2*6=	132000
Total cost of Ending Finished Goods Inventory		2117641
Cost of Goods Sold
Direct materials	195000*2*60=	23400000
Direct labor	195000*2*20=	7800000
Fixed OH	1000000*4=	4000000
Variable OH	195000*2*6=	2340000
		37540000
Add: Op. Finished goods		2700000
Total COG available for sale		40240000
Less: Cost of Ending Finished Goods Inventory		2117641
Cost of Goods sold		38122359
Income statement
Sales Revenue(196000*320)		62720000
Less: COGS (as above)		38122359
Gross margin		24597641
Less: Other ipg. Expenses:
Fixed s&A	500000*4=	2000000
Variable S&A	196000*7	1372000
XYZ expense		400
Net Income		21225241