attached is the case. needs to be set up just like the pics I have attached for reference. please do this in excel and label everything correctly.

thanks.

MANAGERIAL ACCOUNTING CASE 2 - PREPARATION OF A MASTER BUDGET Information for 2016: 1. Sales forecast: January: 5,100 units; February: 6,900 units; March: 7,300 units; April: 7,500 units. The unit sales price is $49. All sales are on credit and collections are 30% in the month of sale and 70% the following month. Accounts receivable as of December 31, 2015 is $18,000 and this amount is expected to be collected in January 2016. 2. End of month inventory must equal 30% of next month's sales. The inventory at the end of December 2015 was 1,530 units. 3. The following are the expected costs for direct materials, direct labor and manufacturing overhead: DM DL Overhead January $12/unit $15/unit $7,500 + $2.60 per unit produced February $12/unit $15/unit $7,500 + $2.60 per unit produced March $12/unit $15/unit $7,500 + $2.60 per unit produced A. Direct materials are paid 40% in the month incurred and 60% in the following month. Account payable for materials as of December 31, 2015 is $5,100; this amount will be paid in January 2016. B. Direct labor is paid in the month incurred. C. Overhead costs are paid in the month incurred. Fixed overhead includes depreciation of $5,500 per month. 4. Selling costs are sales commissions: $2.10 per unit sold; shipping costs: $0.50 per unit sold. Administrative costs per month are: salaries: $15,000; rent: $2,000; depreciation: $1,900. All costs are paid in month incurred. 5. The company plans to buy equipment costing $19,000 in January and to pay dividends of $35,000 in March 6. The cash balance as of December 31, 2015 is $25,000. The company requires a minimum cash balance of $2,000. The company has a revolving credit with US Bank to borrow in increments of $1,000 at the beginning of each month at interest of 12% annual rate. The company may borrow any amount at the beginning of any month and repays its loans, or any parts of its loans, at the end of any month. Interest payments are due on any principle at the time it is repaid (the amount repaid does not have to be in increments of $1,000). For simplicity, assume that interest is not compounded. As of December 31, 2015 the company has no outstanding loans. Required: Based on the information given, prepare the following budgets for each month of the first quarter of 2016 and the quarter totals: 1. Sales Budget, including a schedule of expected cash collections; 2. Production Budget (in units); 3. Direct materials budget, including schedule of expected cash disbursements; 4. Direct labor budget; 5. Manufacturing Overhead Budget; 6. Selling and Administrative Expenses Budget; 7. Cash Budget. Case 2 Grading Rubric Managerial Accounting Item Score Student Name: ________________________________________ Maximum Instruction 1. Sales budget, including schedule of expected cash collections 14 The information is organized similarly to the example given in Schedule 1 on page 353. Accurate calculations of budgeted sales for the three months of the first quarter and the total for the quarter are present. Accurate calculations of expected cash collections for the three months of the first quarter and the total for the quarter are present. Instruction 2. Production budget 20 The information is organized similarly to the example given in Schedule 2 on page 354. Accurate calculations for the production budget for the three months of the first quarter and the total for the quarter are present. Instruction 3. Direct material budget, including schedule of expected cash disbursements 13 The information is organized similarly to the example given in Schedule 3 on page 356 (some modifications of the fields may be necessary to accommodate the data provided in the case). Accurate calculations for the direct materials budget for the three months of the first quarter and the total for the quarter are present. Accurate calculations for the expected cash disbursements for direct materials for the three months of the first quarter and the total for the quarter are present. Instruction 4. Direct labor budget 4 Accurate calculations for the direct labor budget for the three months of the first quarter and the total for the quarter are present. Instruction 4. Manufacturing Overhead budget 13 The information is organized similarly to the example given in Schedule 5 on page 358. Accurate calculations for the manufacturing overhead budget for the three months of the first quarter and the total for the quarter are present. Instruction 5. Selling and administrative expenses budget 22 The information is organized similarly to the example given in Schedule 7 on page 360. Accurate calculations for selling and administrative expenses budget for the three months of the first quarter and the total for the quarter are present. Instruction 6. Cash budget 29 The information is organized similarly to the example given in Schedule 8 on page 362. Accurate calculations for the cash budget for the three months of the first quarter and the total for the quarter are present. Presentation of Case Assignment 5 The case is presentable, clean, and organized in order of the requirements. Grading rubric is stapled to the case