CGS2518 Final

built-in functions

Functions that are "built-in" excel. Ex:

create PivotChart from PivotTables

1. ◗ Select any cell in the PivotTable. 2. ◗ On the ribbon, click the PIVOTTABLE TOOLS ANALYZE tab. 3. ◗ In the Tools group, click the PivotChart button. The Insert Chart dialog box opens. 4. ◗ If necessary, click the Clustered Column chart (the first chart in the Column charts section), and then click the OK button. A PivotChart appears next to the PivotTable along with the PivotChart Fields pane. Trouble? If you selected the wrong PivotChart, delete the PivotChart you just created, and then repeat Steps 1 through 4. 5. ◗ On the ribbon, click the PIVOTCHART TOOLS DESIGN tab. 6. ◗ In the Chart Layouts group, click the Add Chart Element button, point to Legend, and then click None. The legend is removed from the PivotChart. You do not need a legend because the PivotChart has only one

3-D formulas

3-D references are often used in formulas that contain Excel functions, including SUM, AVERAGE, COUNT, MAX, and MIN.

elasticity

?

PivotChart

A PivotChart is a graphical representation of the data in a PivotTable.

PivotTable

A PivotTable is an interactive table used to group and summarize either a range of data or an Excel table into a concise, tabular format for reporting and analysis.

growth trend

A growth trend changes values by a constant percentage.

linear trend

A linear trend changes values by a constant amount.

vertical lookup table

A lookup table stores the data you want to retrieve in categories. A vertical lookup table that organizes the categories in the first column of the table.

mixed expenses

A mixed expense, which is an expense that is part variable and part fixed.

mixed references

A mixed reference contains both relative and absolute references.

nested IF function

A nested IF function is when one IF function is placed inside another IF function to test an additional condition, such as calculating employee bonuses based on three performance levels.

relative references

A relative reference is a cell reference that is interpreted in relation to the location of the cell containing the formula.

Slicer

A slicer is an object used to filter data in an Excel table. It includes a button for each unique value in the field.

text file

A text file contains only text and numbers without any formulas, graphics, special fonts, or formatted text. The text file is one of the simplest and most widely used formats for storing data because most software programs can save and retrieve data in this format.

SUM

Adds all the numbers in a range of cells.

Excel table

An Excel table is a range of related data that is managed independently from other data in the worksheet.

absolute references

An absolute reference is a cell reference that remains fixed when the cell formula is copied to a new location. It includes a $ in front of the column letter and row number.

PMT

Calculates the payment for a loan based on constant payments and a constant interest rate.

IF

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.

AND

Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE.

OR

Checks whether any of the arguments are TRUE, and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE.

COUNT

Counts the number of cells in a range that contain numbers.

COUNTIFS

Counts the numbers of cells specified by a given set of conditions or criteria.

Scenario Manager

Create scenarios using the Scenario Manager. Rather than manually changing every input cell value, the Scenario Manager lets you define those input values within a named scenario and quickly switch from one scenario to another. The Scenario Manager can also be used to create reports that summarize the key differences in how the various scenarios impact result cells in the financial worksheet.

Using the Conditional Formatting Rules Manager

Each time you apply a conditional format, you are defining a conditional formatting rule. A rule specifies the type of condition (such as formatting cells greater than a specified value), the type of formatting when that condition occurs (such as light red fill with dark red text), and the cell or range the formatting is applied to. You can edit existing conditional formatting rules from the Conditional Formatting Rules Manager dialog box.

extrapolation

Extrapolation is used to extend a series from a single value or a few values to project future values.

filtering data in an excel table

Filtering is the process of displaying a subset of rows in an Excel table or a structured range of data that meets the criteria you specify. The filter button opens the Filter menu, which includes options to sort and filter the table based on the data in that column.

Functions:

Functions are organized by category in the Function Library group. Select a function to open the Function Arguments dialog box. The Insert Function button opens the Insert Function dialog box from which you can select a function. Ex: SUM, AVERAGE, COUNT, MIN, MAX, IF, AND, OR, VLOOKUP, TODAY, PMT, COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, PMT, IPMT, FV, PV, RATE, NPER, IRR, CVP

fixed expenses

General expenses or fixed expenses are expenses not directly related to production.

Goal Seek

Goal seek automates the trial-and-error process by allowing you to specify a value for a calculated item, which Excel uses to determine the input value needed to reach that goal.

one-variable data tables

In a one-variable data table, you specify one input cell and any number of result cells. The range of possible values for the input cell is entered in the first row or column of the data table, and the corresponding result values appear in the subsequent rows or columns. One-variable data tables are particularly useful in business to explore how changing a single input value can impact several financial results.

