Download Excel Data Analysis Techniques: Pivot Tables, What-If Analysis, and Power Pivot and more Exams Computer Communication Systems in PDF only on Docsity!
com s 113 - exam 2
subtotals - correct answer in large data sets you often want to see -- for portions of the data, such as sums for each main category pivot tables - correct answer allow you to easily rearrange data to help you more easily identify trends and patterns. they are useful for extracting meaningful information from the data. very easy, no forumulas, no VBA Macros, just drag & drop pivot charts - correct answer just like normal charts, except they present the data in a pivot table subtotal data - correct answer excel can automatically insert subtotal rows for each category in a sorted range of data. subtotals can be sums, averages, etc. you can show subtotals for subcategories as well. you must enter: "At each change" (where to place the subtotal rows), "use function" (what type of subtotal [sum, avg]), "add subtotal to" (which columns to calculate subtotals for) group data - correct answer you can group rows or columns of related data (which is called an outline in excel). expand or collapse groups depending on what you want to focus on. ex: group column F through H, so you can view all 3 columns or just the "---" column create a pivot table - correct answer data mining (analyzes large volumes of data, uses advanced statistical techniques, identifies trends and patterns). it's excel's interactive data-mining feature. they are dynamic (data can be rearranged, details can be expanded or collapsed, data can be organized and grouped differently, row and column categories can be switched). create it by selecting range, quick analysis gallery, click table, select range. ribbon. clicking 'more' opens the recommended pivottables dialog box. pivot table fields - correct answer choose fields to add to report section (lists all the fields or column labels from the original data source), drag fields between areas below section (is used to arrange fields in one of the four pivot table areas). filters area, columns area, rows area, values area. filters area - correct answer filters the data to display results based on particular set conditions columns area - correct answer subdivides data into one or more additional categories rows area - correct answer organizes and groups data into categories on the left side values area - correct answer displays summary statistics, such as totals or averages
modifying a pivot table (fields) - correct answer add a fields as a row- in the Choose fields to add to report section. click the field's check box or drag the field and drop in ROWS area or right-click filed name and select Add to Row Labels modifying a pivot table (value) - correct answer add a value -- in the Choose fields to add to report section. click the field's check box or drag the field and drop in VALUES area or right-click filed name and select Add to Values modifying a pivot table (column) - correct answer add a column -- in the Choose fields to add to report section. drag the field and drop in COLUMNS area modify a pivot table - correct answer add, remove, or rearrange fields to get a different perspective of the data. be careful not to make the data overwhelming with too many details. excel doesn't automatically update pivottables when the original data source changes. you must select "refresh" from the pivot table menu sorting a pivot table - correct answer basic sorting is very simple. select any cell in the column you want to sort, then click the A->Z or Z-->A button in the "sort and filter" menu filter a pivot table - correct answer two types: report filter and group filter. report filter - correct answer filters the data that goes into the pivot table using any field. filter on pre-selected data - filter area. ex: only sum the sales for 1st edition books group filter - correct answer filters the selected column and row fields (the "groups"). filter on any column. ex: only show rows where the row label contains the word "justice" slicers - correct answer insert these to allow easy filtering by clicking buttons calculated field - correct answer a new field that is calculated from other fields. ex: royalties field is calculated as 10% of the Total Book Sales. only exists in the pivot table (it doesn't exist in the original dataset). after it is created, it can be added to the pivot table rows, columns, or values (just like any other field) create a calculated field - correct answer analyze tab select a pivot table cell, click fields, items, & sets, and select calculated field to display the insert calculated field dialog box. options for displaying pivot table values - correct answer custom calculation options: % of either grand total, column total, row total, or of parent row total. running total. rank of smallest to largest and largest to smallest pivot table limitations - correct answer single data source. 2^16 of rows limit ( rows)
"changing cells" then uses an iterative process to find an optimal solution. a summary report can be gnerated if a solution is found. one-variable data table - correct answer shows how changing one value affects one or more calculated results creating a one-variable data table - correct answer you must choose: 1. which variable to try changing (e.g., APR). 2. what values to try for that variable (e.g. 4%, 4.25%, etc). (these values are called "substitution values"). 3. what calculated results to view the effect on (e.g. Payment. etc) formatting the column labels - correct answer by default, excel doesn't add appropriate labels. instead, it shows the current values of the formulas. you can use a "custom" format to override what is shown in the cells with an appropriate label. this isn't ideal, but it works. two-variable data table - correct answer shows how changing two variables affects one calculated result using goal seek - correct answer enter: the end result you want to get (which cell should be set to what value), and the input cell that you want to allow to change to get the end result. ex: you be able to pay $1,000/month. you want to know the cost of home that you can afford? using scenario manager - correct answer you create multiple "scenarios". each scenario has a unique set of input values which will produce a set of output values. give a name to each scenario ("best case," "if I get a raise", etc). then generate a "scenario report" to compare the outputs for all of the scenarios. drawback: is a semi-automatic tool entering a scenario - correct answer enter the scenario name and the cells that you want to provide inputs for. then enter the values for those chosen input cells. ex: enter "best case" scenario values for car cost, down payment, rate, and loan length. generate scenario summary reports - correct answer after entering multiple scenarios, generate a ------. choose which calculated results to include. solver three parameter requirements - correct answer objective cell, changing cells, constraints objective cell - correct answer contains the value that you want to optimize changing cells - correct answer are adjusted until the constraints are satisfied constraints - correct answer specify the restrictions. ex: house cost < 150,000. ex: loan years must be an integer (whole number)
four steps for solver - correct answer activate solver, determine target cell, determine changing cells, establish constraints activate solver - correct answer add-in that is not installed by default determine target cell - correct answer must be a formula cell. max, min, set value. determine changing cells - correct answer must "feed into" target cell establish constraints - correct answer the "key" to making solver work binding constraints - correct answer constraints where the limit was enforced. the solver couldn't go past the limit, so this restricted how much the objective cell could be optimized nonbinding constraints - correct answer constraints where the limit was not enforced. solver summary report - correct answer can be generated if a solution is found. ex: find down payment, APR, etc. (within constraints) that result in minimum total interest paid. nested logical functions - correct answer function that is embedded within an argument of another function. many ---- have more complicated conditions (do something if this and this or this are true) index and match lookup functions - correct answer sometimes you want to search for records in a table that match a certain value (look up the salary of the employee who has the id 12345) "database" filtering and functions - correct answer ---- functionality can be used to easily do advanced filtering (sum salaries of all managers in Dallas and Chicago and..) perform common calculations using an "advanced filter" to select the data to use. do automatcially update when the filter criteria changes loan amortization table - correct answer helps you complete investment analyses (best rate while buying a car) nested logical function forumla - correct answer IF(logic, value_if_true, value_if_false) AND, OR, and NOT functions - correct answer sometimes you want to test multiple conditions at the same time (commission baed on sales AND years of service) AND function - correct answer true if all conditions are true. returns TRUE if all of the arguments evaluate to TRUE. =AND(A2>=10, B2<5). the formula returns TRUE if a
INDEX MATCH MATCH - correct answer =INDEX(array, MATCH (lookup_value, lookup_array, 0), MATCH (lookup_value, lookup_array, 0)). instead of just a vertical lookup, INDEX MATCH MATCH allows you to perform a matrix lookup aka two-way look up use advanced filter to.. - correct answer easily filter data using multiple filter criteria (combined multiple criteria). ex: create a filter to show all of these: 1. "account reps" who earn more than $30,000 in Chicago. 2. "account reps" who is in Atlanta. 3. More... Keep the original dataset visible and put the results of the filter in another location use advanced filtering - correct answer to use an Advanced filter, you must first create a "criteria range". which is a separate area in the sheet that lists the criteria to filter by. you can easily change the criteria and then re-apply the filter. records are shown that meet: criteria in any of the rows (show records that meet row1 criteria OR row 2 criteria OR row 3 criteria...). all of the criteria within a row (show records that meet criteria for "Type" AND criteria for "release date" AND..) you can designate a separate location to output the results of the filter. *** if you change the criteria, the output doesn't automatically update. you much apply the filter again. advance filter dialog - correct answer "List range" "Criteria range" "Copy to" list range (advanced filter) - correct answer the location of the data to filter criteria range (advanced filter) - correct answer location of the criteria to filter on copy to (advanced filter) - correct answer where to output the results of the filter normal filter vs. advanced filter - correct answer normal filer: you'll only get single combined condition, all need to conjunct with AND. (Boston or Chicago) AND (Account Rep) AND (>30,000). advanced filter: criteria in any of the rows (show records that meet row 1 criteria OR row 2 criteria OR row 3 criteria..) AND condition could be within row most common database functions - correct answer DSUM, DAVERAGE, DMIN, DMAX, DCOUNT database functions have three arguments - correct answer database, field, criteria database (database function) - correct answer the location of the data (including column labels) filed (database function) - correct answer which field to perform the function on. ex: sum the "salary" field criteria (database function) - correct answer location of the "criteria range"
example of DSUM database function - correct answer =DSUM(database, field, criteria) creating a loan amortization table - correct answer schedule for loan amortization table. excel has financial functions that can be used for business financial analysis and for personal financial management. PMT function, IMPT function, PPMT function, CUMIPMT function, CUMPRINC function schedule for loan amortization table - correct answer monthly payments, interest per period, principal repayment per period, balances PMT function - correct answer calculates the monthly payment for a loan with a fixed interest rate for a specified period of time. =PMT(rate,nper,pv) IPMT function - correct answer calculates the periodic interest for a specified payment period on a loan or an investment given a fixed interest rate, term, and periodic payments. =IPMT(rate,per,nper,pv) PPMT function - correct answer calculates the principal payment for a specified payment period on a loan or an investment given a fixed interest rate, term, and periodic payments. =PPMT(rate, per,nper,pv). so PMT(rate,nper,pv) = IPMT(rate,per,nper,pv) + PPMT(rate,per,nper,pv) CUMIPMT function - correct answer calculates the cumulative interest throughout a loan amortization table. =CUMIPMT(rate,nper,pv,start_period,end_period, type) "conditional" math and statistical functions - correct answer you often only want to sum, average, etc. values that meet certain conditions. excel has other versions of these functions that perform the same function IF a certain condition is met (ex: sum all salaries IF the township equals "jackson") some of the conditional functions: SUMIF, AVERAGEIF, COUNTIF. conditional functions when more than one condition must be met, use: SUMIFS, AVERAGEIFS, COUNTIFS calculate relative standing with statistical functions - correct answer compare individual value to rest of dataset (ex. your ranking in your class based on exam scores) measure central tendency - correct answer sometimes you want to describe your data with statistics like average and variance histogram - correct answer visual display of distribution of data, visual display of tabulated frequencies. requires bins to tabulate the data and returns a frequency distribution table conditional functions require these arguments - correct answer 1. the criteria to meet (ex: "=Jackson" ">40,000"). 2. the range of cells to compare to the criteria. 3. some functions such as SUMIF also need an argument to specify the range of values to sum.
SD - correct answer standard deviation measuring central tendency - correct answer population, sample, variance, standard deviation population (measuring central tendency) - correct answer dataset containing all the data to be evaluated sample (measuring central tendency) - correct answer smaller, more manageable segment of the population variance (measuring central tendency) - correct answer measure of a dataset's dispersion standard deviation (measuring central tendency) - correct answer measure of how far the data sample is distributed around the mean standard deviation functions - correct answer STDEVA, STDEVPA, STDEV.P, STDEV.S variance functions - correct answer VARA, VARPA, VAR.P, VAR.S STDEVA - correct answer standard deviation of a sample including logical values and text STDEVPA - correct answer standard deviation of a population including logical values and text STDEV.P - correct answer standard deviation of a population STDEV.S - correct answer standard deviation of a sample VARA - correct answer variance of a sample including logical values and text VARPA - correct answer variance of a population including logical values and text VAR.P - correct answer variance of a population. calculates the variance based on the population (=STDEV.P(number1,number2)) VAR.S - correct answer variance of a sample. calculates the variance based on a sample. (=STDEV.S(number1,number2)) analysis toolpak - correct answer an add-in that contains tools for performing complex statistical analyses. the --- must be enabled, then the tools can be selected as usual. tools include Correlation, T-Tests, etc.
correlation coefficient - correct answer a value between -1 and +1 that tells you how strongly two variables are related to each other. a --- of +1 indicates a perfect positive correlation. As variable X increases, variable Y increases. as variable X decreases, variable Y decreases CORREL - correct answer determine the strength of a relationship between two variables (=CORREL(array1,array2)) FREQUENCY - correct answer determines the frequency distribution of a dataset. determine how often a set of numbers appears in specific range. (=FREQUENCY(data_array,Bins_array))
