excel 201 macros & controls

Excel 201

Advanced Skills

This presentation is solely for the use of ZS Associates personnel. No part of it may be circulated, quoted or reproduced for distribution outside of ZS Associates without prior written approval of ZS Associates.

ZS Associates609.419.3800 Tel

www.zsassociates.com

2 20090416 - Excel 201 -v1.0© 2009 ZS Associates

Please ensure you have the Excel 201 training materials on your computer

• Training materials for this session may have already been loaded on your laptops

– If not, download all files from: [ENTER PATH HERE]

• For all exercises, refer to “Excel 201 Exercises v1.0.xls”– Solutions can be found in “Excel 201 - Solution v1.0.xls”

Required Materials


Agenda

• Introduction

• Concepts to Improve Workbook Usability

• Advanced Functions and Formulae

• Advanced Defined Names

• Controls

• Array Formulae

Introduction

• 5 min

• 45 min

• 45 min

• 30 min

• 30 min

• 30 min


Excel 201 is designed to enhance and expand your Excel skills

• Excel 201 is designed to enhance your Excel skills through powerful formulae and functionality

• At the end of this course, you will be able to:

– Work more efficiently in Excel by using advanced functionalities

– Perform powerful analysis in Excel by understanding a wider range of options available to you

– Develop more dynamic Excel tools through the use of advanced formulae and functions

– Create user-friendly Excel workbooks by incorporating dynamic ways to interact with data

Introduction


Agenda

• Introduction




• Controls

• Array Formulae

Introduction

• 5 min

• 45 min

• 45 min

• 30 min

• 30 min

• 30 min


Conditional Formats change the appearance of a cell to reflect the value it contains

• When dealing with large amounts of data, it can be helpful to highlight records that meet certain criteria

• Cell formats can be specified using your customized toolbars or the cell properties menu

• Can be used to highlight values that meet certain criteria (e.g. outliers)

Example: Highlight Sales greater than $300

Workbook Concepts


• A formula can be used instead of the cell value to take advantage of relative references or worksheet functions

• Multiple conditions can be used to create different conditional formats

Advanced uses of conditional formats allow for multiple formats and multiple conditions

Workbook Concepts

If the formula evaluates to TRUE, the formatting will be applied


Data Validation restricts the possible values of a cell to a given list or range

• The list option of Data Validation can be used as a simple version of a drop down box.

Criteria

Type of Criteria

Customized Alerts

Workbook Concepts

e.g. Can be useful to ensure client enters appropriate data


• Both a worksheet and the individual cells must have protection enabled (“locked”) to prevent changes from being made

• To lock a worksheet intended for client use:– Step 1: Unlock cells that users should be

able to edit– Step 2: Lock the worksheet

Passwords can be required to unlock the worksheet

Worksheet passwords are not secure!

Worksheet protection can be used to prevent changes to all or part of a worksheet

Workbook Concepts


Workbook protection can be used to limit access to the workbook or changes to its structure

• Workbook protection can be used to require a password to open a workbook or save changes to a workbook

• Other workbook protection options can be found in Tools > Protection ([Alt]+T, P)

Workbook Concepts

Password for “write” access

Password to open


ZS has been hired by ABC Pharma to set product sales goals for each geography in their sales force

Workbook Concepts

Situation

•ABC Pharma has a single product with access to accurate and reliable account level product and market sales data

•They would like to use a goal based plan for their sales force’s IC plan

Complication

ABC pharma’s product is very mature and they would like their goals to be based 90% on the previous year’s product sales and 10% on each geography’s untapped potential

QuestionHow can we best design and create an excel workbook to calculate their goals?

• In a goal based plan, the field gets a bonus proportional to their goal attainment

• Attainment is calculated by dividing sales by the goal


After completing these exercises, you will understand the inputs to ABC Pharma’s goal setting process

• Review the inputs on the “Inputs” worksheet and review the raw account level sales data on the “Raw – Account Sales Data” worksheet

• Complete the tasks 1 – 5 listed on the “Exercises” worksheet

After completing the exercises in this section, you should understand data validation, formula auditing, named ranges and conditional formatting

Workbook Concepts