interpolation

Interpolation is used to fill in a series when you know the starting and ending values of that series.

COUNTIF

The COUNTIF function calculates the number of cells in a range that match criteria you specify.

AVERAGE

Returns the average (arithmetic mean) of its arguments, which can be numbers or names, arrays, or references that contain numbers.

TODAY

Returns the current date formatted as a date.

FV

Returns the future value of an investment based on periodic, constant payments and a constant interest rate.

IPMT

Returns the interest payment for a given period for an investment, based on periodic, constant payments and a constant interest rate.

RATE

Returns the interest rate per period of a loan or an investment. For example, use 6%/4 for quarterly payments at 6% APR.

IRR

Returns the internal rate of return for a series of cash flows.

MAX

Returns the largest value in a set of values. Ignores logical values and text.

NPER

Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.

PV

Returns the present value of an investment: the total amount that a series of future payments is worth now.

MIN

Returns the smallest value in a set of values. Ignores logical values and text.

Solver

Solver is an add-in, which is a program that adds customized commands and features to Microsoft Office programs such as Excel. You might need to activate Solver before you can use it.

text file formats

Text files can be stored as delimited text in which a special character such as a space, comma, or tab marks the beginning of each column. A text file can also be stored in a fixed-width format in which each column starts at the same location in the text file.

AVERAGEIF

The AVERAGEIF function calculates the average of values in a range that match criteria you specify,

The Consolidate Command

The Consolidate command can be applied in two ways. One way is to consolidate by position, which requires data to be laid out exactly the same way and in the same ranges on all the worksheets. The second way is to consolidate by category, which uses labels in the top row or first column of the data ranges on the different worksheets to identify the categories to be summarized. This means that the data can be laid out differently on each worksheet as long as the worksheets use the same row and column labels.

Creating Nested IFs

The IF function can choose between only two outcomes. If you want it to choose from among three or more outcomes, you can nest IF functions. A nested IF function is when one IF function is placed inside another IF function to test an additional condition. You can nest more than one IF function to test for multiple outcomes. Ex of a formula: =IF(B1>B2,"Won",IF(B2>B1,"Lost","Tie"))

NPV function

The NPV function calculates the present value of a series of future cash flows. The PV function assumes that all future payments are equal. If the future payments are not equal, you must use the NPV (net present value) function to determine what would be a fair exchange. The syntax of the NPV function is... NPV(rate, value1[, value2, value3, ...])

present value (PV function)

The PV function calculates the present value of a loan or an investment. For a loan, the present value would be the size of the loan. For an investment, the present value is the amount of money initially placed in the investment account. The syntax of the PV function is... PV(rate, nper, pmt[, fv=0][, type=0])

SUMIF

The SUMIF function adds the values in a range that match criteria you specify.

total row

The Total row is used to calculate summary statistics (including sum, average, count, maximum, and minimum) for any column in an Excel table. The Total row is inserted immediately after the last row of data in the table. In Table Tools under the Design tab you can check off "Total Row."

VLOOKUP

The VLOOKUP function returns values from a vertical lookup table by specifying the lookup value to match to a compare value, the location of the lookup table, and the column in the table that contains the return values.

formatting choices (data bars, color scales, icons, etc.)

The buttons on the HOME tab provide quick access to the most commonly used formatting choices. For more options, you can use the Format Cells dialog box. You can apply the formats in this dialog box to the selected worksheet cells.

break-even point

The point where total revenue equals total expenses is called the break-even point.

Scenario Report

The scenario summary report displays the values of the input cells and result cells under each scenario. Each scenario is listed by name, and the current worksheet values are also displayed. Note that the report uses the defined names you created earlier to identify the changing and result cells. The defined names make the report simpler to interpret.

Create and manage fromulas

Type in the formula bar on the ribbon or click the "fx" button next to the formula bar.

removing duplicate rows

Use the Remove Duplicates dialog box to locate and remove records that have the same data in selected columns. The Remove Duplicates dialog box lists all columns in the table. Usually, all columns in a table are selected to identify duplicate records.

variable expenses

Variable expenses change in proportion to the volume of production.

What-If analysis

What-if analysis lets you explore the impact of changing different values in a worksheet. You can use what-if analysis to explore the impact of changing financial conditions on a company's profitability.

future value (FV function)

When a loan will not be completely repaid, you can use the FV function to calculate the loan's future value. The syntax of the FV function is... FV(rate, nper, pmt[, pv=0][, type=0]) The FV function is often used with investments to calculate the future value of a series of payments. For example, if you deposit $100 per month in a new savings account that has a starting balance of $0 and pays 1 percent interest annually, the formula with the FV function to calculate the future value of that investment after 10 years or 120 months is... =FV(1%/12, 10*12, -100)

