ta ea are re an as: ' *9 a; EXCBi+Spreadsheet+Project+Fall+2018+Bookcases.pdf ($133, atta) v [T3 7.! _v__ is or 1. The BookCases Company manufactures hand-crafted natural wood book cases. Currently the company makes only one size of book case, which is 5 feet tall and has 5 shelves. The nal product consists of a routed, sanded, assembled, and stained poplar wood book case. Direct materials include poplar shelving wood and red oak plywood backing board. Other materials, such as wood screws, sand paper, stain, wood glue, and packaging, are treated as indirect materials. BookCases is preparing budgets for the fourth quarter ending December 31, 2018. F or each requirement belowirezre budgets Month for October, November and Decembe_r,_and a total budget for thmuarter. The previous year's sales (2017/18) for the corresponding period were: October 2,000 book cases November 2,800 book cases December 4,000 book cases January 4,400 book cases February 3,600 book cases The company expects the above volume of book case sales to increase by 15% for the period October 2018 February 2019. The budgeted selling price for 2018 is $280.00 per book case. The company expects 40% of its sales to be cash (COD) sales. The remaining 60% of sales will be made on credit. Prepare a Sales Budget for BookCases. 2. The company desires to have nished goods inventory on hand at the end of each month equal to 15 percent of the following month's budgeted unit sales. On September 30, 2018, there were 345 book cases on hand. (Note, an estimate of sales in January is required in order to complete the production budget for December). Prepare a Production budget. it iiii IE ED #33 as Excel-rSpreadsheet+Project+Fall+2018+Bookcasespdf (1 E, Edi) ~ ! v ;:'-_"3': The book cases require two direct materials: poplar shelving wood and red oak plywood backing board. Twenty (20) feet of 12x1 poplar shelving wood are required for each bookcase produced. Management desires to have materials on hand at the end of each month equal to 20 percent of the following month's bookcase production needs. The beginning inventory of poplar wood, October 2018, was 9,752 feet of wood. Poplar wood is expected to cost $2.00 per foot. Red oak plywood backing board is purchased by the sheet and 4 backing boards can be cut from each sheet. Management desires to have plywood on hand at the end of each month equal to 10 percent of the following month's production needs. The beginning inventory of plywood, October 2018, was 60 sheets. Red oak plywood is it- expected to cost $24 per sheet. (Note, budgeted production in January is required in i order to complete the direct materials budget for December. Also, use the @ROUND function to round up to the nearest whole number the number of sheets of plywood to purchase). Prepare a Direct Materials budget. Also because two direct materials are required for production - poplar wood and red oak plywood - you will need a separate schedule for each direct material. $53 551 Eilii IE EC] ii'ib '2 {D a Excel+Spreadsheet+Project+Fall+2018+Bookcasespdf (% 2 E, lth?) v 4 z' ., a. .... Q_ 4. Each book case requires 6 hours of direct labor. BookCases uses a series of table saws, table routers and sanders set up for specialized operations to achieve production efciencies. Direct labor costs the company $18 per hour. Prepare a Direct Labor budget. 5. BookCases budgets indirect materials (e.g., wood screws, sandpaper, stain, packaging) at $16.50 per book case. BookCases treats indirect labor and utilities as mixed costs. The variable components are $5.40 per book case for indirect labor and $2.20 per book case for utilities. The following xed costs per month are budgeted for indirect labor, $60,000, utilities, $4,000, and other, $61,800. Prepare 2 Manufacturing Overhead budget. 6. Variable selling and administrative expenses are $32.50 per book case sold. Fixed selling and administrative expenses are $100,000 per month. These costs are not itemized, i.e., the budget has only two line items variable operating expenses and xed operating expenses. Prepare an Operating Expenses budget. 7. Prepare a Budgeted Manufacturing Cost per unit budget. Refer to exhibit 9-11 for guidance. To calculate FMOH/unit calculate total F MOH for the year and divide this by budgeted production for the year. The total production volume for the year is budgeted at 48,000 book cases. 8. Prepare a Budgeted Income Statement for the quarter for BookCases. Assume k, interest expense of $0, and income tax expense of 21% of income before taxes