Excel provides the building blocks for an imaginative and exciting array of decision-making tools. It acts only on data and procedures entered by its users. But once those data and procedures are entered into Excel, it can process this data in a veritable garden of ways. It can treat the data as an electronic spreadsheet, or as a database, and it can plot or graph that data.

These functions enable us to quickly solve business problems traditionally solved with paper and pencil, a ledger, calculator, and drafting tools.  Excel's primary function provides a combination spreadsheet and calculator which facilitates easy problem layout and calculation. Its database function is used to manipulate the numbers, formulas, or phrases stored in the spreadsheet.  And with its graphics function, users can create graphs of spreadsheet data on the screen or on a printer or plotter.  Excel’s advanced capabilities include Solver, which can solve problems that have multiple interdependent variables; Goal Seek, a simplified version of Solver, uses a single changing variable; and Visual Basic for Applications, which is the Excel Macro language.

Excel magnifies our ability to analyze and solve business problems. In particular, repetitive and even dynamic problems needing new solutions for different input values are quickly, easily, and automatically solved. Too, we can save these spreadsheet "models" (also called "worksheets" or "templates") for use or modification in other applications.

In this chapter we begin a journey through the specific features Excel that make it appropriate for model building. Our objective in this chapter, and in the two which follow it, is to increase our familiarity with Excel, toward becoming comfortable using it for your own model building efforts.

After starting Excel, the display brings you immediately to the spreadsheet. In Excel, the spreadsheet is composed of individual worksheets or pages. The spreadsheet requires that you enter labels, data, formulas, and even define specific functions to be performed on the data. The spreadsheet contains several menus, which are identified in Figure 2.1 as the Menu bar, the Standard toolbar, the Formatting toolbar, Status bar, and the Formula bar. By using the Menu bar to select View Toolbars, all toolbars options are displayed and may be selected or customized. Most often, the Menu bar, Standard toolbar, Formatting toolbar, and Status bar are used.

The Menu bar items can be selected in four ways: (1) selecting the menu item with the mouse and clicking with the left mouse button , (2) pressing the alt key, using the arrow keys to move the highlighted box along the Menu bar, and then pressing enter to select a menu item, (3) pressing the alt key and pressing the underlined letter associated with the desired menu item, or (4) by pressing the / key.

Title bar The Title bar is at the top of the Excel spreadsheet window and shows the name of the file currently open.

Menu bar Clicking any item in the Menu bar displays the options that are controlled. Menu bar items include File, Edit, View, Insert, Format, Tools, Data, Window and Help. Because Microsoft keeps the same look and feel across software programs, users of Word or Powerpoint will find similarities in Menu bar functionality.

Standard Toolbar The standard toolbar displays icons for frequently used file, spreadsheet, and database operations, including functions and charting.

Formula Toolbar Is found on the right side of the toolbar and is used for entering, editing, or viewing data or formulas into a cell of the spreadsheet. On the left side of the toolbar is the Name Box. Whenever the pointer is positioned on a cell, the contents of that cell will be shown in the Name Box.   

Formatting Toolbar contains icons for formatting the text or data in the spreadsheet cell(s).

The spreadsheet has many control features and prompts, most of which may be selected from the Menu bar.

The spreadsheet is a table or matrix with 230 columns and 65,536 rows. The screen shows only a fraction of the spreadsheet at a time, much like a window that admits only a bit of a panoramic landscape. But you can move the window around so that you are able to see the entire spreadsheet, though a bit at a time.

The spreadsheet columns are labeled A through Z, then AA through AZ, BA through BZ and so on to column IV.  The standard column width is 8.43 spaces (but this can be varied from 1 to 255 spaces to permit longer text areas). Rows are numbered 1 through 65,536.  Depending on your monitor resolution and size, most monitors display 20+ rows at a time.

Spreadsheet "cells" are formed by the intersection of columns and rows. Every cell has a specific address or coordinate that corresponds to its particular column heading and row number.  For example, the cell located at the upper left corner of the spreadsheet (in column A and row 1) has coordinate A1, and the cell found at the lower right corner of the spreadsheet has coordinate IV65,536. Cell addresses are used in specifying relationships in the formulas.

Into each cell you can enter numeric values, formulas or characters.  While numbers of nearly any length can be entered into cells of any width, the screen will not display the number unless the column width is wider than the number. For example, to display a number with 6 digits the column must be at least 7 spaces wide.  The column must be wider when a negative number, or special formats are used. A numeric value with more digits than the screen can display will usually be shown as a row of asterisks, or in scientific notation.  Only the screen display is affected here; the number itself will reside in the cell whether or not we can view it.

