ExcelAnalytics_JobOrderCosting_PlantwidePredeterminedOverheadRates_Template

xlsx

School

Northeastern University *

*We aren’t endorsed by this school

Course

3000

Subject

Finance

Date

Apr 3, 2024

Type

xlsx

Pages

Uploaded by AgentMorningOwl40

Pivot Tables: Pivot tables allow us to summarize data sets quickly and easily agnostic of size. The table allows us to summa specific attributes and using specific measures such as sum, average, maximum and so on. When utilizing Pivot Tables you should proceed as follows: Go to the “Insert” tab Click on “PivotTable” and the “Create PivotTable” Wizard will pop up: Under “Choose the data that you want to analyze” make sure the radio button for “Select a table or range” is Ensure the Table/Range is set as the entire range of cells you want to summarize Under “Choose where you want the PivotTable report to be placed” choose the radio button for “New Works Leave the box unchecked next to “Add this data to the Data Model” Click OK Within the “PivotTable Fields” on the right of the screen, do the following actions: Click on and drag any attribute (typically non-numerical) you want to summarize by such as an account name to the “Rows” quadrant Click on and drag the numerical value such as sales or quantity to the “Values” quadrant Note that this will default to “Sum” of whatever value you chose. If you want to have a measure other than s should click on the drop down of that value in the “Values” quadrant and click “Value field settings” to modify summarization type. Your table will now show you a summarization of the numerical value in correspondence with the non-numer you chose such as sales by state, If you want to add additional dimensions such as the Year you can drag those values to the “Columns” quadra you will have a cross sectional summarization such as sales by year by state.

arize on a selected sheet” e or a location sum, you y the rical attribute ant and now

VLOOKUPs: VLOOKUP is a function in Microsoft Excel that easily allows you to search tables or sheets within Excel workbo specified corresponding values. Below is a description of the Excel formula and an example of how to use it: 1. The first step is to understand the VLOOKUP function itself. The VLOOKUP Function has the following argu 2. =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup] The “lookup_value” is the cell that contains the value you want to search for in another table The “table_array” is the table where you want to look for the “lookup_value” It is very important that the leftmost column in the “table_array” is the column that would co “lookup_value” you are looking for The “col_index_num, is the column number that contains the corresponding value you want to return, for exa input 1, it will give you the column you are searching in for the “lookup_value”, if you choose 2 it will give you directly to the right of the column that contains the “lookup value” and so on. “range_lookup” has to options “True” or “False”. If you want an exact match (which you typically do, choose are looking only for an approximate match, choose “True”. 3. Lets do the following example (note this is simplified for ease of following)You want to have a lookup functi can type in any state and have it return the sales for that state. You have a simple table that lists the sta company operates in and the corresponding sales. The below table is your setup where in you want to name into Cell E2 and have Cell F2 generate the sales for that state: To do this our VLOOKUP function is will go into Cell F2 and will look as follows: 1. =VLOOKUP(E2,$A$1:$B$7,2,FALSE) a) The “lookup_value” is cell E2 as it contains the value (State) you want to search for table b) The “table_array” is $A$1:$B$7 as this is the table where we want to search for th “lookup_value” (State) c) The “col_index_num is 2 as we are searching the first column but want to return th in the 2 nd column (Sales) d) “range_lookup” is set to “False” as we want to match the state exactly

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

ooks and return uments: ontain the ample if you u the column “False” if you tion where you ates your o type the state r in another he he value found

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Requisition # Department Job Cost MR1 Molding 1 $ 175.00 MR17 Fabrication 1 $ 198.00 MR33 Assembly 1 $ 169.00 MR4 Molding 2 $ 305.00 MR13 Fabrication 2 $ 140.00 MR30 Assembly 2 $ 115.00 MR2 Molding 3 $ 188.00 MR18 Fabrication 3 $ 134.00 MR6 Molding 4 $ 168.00 MR14 Fabrication 4 $ 138.00 MR25 Assembly 4 $ 118.00 MR7 Molding 5 $ 170.00 $ 306.00 MR20 Fabrication 5 $ 136.00 MR11 Molding 6 $ 185.00 MR15 Fabrication 6 $ 135.00 MR31 Assembly 6 $ 112.00 MR12 Molding 7 $ 144.00 MR21 Fabrication 7 $ 105.00 MR26 Assembly 7 $ 80.00 MR3 Molding 8 $ 155.00 MR23 Fabrication 8 $ 132.00 MR10 Molding 9 $ 192.00 MR22 Fabrication 9 $ 131.00 MR27 Assembly 9 $ 112.00 MR9 Molding 10 $ 148.00 MR24 Fabrication 10 $ 119.00 MR32 Assembly 10 $ 114.00 MR5 Molding 11 $ 160.00 $ 402.00 MR16 Fabrication 11 $ 129.00 MR29 Assembly 11 $ 113.00 MR8 Molding 12 $ 210.00 MR19 Fabrication 12 $ 142.00 MR28 Assembly 12 $ 116.00