Agenda

• Introduction




• Controls

• Array Formulae

Functions and Formulae

• 5 min

• 45 min

• 45 min

• 30 min

• 30 min

• 30 min


We will discuss a few advanced features that provide functionality beyond what was covered in Excel 101

Function Syntax Comments

VLOOKUP VLOOKUP(Lookup value, Lookup table, Offset, Type) Searches for a value in the leftmost column of a table, and then returns a value in the same row from the column specified

HLOOKUP HLOOKUP(Lookup value, Lookup table, Offset, Type) Searches for a value in the topmost row of a table, and then returns a value in the same column from the row specified

MATCH MATCH (Lookup value, Lookup range, Type) Returns the relative position of an item in an array that matches a specified value in a specified order

INDEX INDEX (Index range, Row, Column) Returns a value or the reference to a value from within a table or range

OFFSET OFFSET (Reference, Rows, Columns, Height, Width) Returns a range of specified Height and Width, which is located a specific number of Rows and Columns from the stated reference

INDIRECT INDIRECT(Reference) Returns the value in the cell which is specified by the stated reference

ADDRESS ADDRESS(Row_num, Col_num) Returns the address of the cells whose row and column numbers are used as parameters

ROW ROW(Reference) Returns the row number of a reference

COLUMN COLUMN(Reference) Returns the column number of a reference

SUMIF SUMIF(range, criteria, sum_range) Adds the cells specified by a given criteria

COUNTIF COUNTIF(range, criteria) Counts the number of cells within a range that meet the given criteria

RANK Rank(Number, Ref, [Order]) Returns the rank of a value within a specified range of values

SUMPRODUCT SUMPRODUCT(array1,array2,array3, ...) Multiplies corresponding components in the given arrays, and returns the sum of those products

TRANSPOSE TRANSPOSE(Source Range) Returns a vertical range of cells as horizontal, or vice versa

FREQUENCY FREQUENCY(data_array, bins_array) Calculates how often values occur within a range of values, and then returns a vertical array of numbers

TABLE TABLE(Input1,Input2) Used to perform sensitivity analysis on one and two variable tables



SUMIF() and COUNTIF() functions can be used to aggregate data to a desired level

• Aggregate functions helps allow the same dataset to be used for different analyses

• These functions are preferred over Pivot tables as they eliminate the refresh task



Use the rank function to determine a value’s size relative to others in a list

• Returns the rank of a number in a list of numbers

• Syntax:– RANK(number,ref,order)

Number is the number whose rank you want to find. Ref is an array of, or a reference to, a list of numbers Order is a number specifying how to rank number.

– Use 0 (zero) for descending order and 1 (or any nonzero value) for ascending order

• After using the rank function, use other functions such as index and match to quickly produce tables for a given attribute listing the top or bottom items in a list

A B C D E1

2 Territory Sales ($MM)Sales Rank

3 A 2.1 =RANK(C3,C$3:C$6,0) 24 B 1.7 =RANK(C4,C$3:C$6,0) 45 C 3.0 =RANK(C5,C$3:C$6,0) 16 D 1.8 =RANK(C6,C$3:C$6,0) 37

Rank formula to use in column D

A B C D1112 Sales Rank Territory13 1 =INDEX(B$3:B$6,MATCH(B13,$D$3:$D$6,0)) C14 2 =INDEX(B$3:B$6,MATCH(B14,$D$3:$D$6,0)) A15 3 =INDEX(B$3:B$6,MATCH(B15,$D$3:$D$6,0)) D16 4 =INDEX(B$3:B$6,MATCH(B16,$D$3:$D$6,0)) B17

Index-Match formula to use in column C



The OFFSET function is used to return a value or range of values based on location inputs

• OFFSET (Reference, Rows, Columns, Height, Width)– Returns a range of specified Height and Width, which is located a

specific number of Rows and Columns from the stated reference

If the Reference is a range of cells, the offset is counted from the upper left corner of the Reference range.



TRANSPOSE can be used to normalize or denormalize data

• TRANSPOSE(Source Range)– Returns a vertical range of cells as a horizontal range, or vice

versa

• Example:


