ACT 220 Ewing Master Budget Project

ACT 220 Ewing Master Budget Project

                Click Link Below To Buy:

http://hwcampus.com/shop/act-220-ewing-master-budget-project/

Contact Us:

Hwcoursehelp@gmail.com

Master Budget Project (15 points, Master Budget Quiz is 20 points)

Cam’s Bookcases manufactures spirit-oriented bookcases for sale at college bookstores.  The company is completing its fifth year of operations and is preparing its master budget for the coming year (2015) based upon the following information:

1. Fourth-quarter sales for 2014 are 55,000 units.  Third quarter sales were 50,000 units.
2. Unit sales by quarter are projected as follows:

First quarter 2015                    65,000

Second quarter 2015               70,000

Third quarter 2015                  75,000

Fourth quarter 2015                90,000

First quarter 2016                    80,000

Second quarter 2016               70,000

Each unit sells for $100.  Cam’s estimates that 60% 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 10% of each quarter’s sale will be collected in the second quarter following the sale. 

3. Cam’s tries to maintain at least 20% of next quarter sales forecast in inventory.  Beginning inventory in the first quarter of 2015 is 6,500 units. 
4. Each bookshelf uses one and a half hours of direct labor, five pieces of wood, and two cement moldings.  Laborers are paid $10 per hour, one piece of wood costs $8, and cement moldings are $2.50 each.
5. At the end of each quarter, Cam’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. 
6. Cam’s buys wood frames 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.
7. 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. 
8. Variable overhead is budgeted at $2 per direct labor hour.  All variable overhead expenses are paid for in the quarter incurred.
9. Fixed selling and administrative expenses total $250,000 per quarter, including $50,000 depreciation.
10. Variable selling and administrative expenses are budgeted at $5 per unit sold.  All selling and administrative expenses are paid for in the quarter incurred.
11. Cam will pay quarterly dividends of $300,000.
12. At the end of the third quarter, a $250,000 long-term debt payment will be made.
13. At the end of the fourth quarter, $3 million of equipment will be purchased.
14. Cam’s beginning cash balance is $300,000.

Required:

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

1. Sales budget
2. Production budget
3. Wood frame direct material budget
4. Cement moldings direct material budget
5. Direct labor budget
6. Overhead budget
7. Selling and administrative expenses budget
8. Cash receipts budget
9. Summary cash budget

You may work in groups of up to four or complete the project on your own.  The master budget must be completed in Excel (or a comparable spreadsheet program), and formulas must be used.  Each group must turn in a hard copy of the master budget and a formula sheet on Tuesday, April 21.  (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 21, which is part of the required course points.