Database manipulates or analyzes the data entered into the spreadsheet using specific command and data manipulation procedures. Excel offers many data manipulation procedures that expand the functionality of Excel to be both a programming language and a dynamic interface for web based databases. In the next chapter, we will discuss using the Visual Basic Language Editor. In this chapter, we will discuss several of the basic database procedures that do not require programming and may be performed as options within the [Tools] and [Data] submenus.  With these five procedures, we are able to perform basic database manipulation and analysis:

1. Sorting records (alphabetically, numerically, or by date),

2. Searching for and manipulating data records,

3. Constructing data output tables,

4. Producing frequency distributions, and

5. Performing statistical analysis.

A database consists of values or labels entered into the spreadsheet. A database is an information set having one or more spreadsheet columns and at least two spreadsheet rows.  The first row of a database contains the "field" name(s) for the database, while the second and subsequent rows contain the database itself.  Each row of the database is called a "record". Each column in the database is called a "field".   

A flat database is a rows by column two dimensional database that may be displayed on a single screen. This database is the type you create with any spreadsheet program.

A relational database is a 3 dimensional database that adds one or more tables to the flat database by having a shared variables

Entering Data The procedure for entering database values and labels is about the same as for the spreadsheet, although if a permanent database is being constructed that will be used or updated by several individuals, you may want to create a template for data entry using the [Data][Form] option. The [Data][Form] option requires that you have a row containing the data field names and a sample row of data. Once the data for these two rows are entered, highlight the two rows and then select [Data][Form]. The above form will appear and you can select "New" to enter a new row of data in your database. The following information will help you in planning your database.

The database is limited to 65,536 rows (2¹⁶ ) and is further limited by the amount of memory (RAM) in your computer. If larger databases are required, you should consider moving to an Access database which utilizes hard drive space for storing and manipulating the database.

Setting up your database requires setting up a data structure. This structure establishes the fields (columns) and rows for the database. The following guidelines will prove to be very valuable in setting up any database.

• Use the first row in each field to enter the field name. This field name is a unique identifier for the data in the column and is used in Excel to specify field manipulation options.
• Determine the layout of the columns in terms of which field comes first, second, and so forth. Be sure to include a field as a row or record identifier. This is important so that you can re-order your records if you desire.
• Set up a separate column for each field or piece of data within each record. Don't leave blank fields between the data fields, but adjust the database by modifying column widths, typefaces, alignment, and other formatting variables to modify the appearance of the database.
  

The Data menu allows for data manipulation of fields data in the spreadsheet. Databases may cleaned, parsed, organized, and presented as professional looking reports. The first five data menu options allow the database to be sorted, filtered to show a subset of the data that meets the conditions specified by logic or boolean operators, subtotaled, and validated by screening the data for specific values that are to be deleted from the database.

Table is a tool for constructing one or two way tables from a column and row input values and from a formula. Given input values, the cells of the table are computed. As the input values are changed or updated, the values of the table will change automatically.

Text to Columns is a parsing tool for converting a text file to fixed length database fields.

Template Wizard helps you create a template you can use to enter data in a database that you link to the template. The database can be constucted in Microsoft Excel, Microsoft Access, Microsoft FoxPro, dBASE, or a Paradox database. When a new workbook is created based on the template, the information entered in the workbook can be saved as a new record in the database.

Consolidate provides

In the next chapter, the advanced Excel commands for graphics and database will be discussed.

A more sophisticated sort may be performed that will sort up to three fields at a time using the [Data][Sort] command from the menu bar. To sort using the data sort command, position the cursor within the database, select [Data][Sort], and then use the pull down menus to select the sequence of the variables to be sorted and whether the sort is ascending or descending.

When specific fields are selected (by selecting the column headings), the data sort will operate only on those fields selected rather than on the entire database. This option should be used with caution, as sorting on a select group of fields will effectively randomize the relationship between rows of the database. For example, if the rows represent a customer's data, sorting by a selected field only rather than the entire database will assign another customer's data to that field within the customer's row. Caution and keeping a backup to your database file is the rule.

Data within the database can be subtotaled to form tables using the [Data][Subtotal] command. In the example below, the worksheet was formatted using the standard [Format][Autoformat] Accounting1 table format, and then was highlighted and subtotaled. The Subtotal menu box on the right shows that each row in the table was summed and subtotaled at the bottom of the table. Subtotals were specified for the Speed, Cleanliness, Convenience, and Value variables. Subtotals are simply a function with three parameters that specify the function to be selected, the start and end of the range to be subtotaled.