Consolidating Data in Worksheets

When a workbook contains many data sources, you will often want to consolidate that information in one simple report. You can summarize or consolidate data from several data sources by doing any of the following: 1. Create a PivotTable report 2. Write formulas that include 3-D references to summarize data drawn from several worksheets or workbooks 3. Use the Consolidate button in the Data Tools group on the DATA tab to create a master worksheet that summarizes data from multiple worksheets within the same workbook. You can display the sum, count, average, or other summary statistics of data values from the worksheets.

Linking Workbooks

When creating formulas in a workbook, you can reference data in other workbooks. To do so, you must create a link between the workbooks. When two files are linked, the source file contains the data, and the destination file (sometimes called the dependent file) receives the data.

Structured References

When you create a formula that references all or parts of an Excel table, you can replace the specific cell or range address with a structured reference, the actual table name, or a column header. This makes the formula easier to create and understand. Ex: [Location] = "NY", [Location] = "SF"

modify PivotTables

You can add a slicer to a PivotTable or PivotChart to filter data. You click one or more slicer buttons to filter the PivotTable or PivotChart. Change the layout of a PivotTable. Format PivotTable.

Create/manipulate Excel tables

You can convert a structured range of data to an Excel table. An Excel table makes it easier to identify, manage, and analyze the groups of related data. When a structured range of data is converted into an Excel table, you see the following: • A filter button in each cell of the header row • The range formatted with a table style • A sizing handle (a small triangle) in the lower-right corner of the last cell of the table • The TABLE TOOLS DESIGN tab on the ribbon

CVP charts

You can illustrate the break-even point by graphing revenue and total expenses against sales volume. The break-even point occurs where the two lines cross. This type of chart is called a cost-volume-profit (cvP) chart. A

sorting data

You can sort data in ascending or descending order. Ascending order arranges text alphabetically from A to Z, numbers from smallest to largest, and dates from oldest to newest. Descending order arranges text in reverse alphabetical order from Z to A, numbers from largest to smallest, and dates from newest to oldest. In both ascending and descending order, blank cells are placed at the end of the table.

subtotals (range of data)

You can summarize data in a range by inserting subtotals. The Subtotal command offers many kinds of summary information, including counts, sums, averages, minimums, and maximums. The Subtotal command inserts a subtotal row into the range for each group of data and adds a grand total row below the last row of data.

named cells/ranges

You can use a defined name to assign a meaningful, descriptive name to a cell or range. A defined name enables you to quickly navigate within a workbook to the cell or range with the defined name. You can also use defined names to create more descriptive formulas.

create PivotTables

You create a PivotTable using the PivotTable Fields pane. The upper section displays names of each field in the Excel table, and the lower section displays four areas in which you place fields to define the PivotTable.

creating a PivotTable

• Click in the Excel table or select the range of data for the PivotTable. • On the INSERT tab, in the Tables group, click the PivotTable button. • Click the Select a table or range option button, and then verify the reference in the Table/Range box. • Click the New Worksheet option button, or click the Existing Worksheet option button and specify a cell. • Click the OK button. • Click the check boxes for the fields you want to add to the PivotTable (or drag fields to the appropriate box in the layout section). • If needed, drag fields to different boxes in the layout section.

Defining a Scenario

• Enter the data values in the worksheet for the scenario. • On the DATA tab, in the Data Tools group, click the What-If Analysis button, and then click Scenario Manager. • Click the Add button in the Scenario Manager dialog box. • In the Scenario name box, type a name for the scenario. • In the Changing cells box, specify the changing cells. • Click the OK button. • In the Scenario Values dialog box, specify values for each input cell, and then click the Add button. • Click the OK button.

Creating a One-Variable Data Table

• In the upper-left cell of the table, enter a formula that references the input cell. • In either the first row or the first column of the table, enter input values. • For input values in the first row, enter formulas referencing result cells in the table's first column; for input values in the first column, enter formulas referencing result cells in the table's first row. • Select the table (excluding any row or column headings). • On the DATA tab, in the Data Tools group, click the What-If Analysis button, and then click Data Table. • If the input values are in the first row, enter the cell reference to the input cell in the Row input cell box; if the input values are in the first column, enter the cell reference to the input cell in the Column input cell box. • Click the OK button.

Setting Solver's Objective and Variable Cells

• On the DATA tab, in the Analysis group, click the Solver button. • In the Set Objective box, specify the cell whose value you want to set to match a specific objective. • Click the Max, Min, or Value Of option buttons to maximize the objective cell, minimize the objective cell, or set the objective cell to a specified value, respectively. • In the By Changing Variable Cells input box, specify the changing cells.

