Question

In: Accounting

Master Budget Project Sam’s Computers manufactures laptop computer stands which can be personalized after mass-production. The...

Master Budget Project

Sam’s Computers manufactures laptop computer stands which can be personalized after mass-production. The company is completing its fifth year of operations and is preparing its master budget for the coming year (2018) based upon the following information:

Fourth-quarter sales for 2017 are 55,000 units. Third quarter sales for 2017 were 50,000 units.

Unit sales by quarter are projected as follows:

First quarter 2018 65,000

Second quarter 2018 70,000

Third quarter 2018 75,000

Fourth quarter 2018 90,000

First quarter 2019 80,000

Second quarter 2019 70,000

Each unit sells for $95. Sam’s Computers estimates that 50% of sales will be collected in the quarter of sale. The company also estimates that 30% will be collected in the quarter following the sale and that 20% of each quarter’s sale will be collected in the second quarter following the sale.

Sam’s tries to maintain at least 20% of next quarter sales forecast in inventory.

Each computer unit uses three hours of direct labor, three pieces of wood, and four cement moldings. Laborers are paid $10 per hour, one piece of wood costs $8, and cement moldings are $1.25 each.

At the end of each quarter, Sam’s plans to have 20 percent of the wood needs and 30 percent of the molding needs for the next quarter’s projected production needs.

Sam’s buys wood and cement moldings on account. Half of the purchases are paid for in the quarter of acquisition, and the remaining half are paid for in the following quarter. Wages and salaries are paid on the 30^th of each month.

Fixed overhead totals $900,000 each quarter. Of this total, $200,000 represents depreciation. All other fixed expenses are paid for in cash in the quarter incurred.

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

Fixed selling and administrative expenses total $250,000 per quarter, including $50,000 depreciation.

Variable selling and administrative expenses are budgeted at $5 per unit sold. All selling and administrative expenses are paid for in the quarter incurred.

Sam will pay quarterly dividends of $300,000.

At the end of the third quarter, a $250,000 long-term debt payment will be made.

During the fourth quarter a $330,000 piece of equipment is purchased with cash.

At the end of the fourth quarter, taxes of $75,000 are due.

Sam’s beginning cash balance is $300,000. Sam must maintain a minimum balance of $250,000 at quarter end. He has access to a line of credit and borrows in multiples of $5,000. Interest of 5% is due quarterly.

Required:

Prepare a master budget for Sam’s Computers for each quarter of 2018 and for the year in total. The following component budgets must be included:

Sales budget

Production budget

Wood direct material budget

Cement moldings direct material budget

Direct labor budget

Overhead budget

Selling and administrative expenses budget

Cash receipts budget

Summary cash budget

