The Manes Company has two products. Product 1 is manufactured entirely in Department X whereas Product 2 is manufactured entirely in Department Y. To produce these two products, the Manes Company has two support departments: B (a materials-handling department) and C (a power-generating department). An analysis of the work supplied by departments B and C in a typical period follows: Used By Supplied By B C X Y B ---- 100 250 150 C 500 ---- 100 400 The work done in Department B is measured by the direct-labor hours of material-handling time. The work done in Department C is measured by the kilowatt-hours of power. The budgeted costs of the support departments for the coming year are $100,000 for Department B and $40,000 for Department C. The budgeted costs of the operating departments for the coming year are $1,500,000 for Department X and $800,000 for Department Y. Required: 1. Determine the allocation of costs of support departments B and C to operating departments X and Y through the reciprocal method using repeated iterations. The following are Excel spreadsheet instructions to follow in answering question 1: a) Open up the uploaded spreadsheet. Go to the worksheet entitled Iteration. b) At top, create an Original Data section for the Manes Company. Use the template in the worksheet to create a table similar to the one after the first paragraph of the problem (with the Supplied By heading for the rows and the Used By heading for the columns). However, convert the numbers into percentages of service provided to the other departments. (Program your iteration section to perform all necessary calculations. Do not hard code any amounts, such as your cost allocations, requiring addition, subtraction, multiplication, or division operations). c) Go to the section Cost Allocation Reciprocal Method and use a format similar to Exhibit 16-6 in the textbook. Show columns for the support departments (B and C), the operating departments (X and Y), and for Total. Create rows for Budgeted costs before allocations, 1st Allocation of Dept. A, 1st Allocation of Dept. B, 2nd allocation of Dept. A, 2nd allocation of Dept. B, and so on. You need to add rows as iterations are needed. d) Use the total costs of $100,000, $40,000, $1,500,000, and $800,000 given in the problem for departments B, C, X, and Y, respectively, to fill in the row Budgeted costs before allocations. Next, use percentages from your Original Data Section to calculate the allocations of Dept. A and Dept. B costs. Continue to add rows for iterations of cost allocations until the costs remaining to be allocated are near zero. When done, total the amounts in the columns to come up with Total budgeted costs of operating departments. Note: If in doubt about the format, look at Exhibit 16-6 in the textbook. 2. Determine the allocation of costs of support departments B and C to operating departments X and Y through the reciprocal method through the following steps i) simultaneous equations to find complete reciprocated costs of the support departments and ii) allocating the complete reciprocated costs of the support departments to the other departments. Along with your completed worksheets, turn in your Answer Report from Excel Solver. The following are Excel spreadsheet instructions to follow in answering question 2: a) To solve the system of simultaneous equations, you need to first set up the system of simultaneous equations. For assistance on this, see page 654 in the textbook and the example using the reciprocal method from the Chapter 16 lecture. b) To solve the system of simultaneous equations, you will need to use Excel Solver, which is an Add-in in Excel. To add Excel Solver on to your computer, see the instructions at the end of this this assignment. c) In using Excel Solver, it is best to set up the system of simultaneous equations with the constants isolated to the right hand side. In our Chapter 16 class example (not this problem), the system of simultaneous equations would be set up as follows: A 0.2M = $12,000 M 0.16667A = $24,000 d) Go to the worksheet entitled Simult. Under the column Constants, key in the numbers from the right hand side of the equations you set up (see part c). Under the column Solutions, key in any number that is not really large or really small. Under the column Equations, key in the equations on the left hand side of the equal signs using cell reference formulas for the variables. For guidance, see the Excel Solver spreadsheet I have posted for the Chapter 16 class example. e) While in the Simult worksheet, click on Data and then Solver in the Excel menu. This will give you the Solver Parameters dialog box. You only need to deal with the By Changing Variable Cells and Subject to the Constraints sections. In the By Changing Variable Cells section, reference the cells in the Solutions column. In the Subject to the Constraints section, set the cells in the Equations column equal to the cells in the Constraints column. For guidance, see the Excel Solver spreadsheet I have posted for the Chapter 16 class example. f) Press the Solve button at the bottom the dialog box. If the solution is acceptable, in the Solver Results dialog box, click on the Keep Solver Solution button, highlight Answer report, and click on OK. g) Now go the Recip worksheet. On top, create the Original Data section the same as the one in the top of the Iteration worksheet. h) Use the template in the Recip worksheet to allocate the complete reciprocated costs you found in the Simult worksheet. See Exhibit 16-7 in the textbook for guidance. Use cell reference formulas for the allocations. Also, use worksheet-cell reference formulas to refer to the complete reciprocated costs from the Simult worksheet as you allocate to departments. i) The Total budgeted costs of operating departments you ultimately find should be the same in question 2 as in question 1.