Can I have some help with an excel file

-Exhibit 1 (tab 1)

Calculate the overhead application rate using the traditional method, using the Exhibit 1 Budget sheet #1 traditional overhead rate columns.

-Exhibit 4 (tab 2)

A) show the total costs charged to each of the five clients for the month of January using the traditional cost allocation method

B) Use the overhead application rate you calculated in step 2. Then determine the amount of revenue billed to each client.

C) determine the profitability by client for the month of January using the traditional cost allocation method.

Clark, Long, Winters and Smith. partner total hours worked total billable hours Budgeted Income Statement For the year ending 2016 Revenues Fees from clients Less expenses Partner's compensation Paralegal compensation Office staff expenses Other office expenses Library and research support Travel expenses Telephone Photocopying Rent Heat Taxes Insurance Accounting Total expenses Operating profit $10,000,000.00 $4,000,000.00 $800,000.00 $120,000.00 $72,000.00 $90,000.00 $100,000.00 $20,000.00 $10,000.00 $24,000.00 $6,000.00 $18,000.00 $15,000.00 $5,000.00 $5,280,000.00 $4,720,000.00 ner total hours worked l billable hours 2000 percent billable Traditional Job Costing per Partner Hour Direct Cost Professional Compensation Overhead Costs Partner's Compensation Paralegal compensation Office staff expenses Other office expenses Library and research support Travel expenses Telephone Photocopying Rent Heat Taxes Insurance Accounting Total Overhead Overhead Rate per Productive hour 80% paralegal total hours worked total billable hours r Hour cost per hour percent billable hours Exhibit 4 Traditional Costing Compared with ABC COSTS ASSIGNED TO CLIENTS FOR THE MONTH OF JANUARY TRADITIONAL COSTING CLIENT A B C D E ACTIVITY-BASED COSTING client A B C D E RATE PER HOUR DIRECT PROFESSIONAL TIME NUMBER OF HOURS PROFESSIONAL TIME COST POOL DATA TO ASSIGN COSTS ASSOCIATED WITH ACTIVITY-BASED COSTING ALLOCATIONS Cost Driver Rate Traditional cost Partners charge rate is per hour Traditional cost Paralegal charge rate is per hour ABC cost Partners charge rate is per hour ABC cost Paralegal charge rate is per hour Paralegal hours by major client A 100 B 250 C 400 D 750 E 300 Total for month 1800 Travel associated with clients A B C D E Total travel Library and research support A B $ $ $ $ $ $ 3,000.00 400.00 2,500.00 2,100.00 8,000.00 HOURS SPENT 10 15 Telephone C D E Total hours spent 0 25 22 72 A B C D E Total hours spent 2 5 10 15 10 42 Photocopying A B C D E 3 10 5 10 12 40 mpared with ABC R THE MONTH OF JANUARY SIONAL TIME TOTAL Job Order Cost Milestone 1 OVERHEAD BILLED TO CLIENT RATE PER HOUR NUMBER OF HOURS TOTAL PARALEGAL TIME COST POOL Partners Bill rate is Paralegal bill rate is A B C D E Total for month Partners hours by major cleint 35 120 90 210 70 525 Activity Based Cost Analysis Milestone 2 TRAVEL LIBRARY per hour per hour t Milestone 1 TOTAL COST ALLOCATED TO CLIENTProfessional Bill Analysis Milestone 2 TELEPHONE PHOTOCOPYING BILL TO CLIENT Paralegal Bill AMOUNT BILLED TO CLIENT TOTAL COSTS ALLOCATED TO CLIENT Profit AMOUNT BILLED TO CLIENTPROFIT COSTS ASSIGNED TO COST POOLS e Professional compensation COST $ 4,000,000 Paralegal compensation $ 800,000 Travel $ 100,000 Library and research support $ 90,000 Telephone Office salaries Other office expenses telephone $ $ $ 60,000 28,800 20,000 Photocopying Office salaries Other office expenses Photocopying expenses $ $ $ 60,000 43,200 10,000 General Overhead Rent Heat Taxes Insurance Accounting $ $ $ $ $ 24,000 6,000 18,000 15,000 5,000 ALLOCATION BASES FOR COST POOLS DIRECT COSTS Professional compensation ANNUAL BUDGET ACTIVITY BASES PARNERS' CHARGABLE HOURS Paralegal compenation PARALEGAL CHARGABLE HOURS Travel ACTUAL DOLLARS SPENT Direct expenses Office salaries Library and research support Telephone Office salaries Other office expenses telephone Total telephone Photocopying Office salaries Other office expenses Photocopying expenses Total photocopying ALLOCATED INDIRECT COSTS General Overhead Rent Heat Taxes Insurance Accounting Total general overhead TIME SPENT ON RESEARCH TIME SPENT ON PHONE WITH CLIEN TIME SPENT ON PHOTOCOPYING Square feet associated with activity see below Total expenses ALLOCATED EXPENSES - OFFICE SALARIES AND OVERHEAD For office salaries, the time spent on the 3 cost pools associated with office salaries is as follows Travel 10% Telephone 40% Photocopying 50% For overhead costs, the square feet associated with the 6 cost pools is as follows; Professional staf 1000 Paralegal staf 500 Travel 100 Library and Research Staf Telephone Photocopying 150 100 150 CHARGABLE HOURS AL CHARGABLE HOURS Exhibit 3 Activity Bases and Estimated Activity for the Year ESTIMATED ACTIVITY FOR CURRENT YEAR Billable PROFESSIONAL HOURS Billable PARALEGAL HOURS RATE PER HOUR #DIV/0! #DIV/0! OLLARS SPENT DOLLARS NT ON RESEARCH Billable LIBRARY STAFF HOURS #DIV/0! NT ON PHONE WITH CLIENTS Billable OFFICE STAFF HOURS #DIV/0! NT ON PHOTOCOPYING Billable OFFICE STAFF HOURS #DIV/0! et associated with activity ries is as follows xhibit 5 ACTIVITY-BASED COSTING client DIRECT PROFESSION TIME DATA TO ASSIGN COSTS ASSOCIATED WITH ACTIVITY-BASED COSTING ALLOCATIONS Cost Driver Rate Traditional cost Partners charge rate is per hour Traditional cost Paralegal charge rate is per hour ABC cost Partners charge rate is per hour ABC cost Paralegal charge rate is per hour Paralegal hours by major client A 100 A B 250 B C 400 C D 750 D E 300 E Total for mon 1800 Total for mon Exhibit 5 Activity Based Cost Analysis Milestone 2 DIRECT PARALEGAL TIME Partners Bill rate is Paralegal bill rate is per hour per hour Comparison of Partners hours by major cleint 35 120 90 210 70 525 Client A B C D E is Milestone 2 TOTAL COSTS ALLOCATED TO CLIENT AMOUNT BILLED TO CLIENTPROFIT 6 Cost Pools $ $ $ $ $ Comparison of Profits Less than 6 cost pools - $ - $ - $ - $ - $ - Variation 0 0 0 0 0 Problem: In past years, the overhead application rate has been calculated by allocating the projected overhead expenses divided by the billable hours of the four partners. Typically, partners work 2,000 hours a year and charge 80% of their time to clients. The rest of their time is associated with professional development seminars, community presentations, and charity work. first task is to develop the overhead application rate currently used to bill a client. The firm currently has five very large clients and several smaller clients. use traditional job-order costing to determine the costs associated with five of the law firm's biggest clients. the firm tracks the following direct costs: 1. Professional partner compensation - partners are paid $500 an hour and are billed to the client at $800 per hour. The four partners work 2,000 hours a year and are able to charge 80% of their time to clients. Thus, some of the professional labor costs are assigned to overhead. Currently, the firm's accounting system only treats professional hours as direct. 2. Paralegal compensation- paralegals paid $40 per hour and are billed to the client at $200 per hour. 3. The firm treats all of the following as indirect costs: 1. General office support, the office 2. Library and research support 3. Travel expenses 4. Telephone for making appointments and responding to clients 5. Photocopying 6. Rent, heat, property taxes, insurance, and accounting services Currently, the firm's accounting system only treats professional hours as direct. All other costs, such as the paralegal costs are treated as overhead. Determine the overhead rate per professional direct labor hour. All of the paralegal labor is considered overhead. Excel File: - Exhibit 1 (tab 1) Calculate the overhead application rate using the traditional method, using the Exhibit 1 Budget sheet #1 \"traditional overhead rate\" columns. - Exhibit 4 (tab 2) A) show the total costs charged to each of the five clients for the month of January using the traditional cost allocation method B) Use the overhead application rate you calculated in step 2. Then determine the amount of revenue billed to each client. C) determine the profitability by client for the month of January using the traditional cost allocation method