Less important are the space limitations for labels.  Even though each cell can hold up to 240 characters, the column width limits the display.  Label displays are limited to the smaller of either the screen width (72 spaces) or one cell width plus the width of any empty cells to its right.

Within the spreadsheet is a cell which is highlighted. This might be considered to be Excel’s version of a cursor.  In a new spreadsheet, the cell A1 is always highlighted.  The pointer location is displayed in the left position of the top line on the control panel.  Although it ordinarily highlights only one cell, the pointer can be expanded to identify a range of cells for input or manipulation.

You can move the pointer in many different ways:

1. Values and formulas must always begin with either a number or a numerical indicator such as 0,1,2,3,4,5,6,7,8,9,0,+ , . , (, @, =
   
2. Do not use commas, spaces, dollar or percent signs when entering a number.  Excel will insert these symbols or commas as needed. 
   
3. Use only one decimal point.
   
4. A % sign after a number indicates percentage and causes the number entered to be divided by 100. 
   
5. Mathematical operators may be used in formulas to perform mathematical and logical operations.  These operators work in order of algebraic precedence, as described below.
   

• Negative

1. Formulas may contain actual values or values that are designated by referencing another cell (or cells) that contains the value.  For example, entering the formula +A1+A2+A3 into any cell will place into that cell the sum of values contained in cells A1, A2, and A3.
   
2. If you wish to begin a cell entry with a cell coordinate, start the entry with either an = ( or + character, or will interpret the entry as a label rather than a value. For example, instead of entering "A1", enter "(A1)" or "=A1".
   

Excel provides you with five methods for correcting a spreadsheet entry: 

1. Use the backspace [ ¬ BkSp ] key
2. Use the [Del] delete key
3. If entering data into a cell, cancel the entry with the escape [Esc] key.
4. Replace cell contents by typing over the current cell.
5. Erase cell contents (by using Edit Clear ).
6. Edit cell contents using the [F2] key, which invokes the edit mode.

If you want to change data that has been entered into the Formatting bar, but not yet saved into the cell, it is easiest to use the backspace key or the escape key. 

Backspacing. At the upper right of the keyboard is the backspace [¬ BkSp] key.  The backspace key always moves the curser to the left, in the direction of the arrow, deleting all characters as it moves.  The right or left arrow keys on the numeric keypad cannot delete characters.  Pressing these arrow keys enters data shown on the Formatting bar into the cell.

Canceling Cell Contents. Often is it more efficient to delete all of the characters stored in the Formatting bar rather than backspacing. By pressing the [Esc] key, the entire entry can be erased.  Note that this feature only erases data from the Formatting bar .  Once data has been transferred from the Formatting bar into a cell, you must either replace or erase the cell contents to change them.

Replacing Cell Contents. Replacing cell contents is easily done by positioning the pointer on a cell, entering the new data into the Formatting bar , and pressing [Enter] or an arrow key.  The old cell contents will then be replaced with the new data .  While you can use the space bar to replace or erase a cell, this causes Excel to store "spaces" in the cell, which inefficiently uses the computer's memory.

Erasing Cell Contents or the Worksheet. The contents of a cell can be erased by moving the pointer to a cell and using the Menu bar to select the Edit Clear command, or by using the / key to invoke the menu and selecting the letters associated with the clear option:

/[Edit] [cleAr] [All]

You can use this command to erase any rectangular group of cells.  Simply place the pointer on one of the corners of the rectangle and enter the following command:

/[Edit] [cleAr] [All]

Use the arrow keys to expand the pointer in any direction desired.  Selecting [All] option will erase the entire highlighted rectangle.

Sometimes it is preferable to start over with a new, clean spreadsheet.  Do this with the [File] [New] option:

/[File] [New] [OK]

Editing Instead of replacing the contents of a cell, the [EDIT] mode may be selected to edit cell contents. Editing a cell requires that a four step procedure be followed. 

1. Move the pointer to the cell you want to edit.
2. Press the [F2] (edit) key.  This copies the cell contents to the Formatting bar.
3. Edit the data displayed on the Formatting bar line by moving the cursor with the left or right arrow keys, by typing in new characters, or by using the [Del] or [¬ BkSp] keys to delete selected characters.
4. After editing is complete, press the [Enter] key to transfer the contents of the Formatting bar back to the cell.

Specifying A Range

Many commands require you to specify a "range." A range is any continuous collection of cells.  Ranges can include a single cell, a row or column, or a rectangular group of cells.  Single cell "ranges" are called by their cell address, such as B5 or A1.  Multiple cell ranges are identified by the opposite corner addresses of the rectangle: (i.e., B1..D3).  The first coordinate (B1) is the upper left corner of the range, while the second coordinate (D3) is the lower right corner of the range.

The range is required for, but is not limited to, the following Menu bar commands:

1. /[Insert] to insert or delete rows and columns.
2. /[Format] to set the format for the cell displays.
3. /[Edit] [Clear] to erase ranges of cells.
4. /[Edit] [Delete] to delete ranges of cells, rows, or columns

(deletion of cells removes the cells and shifts the neighboring cells to take their place). Alternatively, you can specify a range of cells and press the [Del] key.

5. /[Insert] [Name] [Create] to name the range.
6. /[Edit][Copy] to copy the contents of one range into another range location.
7. /[Edit][Move] to move a range within the spreadsheet.
8. /[File][Print Area] to designate ranges of cells or be printed.
9. /[Insert][Chart] to identify ranges of cells used to make a chart or graph.

1. Pointing to a range involves using the arrow keys to expand the pointer.  First, lock the upper left-hand corner of the range by pressing the [Shift] key. Second, expand the pointer (the range) as far as desired by repeatedly pressing an arrow key.  When the desired dimension is reached, use an arrow key to expand the range in a second direction if a rectangle is desired.  Finally, select the menu options using the mouse or / key.
2. Typing the Range is the second alternative for specifying a range.  For example, typing A1.B2 or A1:B2 will define a range composed of cells A1, A2, B1, and B2.
3. Naming the Range:  /[Insert] [Name] [Define] assigns a name to a specified range.  Once the range is defined and named, the name may be referenced instead of typing the cell address or pointing to the cell.  First, select the range to be named. Next, the /[Insert][Name] [Define] command causes the menu box to appear and prompt you for the name.
4. The /[Insert][Function] command is used to specify special functions that apply to specific cells or ranges that serve as an argument for the function. For example, the statistical function AVERAGE is entered as: =AVERAGE(B1.B10) and computes the average of the range B1 to B10. The @ or = symbol specifies a function, followed by the function name and parentheses containing the range to be operated on. Ranges are required for the math & Trig, Statistical, Logical, Text, Database, Lookup and Reference, date and time, and Information functions.
   

Absolute versus Relative Cell Addressing permits the copying of formulas in just the same way that numerals and labels are copied.  Formulas, when copied, will automatically change to reflect a new set of cells that have the same relative address positions to the cell copied...unless addresses are made "absolute". 

Formulas with relative cell addressing use data relative to the cell where the formula is located.  If the formula location is changed, the cells identified in the formula as the source of the data also change.  For example, if cell A2 contains the formula (A1+B1), the data is taken from the cell directly above the formula and the cell located one row up and one column to the right. When the formula is copied from cell A2 to cell B2, the formula would automatically change to read (B1+C1), again taking the data from the cell directly above the formula and the cell located one row up and one column to the right. 

At times, when copying formulas, we desire that the same cells be used, thereby avoiding this relative formula change. This requires use of "absolute" cell addressing. Absolute cell addressing would result in the formula inputs being held constant. The dollar sign ($) in the cell address may be used to anchor either the row or column number, or both of the coordinates.  All coordinates without the $ anchor remain relative. 

Consider the following three examples:

=$A$1+B1 =$A$1+C2 The Column A and Row 1 are anchored

=$A1+B1 =$A2+C2 Only Column A is anchored, leaving the Row to change

=A$1+B1 =B$1+C2 Only Row 1 is anchored, leaving the Column to change

The File option controls the overall spreadsheet formatting and file functions. New files may be created, existing files opened, and opened files closed.

Open allows files from other formats to be opened and defined for import, including text, Quattro Pro, Lotus 1-2-3, and Microsoft Works.

Close simply closes the open spreadsheet and allows you to save it when closing.

Save allows for saving the spreadsheet. If the spreadsheet has been saved previously, selecting the save option automatically saves the sheet under the default name.

Save As allows for saving the spreadsheet under a new name or new format. Formats include text, database, and many versions of Excel and non-Excel spreadsheets.

Save as HTML saves the spreadsheet as HTML for publishing in web documents.

Save Workspace Saves changes to the individual worksheets. This command is used in conjunction with saving a workspace file, which saves information about all open workbooks, such as their locations, window sizes, and screen positions. When you open a workspace file by using the Open command (File menu), Excel opens each workbook saved in the workspace.

Page Setup controls the formatting for the entire spreadsheet and includes settings for page orientation (landscape and portrait) and scaling, margins, headers and footers, and sheet print areas and page orders within the spreadsheet.

Print Area, Print Preview, Print Excel will print defined ranges of a spreadsheet.  The amount of print contained on a given page is limited by the size of the character font used to print the page. When the width of the range to be printed exceeds the physical page width, blocks of columns will be printed until the entire spreadsheet is completed. 

Print Area allows for the selecting of a specific range of the worksheet that is to be printed.

Print Preview allows for viewing the document to be printed prior to actual printing.

Print beings up the print dialog box that specifies the printer, whether the whole or selected area are printed, and gives the final command to print.

Send To is a useful command if you have an Internet mail connection. The spreadsheet can be attached to an E-mail and forwarded to a designated e-mail address.

Paste Special command has some features important in preparing an Excel spreadsheet. In Excel, values are often computed using formulas. If areas of the spreadsheet are copied, the formulas are copied with the areas. When the areas are pasted using the paste command, the relative addresses change and the values computed when the formulas are pasted back into the spreadsheet often become nonsense. The Paste Special command allows the copied values and their associated formulas to be pasted as copied, or to paste only the formulas, values, formats, comments, or without the borders. Additionally, the Paste Special command will allow the data to be transposed (a row of data to be converted automatically into a column).

Fill allows for the filling of a range in the spreadsheet with a given number or with a series of numbers. We would use the following steps to create a range of numbers from 1 to 10 in cells A1 to A10. First, enter the value 1 in cell A1. Next highlight the range A1 to A10 using the mouse. Finally, select [Edit][Fill][Series] on the Menu bar. Once in the series dialog box, make sure that the following are selected: Series in Columns, Linear and Step value of 1. Click [OK] and the series will be created. It is a quick and easy way to enter a long series of values.

Clear is used to clear comments, formats, or all values and labels from a cell, range of cells, or the entire worksheet if it is selected.

Delete removes the cells selected and moves the adjacent cells up or over to take their place.

Delete Sheet removes the entire worksheet contents and removes the worksheet from the workbook.

Move or Copy Sheet copies or moves the entire worksheet.

Find, Replace invokes the search and search/replace function for Excel.

Go To moves to a specified cell or named cell within the spreadsheet.

The view option menu also permits insertion of headers and footers, and the viewing of comments that have been inserted with the Insert Menu Options.

new data, labels, or formulas.

Inserting rows and columns requires precise positioning of the pointer.  Column insertion occurs directly to the left of the pointer.  Row insertion occurs directly above the pointer.  To insert columns, place the pointer directly to the right of where the columns are to be inserted and select [Insert] [Column]

Similarly, to insert a row, place the pointer on the row(s) directly below where the row is to be entered, and select [Insert] [Row]

For example, if two new columns were

were to be added, we would simply select the point for insertion

on the column labels, hold down the left mouse key, drag to cover two columns and then select [Insert][Columns] and the task is completed.

Deleting columns or rows is not part of the insert menu, but part of the [Edit] menu. It is mentioned here simply for association with Insertion. Deletion occurs by highlighting the columns or rows to be deleted and then pressing the [Del] key on the keyboard, or by highlighting and then entering the [Edit][Delete] menu command sequence.

Inserting cells requires that the area of insertion be highlighted, [Insert][Cells] be selected, and then you are asked which way the adjacent cells should be shifted.

Chart insertions require that the data to be charted be highlighted and the [Insert][Chart] command be selected. At this point, the chart wizard dialogue box appears and guides you through the chart setup procedure. The chart will be inserted into the worksheet.

Functions are inserted using the [Insert][Function] option. Functions include Math & Trig, Statistical, Logical, Text, Database, Lookup and Reference, date and time, and Information functions.

For example, the statistical function AVERAGE may be selected from the statistical functions using the function wizard. The function, when entered might appear as: =AVERAGE(B1:B10) assuming that we are computing the average of the range B1 to B10.

Name The name command assigns a name to a cell or range of cells in the worksheet. Names, once defined, can be referenced in formulas and logical statements, and provide absolute addresses to the cell even when the range is moved.

Comment Comments may be inserted as overlay dialogue boxes to draw attention or provide explanation for information contained in the cells.

Pictures and Graphics may be inserted into the worksheet in the form of clip art, organizational charts, maps, graphics files and word art.

Hyperlink Cells, pictures, or graphics in the worksheet may be linked to the World Wide Web or to files on your local computer through the [Insert][Hyperlinks] command. After links are created, clicking on the value in the cell or on the graphic will move you to the hyperlinked file or internet address.

The tools menu provides a broad variety of tools to the Excel user. Tools range from the standard spelling checker to protection options, to sophisticated linear programming models for solving for constrained solutions. Changes can be tracked, workbooks shared, and of course new programs developed with Excel’s Visual Basic Macro Command Language.

Most of these tools are problem-application specific and will be addressed in the context of specific marketing problems within the chapters of the text. Many of these chapters will focus on the development and use of Macros to solve marketing problems.

This completes the basic primer for Excel.  It is worth repeating that this is basic primer designed to start the user, but does not cover much of the detail available in other Excel command options. Consulting the Excel help will provide descriptions of the additional program capabilities. Chapters Three and Four build upon this introduction to by presenting "Advanced Commands: Graphics and Database", and "Macros: A Model Development Tool". These chapters acquaint you with the specific tools needed for model development using Excel.

CHAPTER PROBLEMS

ABC Corporation

Excel Exercise 2-1

ABC Corp. has assigned Bill Cohen and Michael Rubens to the sales accounts in the New England region of the United States. The table below shows their monthly sales results. As a sales manager, your task is to recreate the table using Excel. You may use the steps outlined below.

SALESPERSON RESULTS

A guide to setting up the worksheet:

1. Set the worksheet column width to 12.
2. Set the format of the worksheet to currency with zero decimal spaces.
3. Starting in row 2 column C, enter "Salesperson Results" as the table label.
4. In row 3 starting with column B, center the column titles.
5. Underline the column titles using border controls.
6. Enter the data in the table, right justify the months of the year and enter the sales data for each salesperson.
7. In the first row of the "difference" column enter formula that subtracts Michael's sales from Bill's sales. Copy the formula into the other cells.
8. At the bottom of each column, calculate the total sales and average sales and the total difference and the average difference using the sum and average functions.
9. Save and name the worksheet.
10. Print the worksheet.
11. Erase the worksheet. (You have already saved it.)

Ralph Johnson, the regional manager for XYZ Food Stores Inc., has given you the first quarter sales information for the grocery stores in your region:

Store 1: Sales in the meat department were $10,134, in the produce department they were $5645, and total store sales were $106,756.

Store 2: Meat department sales of $7567, produce department sales of $4589, total sales of $97,508.

Store 3: Meat department sales of $6732, produce department sales of $7849, total sales of $102,397.

Mr. Johnson wants you, his assistant, to prepare a report that presents the first quarter regional sales results by store and department. Create an Excel spreadsheet to present the data in an easy to read and understand format. The spreadsheet should look like the one shown below.

FIRST QUARTER REGIONAL SALES RESULTS, XYZ STORES INC.

Steps to set up the worksheet:

1. In cell B1 type the title
   First Quarter Regional Sales Results, XYZ Stores Inc.
2. Change the worksheet column width to 11 spaces.
3. Set the format of the worksheet to currency with zero decimal places.
4. In row 3 put the following titles in consecutive columns starting in column B: STORE 1, STORE 2, STORE 3, TOTAL, AVERAGE.
5. In column A beginning with row 4 and moving down the rows enter the following titles: MEAT DEPT, PRODUCT DEPT, TOTAL SALES, MEAT % TOT SALES, PROD % TOT SALES, STORE SALES %, TOTAL SALES. (Leave a space between the TOTAL SALES and the MEAT % TOT SALES row titles. Also split the last title into two rows in column A.)
6. Adjust the column width for column A so that the titles fit into column A.
7. Enter the store sales data in the appropriate cells.
   
   Perform the Calculations:
8. In the column labeled TOTAL, use the sum function to sum the meat department sales. Copy the formula to the TOTAL PRODUCE and TOTAL SALES cells.
9. In the column labeled AVERAGE use the average function to calculate average sales for the meat and produce departments and for total sales.
   
   Percentages:
10. The cells with percent calculations need to be percent formatted, therefore, use the range format command to format the cells as a percentage with one decimal place.
11. For Store 1, compute the meat department sales as a percentage of TOTAL STORE SALES. (Divide the meat sales in Store 1 by the total sales in Store 1.) Copy the formula into Store 2, Store 3, and total columns.
12. Perform the same operation for the produce department.
13. Compute the store sales as a % of regional sales by dividing the total store sales by the total regional sales. Use an absolute cell address for the total regional sales and copy the formula into the other two store columns.
14. Save the worksheet.
15. Print the worksheet.
16. Erase the worksheet.