Employee Department Job Cathy Smithson Assembly 1 2.50 $ 20 $ 50 Christopher Tanaka Assembly 1 1.50 $ 20 $ 30 Damien Charles Fabrication 1 4.50 $ 20 $ 90 Juwan Thompson Molding 1 3.25 $ 20 $ 65 Rebecca Wills Assembly 1 3.25 $ 20 $ 65 Ernest Kroyman Assembly 2 15.00 $ 20 $ 300 Jeff Durand Molding 2 10.50 $ 20 $ 210 Loretta Hoyt Assembly 2 13.50 $ 20 $ 270 Mary Jo Hawley Fabrication 2 11.25 $ 20 $ 225 Rebecca Wills Assembly 2 12.75 $ 20 $ 255 Damien Charles Fabrication 3 26.50 $ 20 $ 530 2,500.00 Emily Evans Assembly 3 30.00 $ 20 $ 600 Ernest Kroyman Assembly 3 31.00 $ 20 $ 620 Juwan Thompson Molding 3 22.00 $ 20 $ 440 William Marks Assembly 3 15.50 $ 20 $ 310 Cathy Smithson Assembly 4 0.75 $ 20 $ 15 David Chen Molding 4 1.00 $ 20 $ 20 Michael Kershaw Fabrication 4 1.00 $ 20 $ 20 William Marks Assembly 4 1.00 $ 20 $ 20 Jeff Durand Molding 5 2.75 $ 20 $ 55 $ 340 Julia Rhymer Assembly 5 6.50 $ 20 $ 130 Kayla Burke Assembly 5 3.25 $ 20 $ 65 Madison Rodriquez Fabrication 5 4.50 $ 20 $ 90 Damien Charles Fabrication 6 11.00 $ 20 $ 220 David Chen Molding 6 12.00 $ 20 $ 240 Emily Evans Assembly 6 20.50 $ 20 $ 410 Loretta Hoyt Assembly 6 18.50 $ 20 $ 370 Rebecca Wills Assembly 6 26.00 $ 20 $ 520 Cathy Smithson Assembly 7 2.25 $ 20 $ 45 Christopher Tanaka Assembly 7 2.50 $ 20 $ 50 David Chen Molding 7 2.25 $ 20 $ 45 Madison Rodriquez Fabrication 7 3.25 $ 20 $ 65 William Marks Assembly 7 1.25 $ 20 $ 25 Cathy Smithson Assembly 8 3.50 $ 20 $ 70 Christopher Tanaka Assembly 8 3.75 $ 20 $ 75 Marshall Toliver Molding 8 0.75 $ 20 $ 15 Michael Kershaw Fabrication 8 4.00 $ 20 $ 80 Rebecca Wills Assembly 8 3.50 $ 20 $ 70 Damien Charles Fabrication 9 27.00 $ 20 $ 540 Juwan Thompson Molding 9 25.00 $ 20 $ 500 Loretta Hoyt Assembly 9 21.00 $ 20 $ 420 Rebecca Wills Assembly 9 32.00 $ 20 $ 640 42.00 Direct Labor- Hours Average Direct Labor Wage Rate Direct Labor Cost

Allison Vanston Assembly 10 17.00 $ 20 $ 340 Jeff Durand Molding 10 10.00 $ 20 $ 200 $ 840 Mary Jo Hawley Fabrication 10 8.00 $ 20 $ 160 Rebecca Wills Assembly 10 7.00 $ 20 $ 140 Emily Evans Assembly 11 8.00 $ 20 $ 160 $ 600 Julia Rhymer Assembly 11 1.00 $ 20 $ 20 Loretta Hoyt Assembly 11 4.00 $ 20 $ 80 Marshall Toliver Molding 11 6.00 $ 20 $ 120 Mary Jo Hawley Fabrication 11 11.00 $ 20 $ 220 Allison Vanston Assembly 12 3.25 $ 20 $ 65 Emily Evans Assembly 12 5.00 $ 20 $ 100 Ernest Kroyman Assembly 12 3.75 $ 20 $ 75 Jeff Durand Molding 12 4.00 $ 20 $ 80 Mary Jo Hawley Fabrication 12 6.00 $ 20 $ 120

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

