how to do this

File Home Insert Draw Page Layout Formulas Data Review View Help PDF Pro 10 XLSTAT K7 A D F G H B E Wharton Associates is a recently formed law partnership. Wharton Associates operates at capacity and uses a cost-based approach to pricing (billing) each job. Currently it uses a simple costing system with a single direct-cost category (professional labor-hours) and a single indirect-cost pool (general support). Indirect costs are allocated to cases on the basis of professional labor-hours per case. The job files for two of Wharton's clients, Steger Enterprises and Bluestone, Inc., can be found on the "JOBFILES" tab. 4 5 Denise Peyton, the managing partner of Wharton Associates, asks her assistant to collect details on those costs included in the $500,000 indirect-cost pool that can be traced to each individual job (see "JOBFILES" tab for details). After analysis, Wharton is able to reclassify $300,000 of the $500,000 as direct costs. The direct costs can be found 6 on the "JOBFILES" tab. 7 Peyton decides to calculate the costs of each job as if Wharton had used six direct-cost pools and a single indirect-cost pool. The single indirect-cost pool would have $200,000 of costs and would be allocated to each case using the professional labor-hours base. 8 9 10 Use the blue shaded areas on the ENTERANSWERS tab for inputs. Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from 11 the Instruction tab you will be marked wrong. 12 13 Requirements 1 Calculate the revised indirect-cost allocation rate per professional labor-hour for Wharton Associates when total indirect costs are $200,000 Compute the costs of the Steger Enterprises and Bluestone Inc. jobs if Wharton Associates had used its refined costing system with multiple direct-cost categories INSTRUCTIONS JOBFILES ENTERANSWERS 14 2 A2 The job files for two of Wharton's clients, Steger Enterprises and Blu D E F A 1 **DATA SHEET -- DO NOT ENTER ANSWERS HERE** The job files for two of Wharton's clients, Steger Enterprises and 2 Blueston Inc., show the following: 3 4 Steger Enterprises Bluestone Inc. 5 Professional labor hours 3,000 2,000 6 Professional labor costs at Wharton Associates are $160 an hour. Indirect costs are allocated to cases at $100 an hour. Total indirect 7 costs in the most recent period were $500,000. 8 9 Total cost of the jobs using the simple costing system are as follows: 10 11 Steger Enterprises Bluestone Inc. 12 Direct professional labor $480,000 $320,000 13 Indirect costs allocated 300,000 200,000 14 Total $780,000 $520,000 15 16 17 Other Direct Costs Steger Enterprises Bluestone Inc. 18 Research support labor $36,000 $77,000 19 Computer time 8,000 32,000 20 Travel and allowances 14,000 84,000 21 Telephone/faxes 5,000 24,000 22 Photocopying 6,000 14,000 23 Total $69,000 $231,000 24 INSTRUCTIONS JOBFILES ENTERANSWERS + A C D E F 1. Calculate the revised indirect-cost allocation rate per professional labor-hour for Wharton Associates when total indirect costs are $200,000. (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the e Instruction tab you will be marked wrong.) 3 4 5 per professional labor hour 2. Compute the costs of the Steger Enterprises and Bluestone Inc. jobs if Wharton Associates had used its 6 refined costing system with multiple direct-cost categories and one indirect-cost pool. (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the 7 Instruction tab you will be marked wrong.) 8 9 Steger Enterprises Bluestone Inc. 10 Direct costs: 11 Direct professional labor 12 Research support labor 13 Computer time 14 Travel and allowances 15 Telephone/faxes 16 Photocopying 17 Total direct costs 18 Indirect costs allocated 19 Total costs of job 20 21 22 23