TRANSPOSE() is different from pasting values; like any other function, it dynamically updates values when the underlying data changes


FREQUENCY can be used to quickly create histograms

• FREQUENCY(data_array, bins_array)– Highlight entire column when entering formula

data_array: bins_array: Frequency:69 68 34 52 10 1026 1 34 79 20 814 67 91 6 30 749 55 53 79 40 629 70 84 6 50 519 29 20 21 60 1276 14 91 74 70 89 43 37 52 80 12

64 79 80 48 90 818 55 36 58 100 451 84 21 3528 95 72 5512 58 63 797 64 52 46

12 23 3 8178 89 79 959 42 19 707 40 86 52

84 86 74 180 52 89 6



One and two-variable data tables can be used to easily perform sensitivity analysis on a complicated model

• Example:

• To enter the formula– select the entire table (C12:G17)– On the Data menu, click Table– In the Row input cell box, enter the reference to the input cell for the input values in the row (C4) – In the Column input cell box, enter the reference to the input cell for the input values in the column (C2)– Click OK

A B C D E F G H I J K L M

1

2 mROI 25%

3 Optimal Size

4 Forecast ($MM) 550 144

5

6 Cost / PDE 110

7

“Complicated” Sizing Model

A B C D E F G H

10 Optimal Size

11 Forecast ($MM)

12 =L4 525 550 575 600

13

mR

OI

0%

{=Table(C4,C2)}

14 25%

15 50%

16 75%

17 100%

18

A B C D E F G H

10 Optimal Size

11 Forecast ($MM)

12 144 525 550 575 600

13

mR

OI

0% 147 154 162 171

14 25% 138 144 151 159

15 50% 129 134 140 147

16 75% 120 124 129 135

17 100% 111 114 118 123

18

Note: this formula is not an array formula (even though it will appear with brackets). It must be entered using the data table prompt



We will also discuss how IS functions can be used to check results, and improve the appearance of workbooks

• IS functions check the type of the parameters and return TRUE or FALSE

• The IS functions are useful in formulae for testing the outcome of a calculationFunction Syntax * Tests for

ISBLANK ISBLANK(value) An empty cell.

ISERR ISERR(value) Any error value except #N/A.

ISERROR ISERROR(value) Any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).

ISLOGICAL ISLOGICAL(value) A logical value.

ISNA ISNA(value) The #N/A (value not available) error value.

ISNONTEXT ISNONTEXT(value) Any item that is not text. (Note that this function returns TRUE if value refers to a blank cell.)

ISNUMBER ISNUMBER(value) A number.

ISREF ISREF(value) A reference.

ISTEXT ISTEXT(value) Text.



ISERROR can be used to make cleaner workbooks

• ISERROR(Value)– Value is the value you want tested

• Example:

Referencing blanks in a formula returns an error.

To avoid error messages we can use IF(ISERROR(Sales/Reps),“ ”,Sales/Reps).


This is an example of on IS function. Other functions can be used in different situations


Let’s use functions and formulae to calculate each territory’s goal

• Complete the exercises 6 – 13 listed on the “Exercises” worksheet

– Calculate each territory’s previous year product sales and market sales

– Calculate each territory’s goal by adding the historical portion and untapped potential portion of each territory’s goal

– Create ranks for territories based on their total goal and also their required growth rate

A territory’s required growth rate is their goal divided by their previous year product sales minus 1

After completing the exercises in this section, you will have successfully set the goals for each of ABC Pharma’s territories


Required Growth Rate = - 1Goal

Previous Year Product Sales


Agenda

• Introduction




• Controls

• Array Formulae

Advanced Defined Names

• 5 min

• 45 min

• 45 min

• 30 min

• 30 min

• 30 min


Defined Names are more powerful than cell references

• Simplify formulae

• Create more robust analyses

• Design tools that are not dependent on a fixed amount of data

• Build graphs that automatically resize themselves to cover all of (and no more than) your data



We can use OFFSET() for resizing named ranges

• To create self-resizing named ranges we can combine COUNTA() and OFFSET() in “Define Name” window

Dynamic named ranges update when the length of an array changes.

Use offset to define the named range!



