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