125.00

42.00

Last Year This Account Cost Behavior Equipment depreciation: Molding $ 400,000 Fixed $ 400,000 Equipment depreciation: Fabrication $ 300,000 Fixed $ 300,000 Equipment depreciation: Assembly $ 50,000 Fixed $ 50,000 Equipment depreciation: Materials Handling $ 15,000 Fixed $ 15,000 Equipment depreciation: Other $ 10,000 Fixed $ 10,000 Production suprvision: Molding $ 60,000 Fixed $ 63,000 Production supervision: Fabrication $ 65,000 Fixed $ 68,250 Production supervision: Assembly $ 58,000 Fixed $ 60,900 Indirect labor: Maintenance & Repairs $ 72,000 Fixed $ 75,600 Indirect labor: Materials Handling $ 68,000 Fixed $ 71,400 Indirect labor: Engineering $ 170,000 Fixed $ 178,500 Indirect labor: Raw Materials Purchasing $ 93,000 Fixed $ 97,650 Indirect labor: Quality Control $ 38,000 Fixed $ 39,900 Indirect labor: Packaging & Shipping $ 99,000 Fixed $ 103,950 Indirect materials: Molding $ 18,000 Variable $ 18,000 Indirect materials: Fabrication $ 13,000 Variable $ 13,000 Indirect materials: Assembly $ 8,000 Variable $ 8,000 Utilities: Molding $ 24,000 Variable $ 24,000 Utilities: Fabrication $ 20,000 Variable $ 20,000 Utilities: Assembly $ 6,000 Variable $ 6,000 Utiltiies: Plant lighting and other equipment $ 12,000 Fixed $ 12,240 Utilities: Heating and cooling $ 24,000 Fixed $ 24,480 Property insurance $ 26,000 Fixed $ 26,520 Liability insurance $ 18,000 Fixed $ 18,360 Medical insurance $ 45,000 Fixed $ 51,750 Workers' compensation insurance $ 13,000 Fixed $ 14,300 Other salaries: Plant manager $ 165,000 Fixed $ 173,250 Other salaries: Accounting $ 89,000 Fixed $ 93,450 Other salaries: Administrative support $ 56,000 Fixed $ 58,800 Other salaries: Janitorial $ 48,000 Fixed $ 50,400 Other salaries: Security and grounds maintenance $ 44,000 Fixed $ 46,200 Property taxes $ 18,000 Fixed $ 18,540 Payroll taxes: Direct labor $ 79,000 Variable Payroll taxes: Other $ 165,000 Fixed $ 173,250 Total Manufacturing Overhead $ 2,389,000 $ 2,384,690 Total direct labor hours 41,400 42,500 Plantwide predetermined overhead rate Actual Amount Estimated Amount

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Year Cost Behavior Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Variable Variable Variable Variable Variable Variable Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Variable Fixed

Job Sales Direct Labor Total Job Cost Gross Margin 1 $ 2,400.00 $ 542.00 $ 300.00 $ 871.05 $ 1,713.05 $ 686.95 2 $ 5,400.00 $ 560.00 $ 3,658.41 $ 4,218.41 $ 1,181.59 3 $ 9,000.00 $ 322.00 $ 2,500.00 $ 7,258.75 $ 10,080.75 $ (1,080.75) 4 $ 1,450.00 $ 424.00 $ 75.00 $ 217.76 $ 716.76 $ 733.24 5 $ 2,200.00 $ - $ 2,200.00 6 $ 7,000.00 $ - $ 7,000.00 7 $ 1,700.00 $ - $ 1,700.00 8 $ 1,600.00 $ 287.00 $ 310.00 $ 900.09 $ 1,497.09 $ 102.91 9 $ 8,000.00 $ 435.00 $ 2,100.00 $ 6,097.35 $ 8,632.35 $ (632.35) 10 $ 4,100.00 $ 381.00 $ 840.00 $ 2,438.94 $ 3,659.94 $ 440.06 11 $ 3,200.00 $ - $ 3,200.00 12 $ 2,800.00 $ 468.00 $ 440.00 $ 1,277.54 $ 2,185.54 $ 614.46 Direct Materials Applied Overhead

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version