• Addressing functions take advantage of Excel’s tabular structure– Cells can have an address (A1:C1) or a name (myData)– Worksheets also have names (Sheet1)

• ADDRESS() will help identify the location name used by Excel

• INDIRECT() will help you use names that have been defined

• ROW() / COLUMN() return the number of a row or column

Addressing functions can create “dynamic” ranges that resize themselves

Sum(Sheet1!$A$1:$A$4)

Worksheet Name ! (Separator) Range Name



ADDRESS returns the location of a cell

• ADDRESS(Row_num, Col_num) is a powerful addressing function in Excel that lets us use our knowledge of how a spreadsheet is designed

• Example: ADDRESS(1,2) returns $B$1

= ADDRESS(1,2)

1st Row

2nd Column



INDIRECT uses a location or named range to return a stored value

• We can use INDIRECT() when we want to change the reference to a cell within a formula without changing the formula itself.

• Example:

= SUM(INDIRECT(C1))

Named Range “MDs” Named Range “Details”

= SUM(INDIRECT(B1))



Formulae can provide added functionality when used as a defined name

• The cell reference and dimensions of a dynamic named range are functions of the value of some other cell(s) in the workbook

Enter a formula instead of a reference.

While it is important to be aware of this functionality in case you encounter it, we recommend that you do NOT use it; it makes workbooks very difficult to understand



Use relative references with defined names to create “custom” functions

• Example:

We can define a named range called MySum that sums the range given in “Refers to:”

= MySum

The same function can be used to calculate the total number details.


While it is important to be aware of this functionality in case you encounter it, we recommend that you do NOT use it; it makes workbooks very difficult to understand


Let’s make our workbook more dynamic by replacing some of the static named ranges with dynamic ones

• Complete exercise 14 listed on the “Exercises” worksheet of the goal setting workbook

– Delete the named ranges on the worksheet “Raw – Account sales Data”

– Replace them with dynamically defined named ranges Use the offset and counta functions in the named range definition

In future goal setting cycles, if the number of accounts changes, the goal setting workbook will now calculated territory level sales correctly!



Agenda

• Introduction




• Controls

• Array Formulae

Controls

• 5 min

• 45 min

• 45 min

• 30 min

• 30 min

• 30 min


Controls are great for building Excel tools

• What are controls?– Controls are elements on a worksheet that the

user can interact with

• There are 2 types of controls– Forms– Control Toolbox (ActiveX controls)

• To access Forms– Select View > Toolbars > Forms to bring up the

controls toolbar – Control formatting options are available by right-

clicking on the control

ActiveX controls provide many more options than Forms. But, sufficient care must be taken as ActiveX controls are sometimes unstable.

Controls


Excel provides the user with various types of Controls

Drop Down Box List Box Group Boxes

Option Button

Check Box

Scroll BarSpinnerButton Text Box or Label

Controls


Controls interact with the spreadsheet via “cell links”

2

2. Based on the user’s selection, the control outputs a result to the cell link

The cell link is designated under the “Control” tab in the Format Control dialog box

3. Use INDEX() to find the matching data

3

3

named ranges

1. The drop-down control takes an input range, from which the user can select one item

1The input range is defined under the “Control” tab of the Format Control dialog box

Controls


The Drop Down/Combo Box and List Box allow the user to select from a pre-defined list• Functionality: Drop Down/Combo and List Boxes

– The item selected is highlighted and displayed in the text box – The control returns a value representing the position of the selected

item

• When to use which ?– Drop Down/Combo Boxes requires less space on the worksheet – The List Box provides faster access

• Control Properties– INPUT RANGE– CELL LINK– DROP DOWN LINES– SELECTION TYPE

Drop Down Box List Box

Controls


Option Buttons and Check Boxes are used to select multiple or a single option within a group

• Functionality

• Control Properties – VALUE– CELL LINK

Group Box

Option Buttons

Check Box

Option Button Check BoxBoth allow for a choice to be turned off/on

Single Selection: Selecting one button will deselect any others

Mutliple Selections: Boxes are independent

Return a value related to the choice selected

The # of the button checked Value for each box

Group Boxes Tell Excel which buttons work together