You may work in groups of up to three or complete the project on your own. The master budget must be completed in Excel, and formulas must be used. Each group must turn in a hard copy of the master budget and a formula sheet on Tuesday, April 10. (Press CTRL + ` [grave accent] to switch between formulas and values in Excel).

Only one copy will need to be turned in for each group; however, each group member will need a copy for the in-class master budget quiz on April 10, which is part of the required course points.

Expert Solution

Sales Budget
	2018
	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total
Sales units	65000	70000	75000	90000	300000
Selling price per unit	95	95	95	95	95
Budgeted Sales	6175000	6650000	7125000	8550000	28500000

Schedule of cash collections	2018
	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total
Quarter 3 - 2017	950000				950000
Quartr 4 - 2017	1567500	1045000			2612500
Quarter 1 - 2018	3087500	1852500	1235000		6175000
Quarter 2 - 2018		3325000	1995000	1330000	6650000
Quarter 3 - 2018			3562500	2137500	5700000
Quarter 4 - 2018				4275000	4275000
Total Collections	5605000	6222500	6792500	7742500	26362500
Budgeted Cash receipts	5605000	6222500	6792500	7742500	26362500


Production Budget
	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total
Sales units	65000	70000	75000	90000	300000
Add: Ending inventory (20% of next qrtr sale)	14000	15000	18000	16000	16000
Units neded	79000	85000	93000	106000	316000
Less: Beginning inventory	13000	14000	15000	18000	13000
Budgeted Production	66000	71000	78000	88000	303000

Raw material budget
	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total
Budgeted Production( units)	66000	71000	78000	88000	303000
Wood
Material needed per unit (pieces)	3	3	3	3	3
Material needed for production (pieces)	198000	213000	234000	264000	909000
Desired ending inventory	42600	46800	52800	46800	46800
Total units needed	240600	259800	286800	310800	955800
Beginning inventory	39600	42600	46800	52800	39600
Budgeted Purchases	201000	217200	240000	258000	916200
Cost per pound ($)	8.00	8.00	8.00	8.00	8.00
Budgeted Purchases of wood ($)	1608000	1737600	1920000	2064000	7329600

Cement moldings
Material needed per unit (nos)	4	4	4	4	4
Material needed for production (nos)	264000	284000	312000	352000	1212000
Desired ending inventory	85200	93600	105600	93600	93600
Total units needed	349200	377600	417600	445600	1305600
Beginning inventory	79200	85200	93600	105600	79200
Budgeted Purchases	270000	292400	324000	340000	1226400
Cost per no.	1.25	1.25	1.25	1.25	1.25
Budgeted Purchases of cement moldings ($)	337500	365500	405000	425000	1533000

Total budgeted purchases	1945500	2103100	2325000	2489000	8862600

Direct labor budget
	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total
Budgeted production	66000	71000	78000	88000	303000
Direct labor hours per unit	3	3	3	3	3
Total direct labor hours for production	198000	213000	234000	264000	909000
Direct labor cost per hour	10	10	10	10	10
Direct labor cost	1980000	2130000	2340000	2640000	9090000

Manufacturing overhead budget
	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total
Direct labor hours budgeted	198000	213000	234000	264000	909000
Variable overhead per DLH	2	2	2	2	2
Budgeted variable overhead	396000	426000	468000	528000	1818000
Fixed overhead	900000	900000	900000	900000	3600000
Total manufacturing overhead	1296000	1326000	1368000	1428000	5418000
Depreciation	200000	200000	200000	200000	800000
Cash manufacturing overheads	1096000	1126000	1168000	1228000	4618000

Selling and admkinistrative expenses budget
	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total
Budgeted Sales - units	65000	70000	75000	90000	300000
Variable overhead per unit	5	5	5	5	5
Budgeted variable overhead	325000	350000	375000	450000	1500000
Fixed S & A overhead	250000	250000	250000	250000	1000000
Total manufacturing overhead	575000	600000	625000	700000	2500000
Depreciation	50000	50000	50000	50000	200000
Cash manufacturing overheads	525000	550000	575000	650000	2300000

ekkarill92 answered 1 year ago

Product Costing in a JIT/Lean Environment Doll Computer manufactures laptop computers under its own brand, but...

Product Costing in a JIT/Lean Environment Doll Computer manufactures laptop computers under its own brand, but acquires all the components from outside vendors. No computers are assembled until the order is received online from customers, so there is no finished goods inventory. When an order is received, the bill of materials required to fill the order is prepared automatically and sent electronically to the various vendors. All components are received from vendors within three days and the completed order is...

A computer company manufactures 3 types of computers which are personal computers, workbooks and gamebooks. Each...

A computer company manufactures 3 types of computers which are personal computers, workbooks and gamebooks. Each personal computer is produced in 0.2 hours, assembled in 0.1 hours, and inspected in 0.05 hours. Each workbook is produced in 0.2 hours, assembled in 0.3 hours, and inspected in 0.075 hours. Each gamebook is produced in 0.4 hours, assembled in 0.4 hours, and inspected in 0.15 hours. At most 600 hours of production, 480 hours of assembly and 160 hours of inspection can...

. manufactures ONE PRODUCT. The company prepared a master budget for 2016 which included the following...

. manufactures ONE PRODUCT. The company prepared a master budget for 2016 which included the following pro-forma income statement, which is based on an expected production and sales volume of 15,000 units. Mississippi Corporation Budgeted Income Statement For the Year Ending December 31, 2019 Sales $3,000,000 Cost of Goods Sold: Direct Materials $975,000 Direct Labor $225,000 Machinery Repairs (Variable) $60,000 Depreciation - Plant Equipment $300,000 Utilities ($45,000 is variable) $195,000 Plant Management Salaries $200,000 Cost of Goods Sold $1,955,000 Gross...

1.Which of the following budgets comes after the production budget but before the cash budget?. Single...

1.Which of the following budgets comes after the production budget but before the cash budget?. Single choice. A. Sales budget B. Material purchases budget C. Budgeted balance sheet D. Budgeted income statement 2.Which of the following is NOT usually a way to estimate future sales?. Single choice. A. Trends in the company’s sales data B. Estimates from the company’s salespersons C. Sales are estimated based on expected costs D. Mathematical models adjusted by an experienced manager using professional judgment 3.A...