To perform a what-if analysis using Goal Seek

• On the DATA tab, in the Data Tools group, click the What-If Analysis button, and then click Goal Seek. • Select the result cell in the Set cell box, and then specify its value (goal) in the To value box. • In the By changing cell box, specify the input cell. • Click the OK button. The value of the input cell changes to set the value of the result cell.

Creating a Scenario Summary Report or a Scenario PivotTable Report

• On the DATA tab, in the Data Tools group, click the What-If Analysis button, and then click Scenario Manager. • Click the Summary button. • Click the Scenario summary or Scenario PivotTable report option button. • Select the result cells to display in the report. • Click the OK button.

how to import text files

• On the DATA tab, in the Get External Data group, click the From Text button, and then select the text file containing the data. • In the first step of the Text Import Wizard, choose how the data is organized, and then specify the row in which to start the import. • In the second step, in the Data preview box, click to insert a column break, double- click a column break to delete it, and drag a column break to a new location. • In the third step, click each column and select the appropriate data format option button or click the Do not import (skip) option button. • Click the Finish button. • Specify where to insert the imported text, and then click the OK button.

Sorting Using a Custom List

• On the DATA tab, in the Sort & Filter group, click the Sort button. • Click the Order arrow, and then click Custom List. • If necessary, in the List entries box, type each entry for the custom list (in the desired order) and press the Enter key, and then click the Add button. • In the Custom lists box, select the predefined custom list. • Click the OK button.

To extrapolate a series from a starting value

• Select a range with the first cell containing the starting value followed by blank cells to store the extrapolated values. • On the HOME tab, in the Editing group, click the Fill button, and then click Series. • Select whether the series is organized in rows or columns, select the type of series to extrapolate, and then enter the step value in the Step value box. • Click the OK button.

Sorting Data Using Multiple Sort Fields

• Select any cell in a table or range. • On the DATA tab, in the Sort & Filter group, click the Sort button. • If necessary, click the Add Level button to insert the Sort by row. • Click the Sort by arrow, select the column heading for the primary sort field, click the Sort On arrow to select the type of data, and then click the Order arrow to select the sort order. • For each additional column to sort, click the Add Level button, click the Then by arrow, select the column heading for the secondary sort field, click the Sort On arrow to select the type of data, and then click the Order arrow to select the sort order. • Click the OK button.

Creating a Defined Name for a Cell or Range

• Select the cell or range to which you want to assign a name. • Click in the Name box, type the name, and then press the Enter key (or on the FORMULAS tab, in the Defined Names group, click the Define Name button, type a name in the Name box, and then click the OK button). - or - • Select the range with labels to which you want to assign a name. • On the FORMULAS tab, in the Defined Names group, click the Create from Selection button. • Specify whether to create the ranges based on the top row, bottom row, left column, or right column in the list. • Click the OK button.

Entering a Function That Contains a 3-D Reference

• Select the cell where you want to enter the formula. • Type = to begin the formula, type the name of the function, and then type ( to indicate the beginning of the argument. • Click the sheet tab for the first worksheet in the worksheet range, press and hold the Shift key, and then click the tab for the last worksheet in the worksheet range. • Select the cell or range to reference, and then press the Enter key.

Editing a Conditional Formatting Rule

• Select the range with the conditional formatting you want to edit. • On the HOME tab, in the Styles group, click the Conditional Formatting button, and then click Manage Rules. • Select the rule you want to edit, and then click the Edit Rule button. • In the Select a Rule Type box, click a rule type, and then make the appropriate changes in the Edit the Rule Description section. • Click the OK button in each dialog box.

To interpolate a series of values between starting and ending values

• Select the range with the first cell containing the starting value, blank cells for middle values, and the last cell containing the ending value. • On the HOME tab, in the Editing group, click the Fill button, and then click Series. • Select whether the series is organized in rows or columns, select the type of series to interpolate, and then check the Trend check box. • Click the OK button.

create subtotals on a range of data

• Sort the data by the column for which you want a subtotal. • If the data is in an Excel table, on the TABLE TOOLS DESIGN tab, in the Tools group, click the Convert to Range button, and then click the Yes button to convert the Excel table to a range. • On the DATA tab, in the Outline group, click the Subtotal button. • Click the At each change in arrow, and then click the column that contains the group you want to subtotal. • Click the Use function arrow, and then click the function you want to use to summarize the data. • In the Add subtotal to box, click the check box for each column that contains the values you want to summarize. • To calculate another category of subtotals, click the Replace current subtotals check box to remove the checkmark, and then repeat the previous three steps. • Click the OK button.