Format options for the user

Format options for the user

Multiple sets of Option Buttons on the same worksheet be in a Group Box or

they will ALL be linked together

Controls


Scroll Bars and Spinners are linked to a cell or a text box to manipulate the values inside

• Functionality: Scroll Bar and Spinner– Adjust the value of a cell (cell link) from a

range of integer values

• Control Properties– CURRENT VALUE– MINIMUM VALUE– MAXIMUM VALUE– INCREMENTAL CHANGE– PAGE CHANGE– CELL LINK

Scroll BarSpinner

20

Controls


Buttons, Labels, Text Boxes, and AutoShapes can initiate the execution of VBA code and can display cell contents

• Functionality– An advantage of displaying data in a control versus a cell is the

ability to freeze the size, position and format of the control regardless of what happens on the rest of the worksheet.

– They do not pass a value back to a cell– They do not have a “Control” tab on the “Format Control” dialog box.

Button Label Text Box

Show Graph

AutoShape

Controls


Capabilities of Controls can be further enhanced by assigning Macros

• A Macro can be executed from the Macro dialog box

• Macros assigned to controls will execute every time the control is used

• Macro’s can also be recorded using the record Macro functionality

Controls


Improve the user interface of a workbook by replacing some of the inputs cells with forms and/or controls

• Complete exercise 15 listed on the “Exercises” worksheet of the goal setting workbook

– Replace the input cells for maximum achievable share and the % of goal based on history with horizontal scroll bar forms

– Set appropriate minimum, maximum, and increment values for the scroll bars

Forms and controls can make it easy for users to refine workbook inputs

Controls


Agenda

• Introduction




• Controls

• Array Formulae

Array Formulae

• 5 min

• 45 min

• 45 min

• 30 min

• 30 min

• 30 min


• An array is a collection of cells or values that is operated on as a group

• Array functions – Perform the same task on each cell of an input array– Return multiple values

• We use array formulae all the time– SUM and COUNT functions summarize data in ranges

Array formulae are a powerful feature of Excel that can be used to summarize lists of data by operating on each cell in an array

Array Formulae: Introduction

Array formulae allow for more complex calculations


Array formulae are very powerful formulae that also provide flexibility in designing Excel tools

• The values get updated automatically when data is refreshed – convenient when data refresh happens often

• There is only one table to deal with – easy to expand to accommodate additional columns

• A combo box is utilized to display different region choices

• The offset worksheet function is used with array formulae to show territories in the selected region

Array Formulae: Introduction


An array formula can perform multiple calculations

Array Formulae: Concepts

• Entering an array formula– Type the array formula in the formula box– Press CTRL + SHIFT + ENTER

• When using multi-cell array formulae, all single-cell references are treated as absolutes

– In the example below, a multi-cell array formula is entered while selecting cells A2 to B2.

– The same value will result in all cells, because the reference to A1 is treated as an absolute

= { A1 + 10 }

A B C1 10 25 822 20 20 2034 20 35 925

= { A1:C1 + 10 }


We can use the binary logic shortcuts instead of writing out the IF() function

• Comparison operators like < , > result in a TRUE or FALSE (Boolean) reply

• To convert Boolean (True/False) values to Binary (1/0) values that can be used in other calculations, multiply the result by 1

– True * 1 = 1– False * 1 = 0

A B = (A>B) = (A<B) = (A=B)5 10 FALSE TRUE FALSE

A B = (A>B)*1 = (A<B)*1 = (A=B)*15 10 0 1 0

Array Formulae: Binary Logic


ORX Y X+Y0 0 00 1 11 0 11 1 2

Using binary logic with array formulae allows you to create advanced formulae with multiple criteria

• The AND operator can be simulated with multiplication ( * )

X Y X AND Y0 0 FALSE0 1 FALSE1 0 FALSE1 1 TRUE

AND

X Y X OR Y0 0 FALSE0 1 TRUE1 0 TRUE1 1 TRUE

OR

• The OR operator can be simulated with addition ( + )

• Note: There is no direct arithmetic operator that is equivalent to the NOT operator

Array Formulae: Binary Logic