Subtotal functions include

Sum: Returns the sum of the numbers in a field

Count: Returns the frequency count of the numbers in the field

Average: Returns the average value of the numbers in the field

Maximum: Returns the maximum value of the numbers in the field

Minimum: Returns the minimum value of the numbers in the field

Product: Returns the product of all values in a specified group in the field

Count Nums: Returns a count of all cells in the field that have a value

StdDev. Returns the standard deviation (Square root of the variance from the average)

StdDevp. Returns the standard deviation adjusted for the population size

Variance Returns the variance of the numbers in the field

Varp Returns the variance of the numbers in the field adjusted for population size

Functions allow you to analyze ranges of data and fields of your database. In addition, functions may be used to perform logical, financial and statistical analyses.

Functions are identified in the [Insert][Function] menu to include a variety of general categories of operators: Financial, Date and Time, Math and Trig, Statistical, Lookup and Reference, Database, Text, Logical, Information, and Engineering.

Because we are discussing features of the database, let us consider the database functions first, and then the other functions that may be inserted into the worksheet.

DAVERAGE Average of all values in a field that meet the designated criteria

DCOUNT Frequency count of all values in a field that meet the designated criteria

DCOUNTA Frequency count of all non blank cells in a field that meet the designated criteria

DGET Returns a single item that meets the criteria. Multiple resulting items produce an error

DMAX The maximum value of the values in the field that meet the criteria

DMIN The minimum value of the values in the field that meet the criteria

