For this project you will account for a factorys costs for one month in a job costing system

There are four tabs on the file:

Overhead Budget

Job Cost Sheets

Actual Overhead Costs

Questions to Answer

Only use the green shaded cells

Update the following information for the monthly budget:

Estimated Machine hours per month: 2,500 Machine Hours

Estimated Variable Cost Per Machine Hour: $6.50/MH

Factory Rent $10,000

Factory Utilities $6,500

Supervision and Support

Service Salaries $48,000

Equipment Depreciation $13,000

Calculate the Predetermined Overhead rate. This rate will then transfer to the next page, the job cost sheets.

The next Tab has three Job Cost sheets with job numbers along with a description of the amount to be produced for three different products. I have left some data in cells to help you. Note, all the cells are formatted so you only have to put in the number.

The third tab is the actual overhead costs. As the information is available per week, post the overhead expenditures to the appropriate cells. For example, rent is only paid once in week 1. There will be supervision and support service salaries each week.

There are four weeks of data. Update the data, where appropriate, to either the Job Cost Sheets or the third tab, the Actual Overhead Costs. All data is by week.

When completed, go to the fourth tab, Questions to Answer and answer the questions on the results for the month of producing the three jobs.

In Week 1 the firm paid Rent on the factory totaling $10,000. The summary of all material requisitions for Week 1: Buckeye Office Furniture Material Requisition Summary Week 1 Charge to: Job # 211-21 $ Job # 211-22 $ Job # 211-23 $ Indirect Materials Total Materials Requisitions for week $ 7,890.00 2,800.00 450.00 3,620.00 14,760.00 The summary of all wages and salaries paid, including direct labor: Buckeye Office Furniture Weekly Payroll Report Week 1 Hours Wages Time Charged to: Job # 211-21 85.0 hours $ 1,670.00 Job # 211-22 125.5 hours $ 3,600.00 Job # 211-23 80.0 hours $ 2,600.00 Supervision and Support Service Salaries $ 12,500.00 Total Payroll $ 20,370.00 The Report of all machine hours for Week 1: Buckeye Office Furniture Weekly Machine Hour Report Week 1 Job Number: Job # 211-21 247.5 Machine Hours Job # 211-22 174.0 Machine Hours Job # 211-23 98.0 Machine Hours Total Machine Hours 519.5 Machine Hours In Week 2 the firm did not incur any additional cost other than the following: The summary of all material requisitions for Week 2: Buckeye Office Furniture Material Requisition Summary Week 2 Charge to: Job # 211-21 $ Job # 211-22 $ Job # 211-23 Indirect Materials Total Materials Requisitions for week $ 4,800.00 3,700.00 1,200.00 5,100.00 14,800.00 The summary of all wages and salaries paid, including direct labor: Buckeye Office Furniture Weekly Payroll Report Week 2 Hours Wages Time Charged to: Job # 211-21 74.5 hours $ 2,200.00 Job # 211-22 92.5 hours $ 2,800.00 Job # 211-23 110.0 hours $ 3,300.00 Supervision and Support Service Salaries $ 11,900.00 Total Payroll $ 20,200.00 The Report of all machine hours for Week 2: Buckeye Office Furniture Weekly Machine Hour Report Week 2 Job Number Job # 211-21 400.0 Machine Hours Job # 211-22 198.0 Machine Hours Job # 211-23 110.5 Machine Hours Total Machine Hours 708.5 Machine Hours In Week 3 the firm did not incur any additional cost other than the following: The summary of all material requisitions for Week 3: Buckeye Office Furniture Material Requisition Summary Week 3 Charge to: Job # 211-21 $ Job # 211-22 $ Job # 211-23 Indirect Materials Total Materials Requisitions for week $ 1,200.00 5,100.00 2,300.00 3,700.00 12,300.00 The summary of all wages and salaries paid, including direct labor: Buckeye Office Furniture Weekly Payroll Report Week 3 Hours Wages Time Charged to: Job # 211-21 60.0 hours $ 1,700.00 Job # 211-22 45.0 hours $ 1,250.00 Job # 211-23 145.0 hours $ 4,200.00 Supervision and Support Service Salaries $ 12,600.00 Total Payroll $ 19,750.00 The Report of all machine hours for Week 3: Buckeye Office Furniture Weekly Machine Hour Report Week 3 Job Number: Job # 211-21 121.0 Machine Hours Job # 211-22 320.5 Machine Hours Job # 211-23 140.5 Machine Hours Total Machine Hours 582.0 Machine Hours For Week 4, the firm completed work on all three jobs. In addition to the usual costs, in Week 4 the firm also incurred the following costs: O A factory utility bill for the month was received and paid. The total bill was $6,650. The firm also recognized depreciation on factory equipment for the month totaling $13,000. The summary of all material requisitions for Week 4: Buckeye Office Furniture Material Requisition Summary Week 4 Charge to: Job # 211-21 $ Job # 211-22 $ Job # 211-23 $ Indirect Materials $ Total Materials Requisitions for week $ 400.00 600.00 2,500.00 3,200.00 6,700.00 The summary of all wages and salaries paid, including direct labor: Hours Buckeye Office Furniture Weekly Payroll Report Week 4 Wages Time Charged to: Job # 211-21 50.0 hours $ 1,600.00 Job # 211-22 30.0 hours $ 900.00 Job # 211-23 170.0 hours $ 3,100.00 Supervision and Support Service Salaries $ 11,900.00 Total Payroll $ 20,200.00 The Report of all machine hours for Week 4: Buckeye Office Furniture Weekly Machine Hour Report Week 4 Job Number: Job #211-21 94.5 Machine Hours Job # 211-22 110.0 Machine Hours Job # 211-23 311.0 Machine Hours Total Machine Hours 515.5 Machine Hours Screenshot saved The screenshot was added to your OneDrive OneDrive File Home Insert Draw Page Layout Formulas Data Review View Help Share 0 Comments X Insert = Calibri ~ A ~ A A 12 Custom OM 47 0 4 Paste BIU OnA += = @ y $ -% 8-28 DX Delete E Format Ideas Sensitivity Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Editing Clipboard Font Alignment Number Ideas Sensitivity B21 fax A B C D E F G H 1 1 Buckeye Office Furniture Overhead Budget Estimated Total Costs $ 10,000.00 $ 10,000.00 2 3 4 Estimated Machine hours per month: 5 Estimated Variable Cost Per Machine Hour 6 7 8 9 Estimated Fixed Overhead Costs 10 Factory Rent 11 Factory Utilities 12 Supervision and Support Service Salaries 13 Equipment Depreciation 14 Estimated Total Fixed Overhead Costs 13 15 16 Estimated Variable Overhead costs. 17 Estimated Total Indirect Material Cost 18 Estimated Total Overhead cost (Fixed and Variable 19 20 21 Predetermined Overhead Rate: 22 23 24 25 26 27 28 29 Overhead Budget Job Cost Sheets Actual Overhead Costs $ $ 10,000.00 Questions to Answer + m 1 + 100% File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments Comments Insert AA Calibri == 12 ab General 4. y Paste BTU A Ideas $ -% 888 DX Delete It Format Conditional Format as Cell Formatting Table Styles Syles Sensitivity Sort & Find & Filter Select Editing Clipboard Font Alignment Number Cells Sensitivity 021 fax 5 16 IL Buckeye Office Furniture Job Cast Sheet 4 Job Number 5 Desertion 201-21 LOUW Direct meste Direct hoe W Wir 4 10. 1 10 Manuten wa SIW 50W SI MN 35W 15 T9 th Cost Summary L' DI TWAWI 3 7.00 Required Hours Total Direct Lahore AM 14 20 Teile Cory $ 7.2005 3 Buckeye Office Furniture Job Cost Sheet 201-22 2003 Job Number 2: Dexertion Direct me 1 Wir Direct we Mantinos Merker W laban sa Wt 1 SW wok2 223 2.00000 Luas - 22 . 59 4 35 BSW 3 WOS Cost Summary 9 DTIWI annual harles 4: Tech Cost Required Hours Total Direct Lehin 20. hon hadis AN savo Buckeye Office Furniture Job Cost Sheet 16 - Job Number Dexrption 12.000 who 51 Direct mat Direct whe MROures Wr Las Wya Local wa IT LUIS VII SON M 1415VH SIWE 15 3 w Cost Summary SL O TEMI us wiedzieland Telack Cool Required Hours Total Dret Lahoru nad VldHRAN Overhead Budget Job Cost Sheets Actual Overhead Costs Questions to Answer # E -- + 40% File Home - Insert Draw Page Layout Formulas Data Review View Help Share Comments Calibri 12 AA == al Accounting LM W 47 4 Y Insert DX Delete # Format Paste BIU , 4 A , $ -% 583 Ideas Sensitivity Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Clipboard Font Alignment Number Cells Editing Ideas Sensitivity F12 X fx A B C D E F G H H 1 K - 1 Buckeye Office Furniture Actual Overhead Costs Week 2 2 Week 1 Overhead costs $ 10,000.00 Overhead Costs Week 3 Overhead Costs Week 4 Overhead costs Total Overehead Costs $ 10,000.00 6,650.00 12,500.00 6,650.00 12,500.00 3 Fixed Overhead Costs 4 Factory Rent 5 Factory Utilities 6 Supervision and Support Service Salaries 7 Equipment Depreciation 8 Total Fixed Overhead Costs 9 10 Variable Overhead costs: 11 Total Indirect Material Costs 12 13 Total Overhead Costs $ 22,500.00 $ $ $ 6,650.00 $ 29,150.00 $ $ 22,500.00 $ $ 6,650.00 $ 29,150.00 14 15 16 17 18 19 20 21 22 27 23 24 25 26 27 Overhead Budget Job Cost Sheets Actual Overhead Costs Questions to Answer + 1 + 100% File Home Insert Draw Page Layout Formulas Data Review View Help Share 0 Comments ra Insert X LO 12 Calibri - A 2 General ITI 47 Delete Paste Ideas BIU A E B $ %_ Sensitivity y Format Conditional Format as Cell Formatting Table Table Styles Styles Sart & Find & Filter Select Editing Clipboard Font Alignment Number Cells Ideas Sensitivity D3 fort V A B C D E E F G 1 2 Answer 3 3 4 5 6 Question Qi What is the cost per chair? Q2 What is the cost per desk? Q3 What is the cost per end table? Q4 What is the total estimated overhead cost? Q5 What is the total applied overhead cost? Q6 What is the total actual overhead cost? Q7 What is the total direct material cost? Q8 What is the total direct labor cost? 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Overhead Budget Job Cost Sheets Actual Overhead Costs Questions to Answer BE B 100% In Week 1 the firm paid Rent on the factory totaling $10,000. The summary of all material requisitions for Week 1: Buckeye Office Furniture Material Requisition Summary Week 1 Charge to: Job # 211-21 $ Job # 211-22 $ Job # 211-23 $ Indirect Materials Total Materials Requisitions for week $ 7,890.00 2,800.00 450.00 3,620.00 14,760.00 The summary of all wages and salaries paid, including direct labor: Buckeye Office Furniture Weekly Payroll Report Week 1 Hours Wages Time Charged to: Job # 211-21 85.0 hours $ 1,670.00 Job # 211-22 125.5 hours $ 3,600.00 Job # 211-23 80.0 hours $ 2,600.00 Supervision and Support Service Salaries $ 12,500.00 Total Payroll $ 20,370.00 The Report of all machine hours for Week 1: Buckeye Office Furniture Weekly Machine Hour Report Week 1 Job Number: Job # 211-21 247.5 Machine Hours Job # 211-22 174.0 Machine Hours Job # 211-23 98.0 Machine Hours Total Machine Hours 519.5 Machine Hours In Week 2 the firm did not incur any additional cost other than the following: The summary of all material requisitions for Week 2: Buckeye Office Furniture Material Requisition Summary Week 2 Charge to: Job # 211-21 $ Job # 211-22 $ Job # 211-23 Indirect Materials Total Materials Requisitions for week $ 4,800.00 3,700.00 1,200.00 5,100.00 14,800.00 The summary of all wages and salaries paid, including direct labor: Buckeye Office Furniture Weekly Payroll Report Week 2 Hours Wages Time Charged to: Job # 211-21 74.5 hours $ 2,200.00 Job # 211-22 92.5 hours $ 2,800.00 Job # 211-23 110.0 hours $ 3,300.00 Supervision and Support Service Salaries $ 11,900.00 Total Payroll $ 20,200.00 The Report of all machine hours for Week 2: Buckeye Office Furniture Weekly Machine Hour Report Week 2 Job Number Job # 211-21 400.0 Machine Hours Job # 211-22 198.0 Machine Hours Job # 211-23 110.5 Machine Hours Total Machine Hours 708.5 Machine Hours In Week 3 the firm did not incur any additional cost other than the following: The summary of all material requisitions for Week 3: Buckeye Office Furniture Material Requisition Summary Week 3 Charge to: Job # 211-21 $ Job # 211-22 $ Job # 211-23 Indirect Materials Total Materials Requisitions for week $ 1,200.00 5,100.00 2,300.00 3,700.00 12,300.00 The summary of all wages and salaries paid, including direct labor: Buckeye Office Furniture Weekly Payroll Report Week 3 Hours Wages Time Charged to: Job # 211-21 60.0 hours $ 1,700.00 Job # 211-22 45.0 hours $ 1,250.00 Job # 211-23 145.0 hours $ 4,200.00 Supervision and Support Service Salaries $ 12,600.00 Total Payroll $ 19,750.00 The Report of all machine hours for Week 3: Buckeye Office Furniture Weekly Machine Hour Report Week 3 Job Number: Job # 211-21 121.0 Machine Hours Job # 211-22 320.5 Machine Hours Job # 211-23 140.5 Machine Hours Total Machine Hours 582.0 Machine Hours For Week 4, the firm completed work on all three jobs. In addition to the usual costs, in Week 4 the firm also incurred the following costs: O A factory utility bill for the month was received and paid. The total bill was $6,650. The firm also recognized depreciation on factory equipment for the month totaling $13,000. The summary of all material requisitions for Week 4: Buckeye Office Furniture Material Requisition Summary Week 4 Charge to: Job # 211-21 $ Job # 211-22 $ Job # 211-23 $ Indirect Materials $ Total Materials Requisitions for week $ 400.00 600.00 2,500.00 3,200.00 6,700.00 The summary of all wages and salaries paid, including direct labor: Hours Buckeye Office Furniture Weekly Payroll Report Week 4 Wages Time Charged to: Job # 211-21 50.0 hours $ 1,600.00 Job # 211-22 30.0 hours $ 900.00 Job # 211-23 170.0 hours $ 3,100.00 Supervision and Support Service Salaries $ 11,900.00 Total Payroll $ 20,200.00 The Report of all machine hours for Week 4: Buckeye Office Furniture Weekly Machine Hour Report Week 4 Job Number: Job #211-21 94.5 Machine Hours Job # 211-22 110.0 Machine Hours Job # 211-23 311.0 Machine Hours Total Machine Hours 515.5 Machine Hours Screenshot saved The screenshot was added to your OneDrive OneDrive File Home Insert Draw Page Layout Formulas Data Review View Help Share 0 Comments X Insert = Calibri ~ A ~ A A 12 Custom OM 47 0 4 Paste BIU OnA += = @ y $ -% 8-28 DX Delete E Format Ideas Sensitivity Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Editing Clipboard Font Alignment Number Ideas Sensitivity B21 fax A B C D E F G H 1 1 Buckeye Office Furniture Overhead Budget Estimated Total Costs $ 10,000.00 $ 10,000.00 2 3 4 Estimated Machine hours per month: 5 Estimated Variable Cost Per Machine Hour 6 7 8 9 Estimated Fixed Overhead Costs 10 Factory Rent 11 Factory Utilities 12 Supervision and Support Service Salaries 13 Equipment Depreciation 14 Estimated Total Fixed Overhead Costs 13 15 16 Estimated Variable Overhead costs. 17 Estimated Total Indirect Material Cost 18 Estimated Total Overhead cost (Fixed and Variable 19 20 21 Predetermined Overhead Rate: 22 23 24 25 26 27 28 29 Overhead Budget Job Cost Sheets Actual Overhead Costs $ $ 10,000.00 Questions to Answer + m 1 + 100% File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments Comments Insert AA Calibri == 12 ab General 4. y Paste BTU A Ideas $ -% 888 DX Delete It Format Conditional Format as Cell Formatting Table Styles Syles Sensitivity Sort & Find & Filter Select Editing Clipboard Font Alignment Number Cells Sensitivity 021 fax 5 16 IL Buckeye Office Furniture Job Cast Sheet 4 Job Number 5 Desertion 201-21 LOUW Direct meste Direct hoe W Wir 4 10. 1 10 Manuten wa SIW 50W SI MN 35W 15 T9 th Cost Summary L' DI TWAWI 3 7.00 Required Hours Total Direct Lahore AM 14 20 Teile Cory $ 7.2005 3 Buckeye Office Furniture Job Cost Sheet 201-22 2003 Job Number 2: Dexertion Direct me 1 Wir Direct we Mantinos Merker W laban sa Wt 1 SW wok2 223 2.00000 Luas - 22 . 59 4 35 BSW 3 WOS Cost Summary 9 DTIWI annual harles 4: Tech Cost Required Hours Total Direct Lehin 20. hon hadis AN savo Buckeye Office Furniture Job Cost Sheet 16 - Job Number Dexrption 12.000 who 51 Direct mat Direct whe MROures Wr Las Wya Local wa IT LUIS VII SON M 1415VH SIWE 15 3 w Cost Summary SL O TEMI us wiedzieland Telack Cool Required Hours Total Dret Lahoru nad VldHRAN Overhead Budget Job Cost Sheets Actual Overhead Costs Questions to Answer # E -- + 40% File Home - Insert Draw Page Layout Formulas Data Review View Help Share Comments Calibri 12 AA == al Accounting LM W 47 4 Y Insert DX Delete # Format Paste BIU , 4 A , $ -% 583 Ideas Sensitivity Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Clipboard Font Alignment Number Cells Editing Ideas Sensitivity F12 X fx A B C D E F G H H 1 K - 1 Buckeye Office Furniture Actual Overhead Costs Week 2 2 Week 1 Overhead costs $ 10,000.00 Overhead Costs Week 3 Overhead Costs Week 4 Overhead costs Total Overehead Costs $ 10,000.00 6,650.00 12,500.00 6,650.00 12,500.00 3 Fixed Overhead Costs 4 Factory Rent 5 Factory Utilities 6 Supervision and Support Service Salaries 7 Equipment Depreciation 8 Total Fixed Overhead Costs 9 10 Variable Overhead costs: 11 Total Indirect Material Costs 12 13 Total Overhead Costs $ 22,500.00 $ $ $ 6,650.00 $ 29,150.00 $ $ 22,500.00 $ $ 6,650.00 $ 29,150.00 14 15 16 17 18 19 20 21 22 27 23 24 25 26 27 Overhead Budget Job Cost Sheets Actual Overhead Costs Questions to Answer + 1 + 100% File Home Insert Draw Page Layout Formulas Data Review View Help Share 0 Comments ra Insert X LO 12 Calibri - A 2 General ITI 47 Delete Paste Ideas BIU A E B $ %_ Sensitivity y Format Conditional Format as Cell Formatting Table Table Styles Styles Sart & Find & Filter Select Editing Clipboard Font Alignment Number Cells Ideas Sensitivity D3 fort V A B C D E E F G 1 2 Answer 3 3 4 5 6 Question Qi What is the cost per chair? Q2 What is the cost per desk? Q3 What is the cost per end table? Q4 What is the total estimated overhead cost? Q5 What is the total applied overhead cost? Q6 What is the total actual overhead cost? Q7 What is the total direct material cost? Q8 What is the total direct labor cost? 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Overhead Budget Job Cost Sheets Actual Overhead Costs Questions to Answer BE B 100%