X Y X*Y0 0 00 1 01 0 01 1 1

AND


Array formulae are appropriate in many, but not all situations

• Can increase complexity of work / Black-Box effect

• Calculation time can increase dramatically

• Easily broken

• Requirements of array formula components to be the same size

• Limitations of multi-cell array editing

Array Formulae: Appropriate Use


ZS will also set district and regional goals and provide several territory goal summaries for ABC Pharma

• Complete exercises 16 - 24 listed on the “Exercises” worksheet of the goal setting workbook

– Calculate district and region historical product and market sales– Use advanced conditional formatting to highlight territories with

the highest goals or growth– Use the frequency function to create a histogram– Use index, match, and indirect to fill in values inside summary

tables

Advanced Functions and Formulae

Using advanced functions and formulae gives your workbooks tremendous power and flexibility!

Appendix A

Keyboard Shortcuts

Appendix


Keyboard shortcuts

Alone Shift+ Ctrl+ Ctrl+Shift+ Alt+F1 Help Context Help F11F2 Formula Bar Edit a Cell Note Show Info Window F12F3 Paste Function Wizard Define Name Create NamesF4 Refer/Repeat Find Next Close Find Previous ExitF5 Goto Find RestoreF6 Next Pane Previous Pane Next Window Prev WindowF7 Check Spelling MoveF8 Extend Toggle Add Mode Toggle SizeF9 Calculate Calculate Sheet Minimize WorkbookF10 Menu Bar Shortcut Menu Maximize WorkbookF11 New Chart New Worksheet Excel 4 Macro SheetF12 Save As Save Open Print

Appendix


Keyboard shortcuts (cont.)

Editing Highlight Section Formula BarCtrl C Copy Ctrl A Select All Ctrl ;Ctrl Ins Copy Ctrl Space Select Column Ctrl :Ctrl X Cut Shift Space Select Row Alt =Shift Del Cut Ctrl Shift Space Select Worksheet Alt +Ctrl V Paste Ctrl / Select Current Array Ctrl 'Delete Clear Ctrl * Select Current Region Ctrl "Ctrl + Insert Ctrl } Select All Dependents Alt EnterCtrl - Delete Ctrl ] Select Direct Dependents Ctrl Alt Tab

