Master Budget Project Sam’s Computers manufactures laptop computer stands which
ID: 2555384 • Letter: M
Question
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 30th 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.
Explanation / Answer
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