`DPRODUCT Returns the product of all values in a field that meet the criteria

DSTDEV The standard deviation based on a sample from the database.

DSTDEVP Returns the standard deviation of the entire population (entire database)

DSUM The sum of all values in the field that meet the criteria

DVAR The variance from the average of a sample from the database

DVARP The variance from the average based on the entire population (entire database)

GETPIVOTDATA Returns data stored within a pivot table

Details of Financial, Logical and Statistical databases are contained in the Appendix to Chapter three.

Data tables are a simple, easy to use method of automatically computing table values from tables with one or two variables that change along the rows or columns of the worksheet. In the example we will show here, we compute an s-shaped function that depicts the traditional form of the product life cycle. This function, written , where P_t is the purchase rate for time period t; S is the maximum purchase ratio; R is the percentage of S that repurchases the product; and t is the time period (year 1-4). As we can determine from the formula, we have ( Total Market - (Non-Repurchasers raised to some exponent that becomes smaller with time)). As the repurchase rate increases, the cumulative sales of the product will increase over time, but decrease at an exponential growth rate. Setting up formula shows clearly what happens when we increase product quality or value to increase the repurchase rate, or conversely decrease our marketing efforts.

The one variable data table is formed by entering the variables for the table in either a row or column. In the example, the repurchase rates R, are entered into the column at cells A62:A66. The column input cell (the data is in the column, so the input cell is a column input cell) is located in cell A67. The formula must be entered into the cell one row above the first value and one column to the right (in this case, cell B61). If several columns of data are to be computed, as in this case, you may copy the formula into multiple cells as in the example: =$A$68*(1-(1-$A$67)^(2^(B60-1))) In the example, the formula is copied into cells C61, D61, and E61.

To populate the cells of the table, we simply highlight the cells containing the formulas, values and the cells to be populated (A61:E66), select [Data][Tables], specify the column input variable (A67) and press enter. The values for the cells of the table will be computed and entered.

The two variable data tables operate in basically the same way as the one way table, except that the Two-variable data tables use only one formula (A62) with two lists of input values. The formula must refer to two different input cells (F62 and A68), both of which are referred to in the formula.

To populate the cells of the table, we simply highlight the cells containing the formulas, values and the cells to be populated (A62:E67), select [Data][Tables], specify the column input variable (A68) and the row input variable (F62) and press enter. The values for the cells of the table will be computed and entered.

SOLVER

Solver is an Excel Add-in that can solve problems with multiple interdependent variables. Solver uses linear algebra techniques to optimize problem solutions. Solver appears in the [Tools][Solver] menu option. If it is not present, you will need to (1) use the Add-ins dialog box, or if not shown in this dialog box, (2) use the Excel install option to Add/Remove program options to add the Solver Add-in. The Excel installation CD-ROM also contains a number of Solver examples in the file SOLVSAMP.XLS. This Excel file contains multiple worksheet files of which one is an example of product manufacturing planning. Load the Product Mix example for optimizing product manufacturing alternatives where costs and profits vary depending on what we are willing to manufacture.

After loading the SOLVSAMP.XLS workbook and the Product Mix workpage, we may select the [Tools][Solver] menu command to invoke the Solver Parameter dialogue box. The dialog box identifies

1. the Target cell ($D$18)
2. the maximization option
3. the cells to change ($D$9:$F$9)
4. the constraints $C$11:$C$15 <= $B$11:$B$15 and $D$9:$F$9 => 0 that is, we may not use more parts in any given category than we have in inventory, and we cannot have a negative number of products built in any given category.

Selecting the Solve option from the dialog box optimizes the function and produces a result of $14,917 profits by building 160 TV sets, 200 Stereos, and 80 speakers.

The Options button in the Solver Dialogue Box contains the advanced control features for the solution process, loading or saving problem definitions, and defining parameters for both linear and nonlinear problems. The default settings are present for each option are appropriate for most problems.

Max time Limits the time taken by the solution process. While you can enter a value as high as 32,767, the default value of 100 (seconds) is adequate for most small problems.

Iterations Limits the time taken by the solution process by limiting the number of interim calculations. While you can enter a value as high as 32,767, the default value of 100 is adequate for most small problems.

Precision Controls the precision of solutions by using the number you enter to determine whether the value of a constraint cell meets a target or satisfies a lower or upper bound. Precision must be indicated by a fractional number between 0 (zero) and 1. Higher precision is indicated when the number you enter has more decimal places 3/4 for example, 0.0001 is higher precision than 0.01. The higher the precision, the more time it takes to reach a solution.

Tolerance The percentage by which the target cell of a solution satisfying the integer constraints can differ from the true optimal value and still be considered acceptable. This option applies only to problems with integer constraints. A higher tolerance tends to speed up the solution process.

Convergence When the relative change in the target cell value is less than the number in the Convergence box for the last five iterations, Solver stops. Convergence applies only to nonlinear problems and must be indicated by a fractional number between 0 (zero) and 1. A smaller convergence is indicated when the number you enter has more decimal places 3/4 for example, 0.0001 is less relative change than 0.01. The smaller the convergence value, the more time Solver takes to reach a solution.

Assume Linear Model Select to speed the solution process when all relationships in the model are linear and you want to solve either a linear optimization problem or a linear approximation to a nonlinear problem.

Show Iteration Results Select to have Solver pause to show the results of each iteration.

Use Automatic Scaling Select to use automatic scaling when inputs and outputs have large differences in magnitude 3/4 for example, when maximizing the percentage of profit based on million-dollar investments.

Assume Non-Negative Causes Solver to assume a lower limit of 0 (zero) for all adjustable cells for which you have not set a lower limit in the Constraint box in the Add Constraint dialog box.

Estimates Specifies the approach for initial estimates of the basic variables in each one-dimensional search.

Tangent Uses linear extrapolation from a tangent vector.

Quadratic Uses quadratic extrapolation, which can improve the results on highly nonlinear problems.

Derivatives Specifies the differencing used to estimate partial derivatives of the objective and constraint functions.

Forward Use for most problems, in which the constraint values change relatively slowly.

Central Use for problems in which the constraints change rapidly, especially near the limits. Although this option requires more calculations, it might help when Solver returns a message that it could not improve the solution.

Search Specifies the algorithm used at each iteration to determine the direction to search.

Newton Uses a quasi-Newton method that typically requires more memory but fewer iterations than the Conjugate gradient method.

Conjugate Requires less memory than the Newton method but typically needs more iterations to reach a particular level of accuracy. Use this option when you have a large problem and memory usage is a concern, or when stepping through iterations reveals slow progress.

Load Model Displays the Load Model dialog box, where you can specify the reference for the model you want to load.

Save Model Displays the Save Model dialog box, where you can specify where to save the model. Click only when you want to save more than one model with a worksheet 3/4 the first model is automatically saved.

The Answer, Sensitivity, and Limits reports show the optimized answer, the calculation of the relative sensitivity of each variable, and the effects of the constraints on SOLVER. Reports are accessed from the Solver Results Dialogue box that appears after the problem is solved.

Goal Seek is a single input variable problem solver that is much simpler to use and faster than Solver. Goal Seek does not have the capability to solve problems where there are multiple input cells and constraints on the solution. Goal Seek operates to solve for a given parameter value required by a given financial, statistical, math and trig, or engineering function. In the following example, we will use goal seek to identify how much we can spend on an automobile given current interest rates, a 48 month loan package, and our family budget that allows $325 to be spent on the car per month.

The spreadsheet must be setup to solve for the given function we desire. In the current example, we would use the PMT function to identify the payment amount. PMT requires three parameters: Number of months, interest rate per month, and the total value of the purchase:

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

Syntax: PMT(rate,nper,pv,fv,type)

In this example, the function is expressed as: =PMT(B46/12,B45,B44)

The [Tools][Goal Seek] menu option will invoke the Goal Seek dialog box, which is setup to solve for a value of cell $B$44 by constraining the payment (cell $B$47) to $325.00.

The Graphics and database functions in Excel offer powerful tools to the manager, whether the focus is on creating a database, presenting charts to explain the results, or solving problems. This chapter showed the basics of charting with Excel, with a focus on 12 charts for market segmentation, tracking and analysis. In the database section, we examined the basic process for design and layout of a database, and the use of a variety of functions for the database and the worksheet in general. We concluded by examining specialty tools for problem solving: Tables, Solver, and Goal Seek.

In the next chapter, we focus on Excel macros to solve new types of problems...those that require programming in Visual Basic for applications.

Do all of the following problem sets on the same worksheet.

Your manager, Erin Shearson, is the national sales manager for the Wally Widget Corporation, and has just receivedthe regional sales results for the three products that you manufacture and market. Ms. Shearson has asked you to tabulate the data and determine which regions sell the most of each product. Table 1 shows what she expects to see.

1. Format the entire worksheet to currency with zero decimal places. Range format the cells that do not need thecurrency format.
2. Put the region numbers in the region column, and recreate Table 1. Include the table and column titles shown below.
3. Use the Sum function to calculate the total sales for each product.

TABLE 1
NATIONAL SALES STATISTICS BROKEN DOWN BY REGION (2001)

1. Copy Table 1 excluding the Total Sales line to a blank position on the spreadsheet below Table 1.
2. Change the titles of the newly copied table to: "TABLE 2" and "PRODUCT 1 - HIGHEST TO LOWEST REGIONAL SALES."
3. Sort regional sales from highest to lowest on Product 1. The new table should look like Table 2, shown below.

TABLE 2

PRODUCT 1 - HIGHEST TO LOWEST REGIONAL SALES

Create Tables 3 and 4 shown below by copying Table 1 two times to blank parts of the spreadsheet (exclude the TotalSales line). Change the titles, and sort the tables according to sales of product 2 and 3 respectively.

Save the worksheet because the tables will be used in the following problem sets.

TABLE 3
PRODUCT 2 - HIGHEST TO LOWEST REGIONAL SALES

TABLE 4
PRODUCT 3 - HIGHEST TO LOWEST REGIONAL SALES

You have forecasted the annual sales growth rates for the next five years for each of the three products marketed by XYZ Corp. The growth for the products are as follows: rate for PRODUCT 1 is 2.0%, for PRODUCT 2, 25%, and for PRODUCT 3, 10%. You want to make a table that shows the forecasted sales for each product for the next five years. The total sales for the current year, 2001, were calculated in Problem 1. Recreate the table shown below on the same spreadsheet used for problem set 1.

1. In both locations of the table that list the current year's (2001) sales use a relative cell address formula to address the total sales calculated in Table 1 of the previous problem.
2. Use the Fill handle to number the columns from 0 to 5 in the year row.
3. Calculate the sales forecast for each year using a formula that addresses the cell which contains the estimatedpercent growth rate and the cell which contains the previous year's sales. (Hint: forecasted sales = previous year sales * (1 + growth rate percent). You will want to use both relative and absolute cell address in the formula.)
4. Save the worksheet for use in Problem Set 3.

Ms. Shearson needs graphs of this year's sales data and the sales forecast data. The graphs will be used in a presentation to top management. She has asked you to prepare "Professional Looking Graphs".

1. GRAPH 1: Make the pie chart shown below using the data from Problem Set 1, Table 1.
2. GRAPH 2: Make the bar chart shown below using the data in Table 1.
3. GRAPH 3: Make the multiple bar chart shown below using the data in Table 1.
4. GRAPH 4: Stacked Bar: The multiple bar graph can be easily made into a stacked bar graph by changing the graph type from bar to stacked bar.
5. GRAPH 5: Make the Line graph shown below using the sales forecast data in the table created in Problem Set 2.