Ctrl { Select All Precedents Ctrl DelWorkbook Ctrl [ Select Direct Precedents Ctrl Shift DelCtrl N New Ctrl ? Select NotesCtrl O Open Ctrl \ Select Row Differences OtherCtrl P Print Ctrl | Select Column Differences Ctrl FCtrl S Save Alt ; Select Visible Cells Ctrl HCtrl W Close Ctrl GCtrl Pg Up Next Sheet in Workbook Formatting Ctrl ZCtrl Pg Down Prev Sheet in Workbook Ctrl 1 Format Cells Alt Bkspace

Ctrl 2, Ctrl B Bold Alt EnterDisplay Ctrl 3, Ctrl I Italic Ctrl YCtrl 7 Show/Hide Std Toolbar Ctrl 4, Ctrl U Underline Ctrl RCtrl 8 Show/Hide Outline Symbols Ctrl 5 Strike through Ctrl DCtrl 9 Hide Selected Rows Alt ' Style Box Ctrl TabCtrl 0 Hide Selected Columns Ctrl & Add Outline Border Ctrl Shift TabCtrl ( Display Selected Rows Ctrl _ Remove All BordersCtrl ) Display Selected ColumnsCtrl ` Display formulas/values

Appendix

Appendix B

Appendix

Adding drop-down menus to charts


Adding Drop Down/Combo and List Boxes can be time intensive, but creates a dramatic effect

• Graphs with Drop Downs ease the presentation of multiple graphs:

– Present different scenarios in an easily accessible, uncluttered worksheet

– User can focus on what he/she wants– Eliminates time necessary to scroll through multiple graphs– When used effectively, creates a more appealing user interface– Refer to an entire data range

Appendix


To add Drop Down boxes to your graphs, prepare the underlying data first

• Prepare the raw data table– Create a table with columns for each of the pull-down boxes and the x- and y-axis

values

– Separately, create a list of unique values for each control E.g. if one control will allow the user to select from a list of products – create a unique list of

products to use as the INPUT RANGE for the control

– Prepare a final table to show the data for the graph – we’ll use INDEX/MATCH functions to fill in the table dependant on the user’s selections

• Create the pull-down boxes– Set the INPUT RANGEs for each control– Set the Cell Link to be used

Unique Key Product Ventile Time Period P1E Market Share # DoctorsA _1_1_0 A 1 1 0 8.61% 291A _1_1_1 A 1 1 1 13.20% 352A _1_1_2 A 1 1 2 17.46% 176A _1_1_3 A 1 1 3 18.15% 122A _1_1_4 A 1 1 4 15.36% 62A _1_1_5 A 1 1 5 26.19% 41

Add a unique key by concatenating the pull-down box columns with

the x-axis values

X–axis values

Y–axis values

Pull-down box values

Appendix


Create a graph based on a final, fixed dimension data table

• The final data table will use lookup formulae to assemble the data

– Create a unique list of the possible x-axis values– Create an associated list of y-axis values in the next column

Use a lookup function to find the values associated with the x-values based on the values chosen from the pull-down boxes

The unique key will serve as an index to pull information from the raw data table

• Create the graph based on this final data table– Changing the drop down boxes will update the data table – and

your graphsP1E Market Share # doctors Product

0 8.61% 291 11 13.20% 352 A2 17.46% 1763 18.15% 1224 15.36% 625 26.19% 41

Cell designated as "Cell Link"Cell referenced to variable chosen with pull-down box

X–axis values

Y–axis values

Appendix

Appendix C

Appendix

Using dynamic ranges with graphs


Graphs and charts can use Dynamic Ranges to ensure the correct scale is used to present data

• Dynamic named ranges can be used to limit the cells in a named range only to nonempty cells

• Useful as inputs to graphs– Ensures that the data range consists of only nonempty cells– Changes the scale of the graph to exactly fit the data points

* For continuous data

Appendix


Special settings are needed to use Dynamic Ranges in Charts

Rather than entering cell references, enter ‘Workbookname.xls’!Rangename

When the named range changes size, the data on which the graph is based also changes size.

Select “Chart | Source Data”

Appendix

Appendix D

Appendix

Formula Auditing


Highlight a section of a formula in the formula bar…

…and hit “F9” to see that portion of the formula evaluated

Use Formula Auditing features and “F9” to troubleshoot complex formulae

Use precedents and dependents to trace data through a sheet

Use “Evaluate Formula” to step through the parts of a formula

Appendix

Appendix E

Background Information for Exercise

Appendix


Goal setting review how we will set goals for ABC Pharma

Workbook Concepts

Analysis Approach

The sum of every territory goal should equal the national forecast

Goal1 + Goal2 + Goal3 + … + Goaln = ABC’s national forecast

90% of the national forecast will be allocated to every territory as the “history portion of their goal” based on each territory’s proportion of the nation’s previous year product sales

Goal1(historical component) + Goal2(historical component) + Goal3(historical component) + … + Goaln(historical component) = 90% * national forecast

For example, if in the previous year territory 1 accounted for 3.2% of national sales, then:

Goal1(historical component) = 90% * national forecast * 3.2%

10% of the national forecast will be allocated to every territory as the “untapped potential (U.P.) portion of their goal” based on each territory’s proportion of the nation’s untapped potential

Goal1(U.P. component) + Goal2(U.P. component) + Goal3(U.P. component) + … + Goaln(U.P. component) = 10% * national forecast

Untapped potential in a given territory is found by subtracting the current product sales from the maximum achievable sales (M.A.S.)

U.P. = M.A.S. – Current Product Sales

For example, if in the previous year territory 1 had $1.2 MM in sales and has M.A.S. of $1.5 MM, then territory 1’s untapped potential is $300K

The maximum achievable sales (M.A.S.) are the market sales multiplied by the maximum achievable share

For example, market sales in territory 1 are $3.0 MM and the maximum acheivable share is 50%, so the maximum achievable sales are:

$3.0 MM * 50% = $1.5 MM

1

2

3

excel 201 macros & controls

Documents