Analytic Techniques for Business Modeling: Opportunities for Advance

(1)

(2)

Analytic

T

ec

hniques for

Business Modeling:

Opport

unit

ies

for Advance

libliotheek TU Delft

IIIII I IIII

I \1

III

\1

I \\111

\1\1 II

\\1\

C 08031138'3

(3)

WBBM

Report Series

WBBM

Delft University of Technology

Faculty of Information Technology and Systems Department of Mathematics and Computer Science Room ET 05.040

Mekelweg 4

2628 CD Delft, The Netherlands Phone +31 15 278 16 35

Fax +31 15 278 72 55

(4)

Analytic Techniques for Business Modeling:

Opportunities for Advance

F.P.M. Schouten

(5)

The WBBM Report Series is published by: Delft University Press

Mekelweg 4

2628 CD Delft, The Netherlands Phone +31 15 278 32 54

Fax +31 15 278 16 61

Editors: E. de Klerk H. van Maaren

Delft University of Technology

Faculty of Information Technology and Systems Department of Mathematics and Computer Science

CIP-GEGEVENS KONINKLIJKE BIBLIOTHEEK DEN HAAG Schouten, F.P.M.

Analytic Techniques for Business Modeling: Opportunities for Advance / F.P.M. Schouten - Delft : Delft University Press. - Ill. - (WBBM Report Series 39)

ISBN 90-407-1765-5 NUGI 841

Trefw.: business modeling software

No part of this book may be reproduced in any form by print, photoprint, microfilm or any other means, without written permission from the publisher: Delft University Press, Mekelweg 4, 2628 CD Delft, The Netherlands.

(6)

Acknowledgements

Chapter 1 Introduction

1.1 Company Overview

1.2 1.3

1.1.1 Business ConsuIting and Training

1.1.2 Product Development and Technical Support 1.1.3 Administration, Marketing, and Sales Project Goals

Report Outline

Chapter 2 Advance Software Overview

2.1 2.2 2.3 2.4 2.5 2.6 Introduction What is Advance?

Principal Features of Advance How Does Advance Work?

An Example of Simple Advance Model Mechanics Product Development Plans and Timeframe 2.6.1 Stage 1 2.6.2 Stage 2 2.6.3 Stage 3

1

1 2 3 3 5

7

7 8 8 10 13 17 17 18 18

(7)

Analytic Techniques for Business Modeling: Opportunities for Advance

Chapter 3 Task Identification

19

3.1 Understanding of Advance and its Possible Enhancements 19 3.1.1 Gather Modeling Experience and Understanding ofthe 19

Advance Paradigm

3.1.2 ltemize Possible Added Functionalities 19 3.2

3.3

3.4

Researching Existing Client Base and Possible New Applications 3.2.1 Research Existing Client Base

3.2.2 Research Possible New Applications Implementing the New Functionalities

3.3.1 Analytical/Statistical Plug-ins and Their Pricing 3.3.2 Advantages/Disadvantages of Both Methods Conclusions and Recommendations

Chapter 4 Possible Functional Additions

4.1

4.2

4.3

4.4

Simple Mathematica! Functions Linear Regression 4.2.1 4.2.2 4.2.3 4.2.4 4.2.5 4.2.6 4.2.7 Regression Basics

Point Estimation Using Least Squares Goodness of Fit

Hypothesis Testing and Confidence 1ntervalsfor the Least Squares Estimates

Multicollinearity

Residual Analysis to Check 1nference Assumptions General Remarks

Time Series Analysis

4.3.1 Exponential Smoothing 4.3.2 Box-Jenkins Techniques

4.3.3 1mplementation of Time Series Analysis Monte Carlo Simulation and Dependence Modeling

20 20 20 20 21 21 21

23

23 23 24 25 26 26 27 28 30 30 31 33 37 38

(8)

4.5 4.6 4.7 Mathematical Programming 4.5.1 4.5.2 4.5.3 4.5.4 4.5.5 Linear Programming (LP) Goal Programming Quadratic Programming CPMandPERT

lmplementation of Mathematical Programming

Scripting

Decision Trees, Influence Diagrams, and Neural Networks

4.7.1 Decisio.n Trees 4.7.2 lnfluence Diagrams 4.7.3 Neural Networks Contents 39 39 40 41 42 47 47 48 48 50 52

Chapter 5 Market Research

57

5.1

5.2

5.3 5.4

Existing Clients and Markets

57

5.1.1 Management Consultants, Financial Analysts, and 57 Business Modelers

5.1.2 CFO's, Financial Managers, Directors, and Other Executives 60

Lighten's Strategie Plans 5.2.1

5.2.2

Classifying Advance and Advance-based Solutions Competition

Pos si bie New Markets and Applications Relevanee of the Different Methods

5.4.1 Past Research

5.4.2 Relevance with Respect to Lighten and Advance

62 63 65 66 67 67 69

Chapter 6 Implementing New Functionalities

73

6.1 Statistical/Analytical Plug-ins and Their Pricing 73

6.1.1 Plug-ins Offering Regression and Time Series Modeling 73

6.l.2 Plug-ins Offering Simulation and Dependence Modeling 75

6.l.3 Plug-ins Offering Mathematical Programming and 76

(9)

Analytic Techniques for Business Modeling.: Opportunities for Advance

6.2 AdvantageslDisadvantages of Both Methods

Chapter 7 Conclusions and Reeommendations

7.1 Conclusions

7.1.1 Advance for Business Modeling Applications 7.1.2 Market Trends

7.1.3 Relevant Functional Additions

7.1. 4 Implementation M ethod 7.2 Recommendations

Referenees

Appendix A

AppendixB

Appendix C

Summary

7.2.1 Implementation of Analytic Techniques 7.2.2 Market Development

Simple Exponential Smoothing

Form Letter to Existing Clients

Bog\e

&

Gates Strategie Planning Model

~amenvatting

(Duteh Summary)

78

81

81 81 82 83 85 87 87 88

91

93

97

101

103

105

(10)

Acknowledgements

This report was written as part of the two-year post-graduate program called Mathematical Decision Support Models at Delft University of Technology in Delft, The Netherlands. This report is the result of a one-year internship at Lighten, Inc., located in Berkeley, Califomia, from September 1997 through August 1998. Lighten are a small software and business consuIting company, and are the makers of business modeling software called Advance.

Supervisors:

Prof. dr. R.M. Cooke Mr. C. Teller

Examination Committee: Prof. dr. R.M. Cooke (Chairman) Dr. H. van Maaren

Dr. E. de Klerk Mr. V. Kritchallo Mr. M. Bjomestad

Delft University of Technology, Delft VP Business Development, Lighten, Inc., Berkeley, CA

Delft University ofTechnology, Delft Delft University of Technology, Delft Delft University of Technology, Delft President, Lighten, Inc., Berkeley, CA First of all, I would like to thank a number of people at Lighten. l' d like to thank Chuck Teller, Owen Davis, Marshall Miller, and Mo Bjomestad for making this internship possible, and for their guidance and support. Additional thanks to Tony Tisdale and Stefan Mihaylov for being such good colleagues and friends during the past year.

l' d also like to thank some people at Delft University. First of all Roger Cooke, for all the guidance and support throughout this internship. Also, thanks to Hans van Maaren, Etienne de Klerk, and Valerie Kritchallo for their time and efforts. Finally, 1'd like to thank Etienne de Klerk and Michiel Odijk for their help with the operational and tinancial arrangements of the internship.

Berkeley, CA - August 1998 Lighten, Inc. 2124 Kittredge St. Suite 775 Berkeley, CA 94704 United States Tel.

++ 1 (510) 528-4376

Fax.

++ 1 (510) 528-0246

http://www.lighten.com Florian Schouten Delft University of Technology Statistics, Probability Theory and Operations Research Faculty of ITSITWI Mekelweg 4, 2628 CD Delft The Netherlands Tel. ++ 31 (15) 2781635 Fax. ++ 31 (15) 2787255 http://ssor.twi.tudelft.nl

(11)

(12)

Chapter 1 Introduction

This chapter introduces the reader to Lighten and Advance. It also contains a section outlining the project goals. Finally, the third section outlines the structure of the rest of this report.

1.1 Company Overview

While working at a bank as a financial analyst, Marshall Miller developed an MS-DOS program - Advance™ - which embodied a new method for creating and maintaining financial modeis. Mr. Miller wrote the program because available software tools were inadequate for creating the financial models required by the bank.

Over the first year of use at the bank it became evident that Advance was really well suited for business modeling applications. In March of 1992 Mr. Miller obtained the copyright to Advance from the bank and left the bank to develop the program.

Lighten, Inc. was founded in April of 1992 to further develop Advance and take it to market. During the last five years, Advance has made considerable progress.

Lighten's current daily activities can be split into three major categories:

1.1.1 Business Consuiting and Customer Training

All of Lighten's consuiting and training work is based on Advance. The goal of the consulting activities is to:

a) generate some revenue besides software sales;

b) further encourage the use of the product by teaching clients how to build modeis. Lighten provides consuiting services to clients from a wide variety of businesses. Originally, the focus of consulting was mainly on modeling work for local govemments, eities and counties. Usually, Lighten would build a budget model for these organizations, bringing together their departmental revenues and expenditures. This model would not only contain historical data at different levels of detail, but would also create cost and revenue projections, based on a number of demographic drivers (e.g. county population) .. an~ key indicators (e.g. inflation).

(13)

Eventually Lighten started doing consuiting projects tor other types of business es and applications. Some current applications of Advance are:

• Business planning and financial business analysis: analyzing company data, budgeting revenues and expenses, calculating growth projections, and conducting what-if analyses.

• Merger and acquisition analysis: analyzing merger and acqUlsltJon data and calculatinglprojecting the consequences of corporate take-overs.

• Retail analysis: consolidating sales and operating expense data from different stores and/or regions and doing store-by-store comparisons.

• Real estate analysis: using land development information to make cash flow projections and to make land use decisions.

• Project management: budgeting, organizing and tracking project expenses by week, by staff member, by expense type, and by project.

• Management Information Systems (MIS): consolidating critical company information into management reports for enterprise-wide analyses; organizing and displaying multidimensional data in many different ways, allowing the user to change the layout of reports by simply dragging and dropping data dimensions to their new locations.

Several outside consulting companies have started using Advance to build business models for their clients.

Lighten also plans to start developing template models that can be sold separately. For instance, someone could buy a template project management model, which contained the structure and functionality of a standard project management application. The customer would then only have to enter his own hi storic al or current data, and would then have a fully functional business model at only a fraction of the price of a completely customized model or a canned project management application.

1.1.2 Product Development and Technical Support

Lighten is currently developing version 2.0 of Advance. This new version will contain some major changes from the previously released versions 1.0 through 1.22. One of the major changes will be the porting of the source code from Pascal to C++ and going from 16 bit to 32 bit. The interface wil! also undergo considerable changes. Besides that, work is being done to expand Advance's function library and to make it possible to link several Advance models together. Another goal is to develop a version of the software that uses client-server technology to allow collaborative model building.

(14)

Chapter 1 Introduction

Chapter 2 wil! contain a more detailed description of the product development plans and timeframe.

1.1.3 Administration. Marketing. and Sales

In the last year Lighten has increased its marketing and sales efforts and has started coordinating its public relations efforts. The fol!owing explains what channels Lighten is using:

Direct Sales Force/Solution Managers:

Lighten is building a direct sales force that consists of individuals with strategie planning and business modeling experience.

Solutions Partners & Value-Added ReseIIers:

Lighten is developing strategic partnerships with key management consultants throughout the United States. To serve customers, who do not require Solutions Partners, Lighten plans to develop a network of V ARs who can sel! Advance as a stand-alone product.

Direct Solicitations & Internet Marketing:

The Lighten sales staff is pursuing the sale of Advance through direct solicitations to prospective clients. Keeping track of people th at download an evaluation copy of Advance generates most of the leads.

Lighten will market Advance through the Lighten web site: www.lighten.com. The web site currently averages around 10 hits per day. Lighten's web site has been redesigned and a number of new materials have been made available for download, including some Advance sample modeIs.

Expositions and Conferences:

Lighten is targeting specific expositions and conferences to market Advance and solutions/models built in Advance.

Custom Model Development:

Lighten is providing custom model development as part of the product evaluation. As part of their coiisulting services, Lighten is also developing custom models for clients on a contract basis.

1.2 Project Goals

This section wil! outline the goals of the project with Lighten.

As mentioned in the previous section, one of Lighten's major product development areas is Advance's function library, especially the development of its analytical and statistical tools. A number of Lighten's potential customers have lost interest in Advance in the past because it lacked certain analytical capabilities. Today's business modeIer uses sophisticated methods and techniques, and wants a tooi that can fulfill his or her modeling needs. The use of simulation, statistical distributions, and multivariate

(15)

AnalytiC Techniques for Business Modeling: Opportunities for Advance

regression (to name but a few) in business applications is becoming more widespread. Therefore, any business analysis software needs to be updated and improved constantly to keep up with the mo~eling needs of its users and to stay competitive.

There is another major factor that contributes to the importance of software development: adding new features to your software allows you to make an entrance into new markets and sell upgrades to existing clients. In Lighten's case, adding analytical and statistical capabilities to the software allows the introduction of Advance into new markets and applications, such as risk/uncertainty analysis, multi-criteria decision analysis, inventory contro!, distribution management, and other mathematical modeling problems.

There are many issues that have to be addressed when developing and implementing new software functionalities. Limiting factors of time, money, and manpower make it impossible to implement all desired new features at once. This means choices have to be made with regard to which new functionalities in the software are the most critical and useful, and therefore which functionalities should be implemented first.

Another issue arises from analyzing the mathematical and statistical software that is currently available on the market: it will be difficult to come close to the level of sophistication of certain specialized software packages. The question is if it' s wise to develop a mathematical/statistical function library in Advance itself, or if Lighten would be better off focusing on ways to link Advance to an extemal plug-in or engine which makes the expertise of others available within Advance. To make this decision, Lighten needs to evaluate both alternatives, consider their development feasibility and cost, and see this decision in the light of their company goals.

This leads to the goals of this internship at Lighten:

• Based on modeling experience in Advance and interviews with existing and potential clients, identify the most essential and useful statistical and mathematical functionalities that should be incorporated into Advance.

• Identify which new markets would open up if these new tools are implemented, and deterrnine how Lighten's position in existing markets would change.

• Evaluate the two approaches to incorporating mathematical and statistical functionalities in Advance (own development and extemal plug-in), and come to a well-founded conclusion regarding this decision.

Clearly these internship objectives cannot be seen as three separate goals; they are related, and therefore any decision or result obtained in one of the objectives greatly influences the outcome of the other two. This means that these three objectives need to be dealt with simultaneously rather than one at a time.

(16)

Chapter 1 Introduction

1.3 Report Outline

C1lapter 2 provides a background for the objectives listed above. It gives an overview of the key concepts behind the Advance software and gives an example of model building in Advance.

Chapter 3 outlines the tasks that need to be completed to make sure all relevant factors are considered in the decision-making process.

Chapters 4,5, and 6 then perform these tasks, by evaluating possible functional additions to Advance, analyzing the market place, and looking at development issues, respectively. Finally, Chapter 7 contains some concIusions and recommendations. It also contains a

(17)

(18)

Chapter 2 Advance Software Overview

This chapter contains a comprehensive description of how Advance works. It outlines the

principal features of the software and gives an example of an Advance model.

2.1 Introduction

Almost all companies and organizations need to do some kind of financial analysis and planning. For most companies this incIudes tasks such as comparing actual department or product performance to an annual budget, projecting revenues and expenses, generating pro-forma financial reports (e.g. income statement, balance sheet) from operational data, performing what-if analyses on revenue or expense factors, and tracking profitability of different departments over time.

For the last ten years, most people have used spreadsheets, such as Microsoft ExceJ, to perform these tasks. Although spreadsheets have developed into a powerful tooI, they still have many drawbacks. One of the major drawbacks becomes apparent when analyzing

complex information or large quantities of data: spreadsheets are limited in the number of

data dimensions they can handJe and display. Business data often has more than three

dimensions, or it has several dimensions used in different combinations. For example, a

company's sales can vary by month, region, product type, and stores. Although it's

possible to analyze multidimensional data in a conventional spreadsheet, it is not easy. The other tooI for analyzing business data has been the database. The database is an excellent tooI for organizing large amounts of data and for defining the reJationships between data:..,i!ets. However, it is not easy to use the database for creating forecasts and for performing what-if analyses. Additionally, the database can usually not be used to

view the data in a multidimensional format. . Data extraction will generate

two-dimensional reports, which can be hard to read if you're dealing with more than two data

dimensions.

The third possible approach to business analysis and planning is using a decision support system (DSS), which can be seen as a piece of software specifically designed to solve a particular analysis or planning problem. This category incIudes software/code written

directly by the analyst himself, but also commercial budgeting software, project

management software, or forecasting packages. In other words, the software in this

category is not an all-round modeling and analysis package, but more of a "canned

(19)

In recent years there has been a growing awareness that a great deal of business data is multidimensional in nature. Conventional tools listed above are usually not weil suited for this data. A number of tools have appeared which address this problem; they are usually referred to as "OLAP tools", or On-Line Analytical Processing tools. These tools store data in a multidimensional form, and make it easier to organize and analyze historical data, allowing the user to "drill down" into more detail or to "roll up" detailed data to summary reports by simple mouse-clicks. But analyzing historical data is only one element of most decision processes: planning and feedback are also crucial elements. The majority of OLAP-tools do not seem particularly strong in these areas, since they don't allow the user to perform extensive analysis on the multidimensional data.

2.2 What Is Advance?

Advance is a new approach to analyzing business information, combining ideas from the approaches listed above. It is based on concepts rather than cells. Unlike spreadsheets, Advance doesn't convert business ideas into a grid of rows and columns. Instead, you can translate relations between variables into Advance simply by writing a formula in English, such as Profit

=

Income - Expense.

Defining and relating ideas in Advance translates a business problem into a model. For example, Advance can be used to build a model to prepare an annual budget, to analyze a company's financial situation, or to keep track of monthly sales.

Variables in Advance (called Data Sheets) can be single values, 'but can easily be tumed into multidimensional vectors by simply defining across which dimensions (called Lists) the variabie operates. For example, a model can track the variabie Income for each Month

and Region, while also tracking the variabie Sales for each Month, Region and Product. This way, a variabie or Data Sheet is only as multidimensional as the input data requires. Each Data Sheet can track data for any combination of Lists, so Advance models are extremely flexible and adaptable.

2.3 Principal Features of Advance

This section lists the principal features of Advance, the features that set Advance apart from other business modeling tools.

• English Formulas

8

Formulas in Advance refer to the names of the Data Sheets and Lists, rather than cryptic cell references. These names can consist of up to 60 characters, so they can fully describe the contents. This makes formulas much easier to understand.

(20)

Chapter 2 Software Overview

• Self-Documenting Models

Because Advance uses English names and formulas, you can figure out a model simply by looking at the definitions. Furthermore, Data Sheets in a model can be organized into different Folders, which helps when trying to understand the general structure of the model. For example, there could be a Folder containing the sales analysis, and a Folder containing the tax analysis.

• Separate Data and Formulas

Most spreadsheets require assigning a formula to all cells that have to be calculated.

In Advance, formulas and data are separate. Wh en calculating profit, you write a

single formula, which works correctly whether profit contains a single value or a value for each month and region. The underlying data structure can be modified without having to change the formula. For example, making the variabie Profit vary by month does not mean having to duplicate the existing formula for Profit for each of the months. Instead, the same formula for Profit holds for each of the months in the model.

• Multiple Views of the Same Data

Because Advance is based on concepts rather than cells, the actual layout of information in a model is very flexible. You can rearrange the data in many different ways, by simply dragging and dropping the Data Sheets and Lists in another location.

• Top Down Model Building

In spreadsheets, models have to be built from the bottom up. This means that all the

relevant, detailed information has to be incorporated into the model before you can start writing the formulas. Advance allows top down model building, starting with general concepts and slowly refining the details of a model. For example, you can start by defining the variables Income, Expense and Profit, before you work down to the assumptions that affect Income and Expense.

• Multidimensional Capabilities

As mentioned earlier, Advance can easily track data for multiple dimensions. Doing that in a multidimensional spreadsheet is possible, but not easy. A multidimensional spreadsheet will simply create an N-dimensional matrix of cells, which is not easy to understand, but even harder to use when building modeis.

Advance assigns the desired number of dimensions to each Data Sheet separately, instead of simply assigning dimension N to all Data Sheets.

(21)

• Automatic Consolidation

Advance makes consolidation effortless. For exarnple, it is very easy to consolidate department level data into a company total, or to combine monthly values into quarterly data.

• Database Capabilities

Advance contains many database functionalities, such as searching, sorting and selecting particular values, as weil as tabulating data. In addition, Advance also makes it pos si bie to group detailed information by summary level. For example, a model can sum employee salaries by department.

2.4 How Does Advance Work?

This section explains the way Advance works in greater detail. It introduces the reader to the basic concepts and parts that are used to build business models in Advance.

An Advance model can be built bottom-up as weil as top-down. You can simply start trans lating ideas about your model into variables and link them together using formulas. It's very easy to add additionallevels of detail afterwards.

An Advance model is created using a number of different building blocks: 1. Data Sheets

The Data Sheet is the most elementary part of an Advance model. Each Data Sheet contains one and only one kind of data. For example, a model of a business might contain a Data Sheet for sales, a Data Sheet for rent, a Data Sheet for salary, and so on. In other words, Data Sheets are the variables in a model. 2. Lists

A List is just a collection of people, places, or things that are similar in nature. Every List has a name that describes its contents. Each thing in a List is called an Item. Good exarnples are a List called Employees, containing the narnes of individual employees as Items, and a List called Months, containing Items "01198" through "12/98".

Data Sheets and Lists work together to create the structure of a model. To make a variabie in a model, e.g. sales, vary for different products, simply attach a List called Products to a Data Sheet called Sales. This tums the variabie Sales into a vector. To make these sales vary by month and product, also attach a List called Months to the Sales Data Sheet, changing Sales from a vector to a matrix.

(22)

The following screenshot from Advance shows a Report View containing three Data Sheets:

In this example, the Products and the Months Lists are attached to the Data Sheets Sales and Total Monthly Sales, while the Data Sheet Selling Price only has the Products List attached to it.

Note that the Products List attached to Selling Price is not a copy of the List attached to

Sales, but it is the same List. This means th at there's an automatically generated, unbreakable one-to-one link between the two Data Sheets involved. It also means that when you add Items to the Products List, they will automatically show up everywhere where the Products List is used.

3. Formulas

Formulas in Advanee are used to define the relationships between Data Sheets. In this example, the formula for Total Monthly Sales would look like this:

Total Monthly Sales = Sales

*

Selling Price

Notiee that the formula refers to kinds of information (Data Sheets or variables) rather than to specifie values or eells. This makes it mueh harder to "break" a formula than in most other data analysis tools. It also means that a newly added Item to the Products List in our example wil! automatically be included in the formula.

These concepts can be represented mathematically in the fol!owing way: Yij

=

Pi· qy, iE {1,2,3},j E {1, ... ,12}

• Yij = Total monthly sales for product i in monthj;

• Pi = Selling price of product i;

(23)

The indices i and j correspond with item references to items in the Lists used in this example (Products and Months, respectively). So basically, Data Sheets can be seen as n-dimensional arrays of data, while Lists contain the indexes to items in those arrays. Data Sheets, Lists, and Fonnulas combine to define the basic relationships between variables in a model. As a model gets bigger, the use of Views can help make the model structure obvious. Each View can store a number of Data Sheets.

4. Views

There are basically fOUf different kinds of Views. There are Folder Views, Report Views, Chart Views, and Import Map Views:

• A Folder View is a collection of related Data Sheets, just as a file folder is a collection of related papers.

• A Report View makes it easy to perfonn powerful database operations, such as sorting, filtering, and grouping, and provide the user with a number of fonnatting options for display and printing purposes.

• A Chart View displays infonnation in a graphical fonnat, while making it possible to use drag-and-drop to move data around.

• An Import Map View helps importing data from other sourees, such as spreadsheets and databases.

The same Data Sheet can be stored in more than one View. For example, the Data Sheet Gross Margin can show up in a Folder called Income Statement, but also in the Sales Data Report. The power of Advance is that putting a Data Sheet in more than one View does not mean creating the same data twice. It's actually the same data, displayed in multiple locations. That means that changing the Data Sheet in one View will result in an automatic update in the other Views as weil. This makes the data completely independent of its location.

A single Advance model can contain up to 8000 Views. The created Views are identified by a unique name and are organized by the View Manager.

(24)

Manage Views lt3

= Budgeting

I

·

· .

Stllff Cost Estimllting :lli Direct Expenses

' ... TotlIl Budgeted Cost by TlIsk = Budget Stlltus

Ijl Remllining Budget

lIL.

Remllining Budget Hours by Stllff Member

lIL.

Remllining Budget Chllrt

o

Actulli Hours lInd Fees

o

Activity BlIse Cost AlioclItion

o

PllInning lInd Billing Info

In the View Manager displayed in the screen shot above, named collections of Views

called Sections have been created. For exampIe, the Section Budget Status contains one

Report and two Charts. The Notes window allows the model developer to document all

the Views and Sections.

2.5 An Example of Simple Advance Model Mechanics

This section contains a detailed description of a simple example model built in Advance.

It illustrates the basic Advance concepts introduced in the previous section.

This model demonstrates how Advance can be used to generate samples from an

exponential distribution (referred to as lifetimes) using standard uniform samples, and

then demonstrates using these lifetimes to simulate a simple queuing system.

Because Advance does not currently contain a random number generator, the values in

the Data Sheet Input random number were generated in Excel and then imported into

(25)

The following screenshot shows the sample transformation:

Note that the Input random number Data Sheet only has the Numbers List attached to it, and the Parameter Data Sheet only has the Scenarios List attached it, but together they calculate Lifetime, which has both Lists attached to it. Advance automatically combines the dimensions. One formula holds for all combinations of attached dimensions:

Lifetime = -LN(Input random number)IParameter

Mathematically, the Advance formula above can be written as: Xij

=

-ln(u;)IÀj , where

14

Xij

=

ith sample from an exponential distribution with parameter Àj, i E

[l,10000],j E [l,4]

(26)

The following screen shot shows a report contammg some basic statIstIcs (the sample average, variance, and standard deviation), calculated from the resulting samples in the

Lifetime Data Sheet:

In the rest of the model, the generated samples from the exponential distribution are used as interarrival times to simulate an arrival process, where the arrival time aij of the {h

pers on or job (using arrival rate Àj) is given by:

aij

=

ai-I,j + Xij_

In Advance, we create a Data Sheet called Arrival times that calculates and stores the values of aij:

ELF(Numbers. 0) + Lifelime

Assuming there is only one server and that the service time is deterministic with service rate J-L equal to 3.5, we can now use these arrival times to generate the times sij that cu stomer or job i enters service (when using anival rate Àj):

(27)

In Advance, a Data Sheet called Service Times is created to calculate and store the values for each combination of items from the Lists Numbers and Scenarios:

Times, PREVSELF(Numbers) + IF(FIRST(NumbersJ, 0, 1 I Service Rate))

Finally, now that we know all the arrival times and services times, it's straightforward to calculate the time wij that person or job i spends waiting in the queue (when using arrival rate À):

The average of these waiting times is a common measure in queuing theory. In Advance:

A graph of these waiting times can be viewed by highlighting the Data Sheet name in Advance and hitting the QuickChart button:

(28)

The resulting waiting time chart for the first 100 people or jobs that arrive in the queue (with arrival rate Àj

=

3) is shown below:

ilo lambda = 3 :

i,. IWai!l!!9 Time in Queuel~

'2.000

,---

---~==:~

=

====:::~---___,

11

~

1

500

t - - - ---.--- - - - . . d I - I I I - - - j '11000 t - - -- ---liI----.lfH---flfHHIlIIHIIIIH---t---d---l :1 !10500 +--....

--~_HHIllI-flI

. . . :10000

L

-2.6

Product Development Plans and Timeframe

This section outlines the product development plans for Advance. At this point, there are basically three stages of product development that have been identified:

2.6.1 Stage 1

In the first stage, the following changes and new features are to be implemented: • Code porting from Pascal to C++ (32 bit)

The porting of the code from Pascal to C++, and with this the use of the MFC (Microsoft Foundation Classes) standard, offers a number of benefits. First of all it makes future development and design easier, since both C++ and MFC are well documented andsupported, and are considered to be the standard for Windows-based development. In addition, the porting means a transition from a 16-bit environment to a 3:è-bit, object-oriented environment, which means better code organization and memory management, and a faster and more powerful application.

• Model linking

Model linking is the ability to link several Advance models together. The implementation of this feature is critical; many clients and prospective clients have asked for this capability, since this feature makes it possible to split a big modeling project into several parts, allowing several people to work at the same model at the same time. It also reduces the recalculation time of large Advance modeIs. The implementation of this feature is expected to attract a number of new clients.

(29)

• Hierarchical Lists

Hierarchical Lists are lists th at make it possible to consolidate data at different levels of detail into one List. For instance, one could define the entire organizational structure of a company, from high-level departments to divisions or even single employees, using a single List. In the earlier versions of Advance, you'd have to create a separate List for each level of detail and then create the organizational structure by grouping the Lists, e.g. assigning each employee from one List to his or her department from another List.

• Layout/interface

There wiU be a change in the layout of Advance. To facilitate rapid model development and to increase ease of use, the new screen layout wil! include a tree structure that contains a branch for all part types in an Advance model, such as Data Sheets, Lists, and Views. Referring to or viewing a particular part can than simply be done by clicking on the appropriate tree item. Copying and moving of model parts will also be easier.

• Undo-function

The implementation of an Undo-function is obviously very important, especially since certain actions, such as deleting a List, can have major consequences for your model.

The planned beta-release date for Stage 1 is the fall of 1998.

2.6.2 Stage 2

Stage 2 of Advance' s development concentrates on expanding jts statistical and analytical capabilities. This does not only mean implementing new functions, but possibly also implementing a scripting function, that allows users to create their own function or sequence of functions. As mentioned before, the implementation of new functionalities does not necessarily mean writing thecode for these functionalities in-house, but may also include facilitating the use of extemal plug-ins. The projected end date for Stage 2 is the winter of 1998/1999.

2.6.3 Stage 3

The focus of Stage 3 is on developing a client-server version of the software. This would obviously be a major change in the product's market position and pricing.1t would allow Lighten to focus on bigger clients and significantly increase the revenue from software sales. No target date for this stage has been set yet.

(30)

Chapter 3 Task Identification

This chapter identifies the tasks and subtasks th at are implied by the internship goals outlined in section 1.2. These tasks can be split up into three main sections, as shown below. These sections will then be explored and developed in chapters 4, 5, and 6, respectively.

3.1 Understanding of Advance and its Possible Enhancements

To make a well-founded decision conceming the development of Advance, the first requirement is a good and thorough understanding of the product and its possibilities. Understanding Advance's data structure is important, since it will serve as the foundation for all added functionalities. It is also important to identify and itemize the possible functional additions, and for each of them, to determine how weIl they fit into the existing product. In other words, there are two main tasks in this section:

3.1.1 Gather Modeling Experience and Understanding ofthe Advance Paradigm Model building is definitely a good way to gain an understanding of Advance's paradigm and data handling capabilities. Building sophisticated business models for corporate clients not only gives you a good understanding of the software itself, but also provides insight into the marketplace, and therefore the current (and possible future) applications of the software.

3.1.2 Itemize Possible Added Functionalities

Another important step to make in the decision process is the identification of the mathematical and statistical techniques that are possible additions to Advance. Of course the number of existing techniques is very large, but some of them do not really have any useful applications in a business environment, and can therefore be ignored.

Chapter 4 itemizes the relevant functionalities mentioned above and, for each individual functionality, gives a practical example of its application. It also indicates how well each technique would fit into Advance, considering the way Advance organizes and handles data.

(31)

Analytic Techniques (or Business Modeling: Opportunities (or Advance

3.2 Researching Existing Client Base and Possible New Applications

The second set of tasks focuses on getting to know the current and pos si bIe future markets for Advance. This is an important part of the decision process, since entering certain markets requires specific mathematical capabilities. A good example would be linear programrning, a technique used in a large number of fields.

3.2.1 Research Existing Client Base

A good place to start when determining the need for new functionalities is the existing

client base. Many prospects and clients have indicated that they feel Advance lacks

certain analytical tools. Therefore it would be useful to interview selected prospects and clients, to get their thoughts on which functionalities would make Advance a more useful tooI for their specific business applications.

3.2.2 Research Possible New Applications

Another important part of researching the market is the exploration of new applications for Advance. As mentioned before, adding certain mathematical capabilities wiU open up new markets, and th us broaden Advance's client base. An obvious example of a new

application field is operations research, where mathematical modeling is essential. If

Advance incorporates the required mathematical capabilities, it would make a powerful modeling tooI that could outperform spreadsheets and offer more flexibility than canned

applications.

It is also important to get a good idea of the future uses of certain techniques, to make sure that Advance will not be a step behind the competition once the new analytical tools have been implemented. This means that understanding the market dynamics is very important. One way to get an idea ab out the future uses of certain techniques would be to talk to leading authorities in the respective fields. AIso, interviewing practicing business

analysts can provide valuable.information about market trends.

Chapter 5 explores the tasks in this section further, and contains the market research

results.

3.3 Implementing the New Functionalities

The third and final set of tasks focuses on the actual implementation of the new mathematical and statistical features in Advance. A decision has to be made whether to

implement the new functionalities in-house, or to use extemal plug-ins or code. It's

obvious that in-house implementation offers more versatility and better customization possibilities, but it wiU also require more effort and time. You risk doing coding work

(32)

Chapter 3 Task Identification

again that others have already done before you, which is not really efficient. Another issue is the complexity of certain techniques. Some mathematical techniques are so sophisticated that they're not easy to implement unless you have a thorough understanding of the way they work.

3.3.1 Analytical/Statistical Plug-ins and Their Pricing

A first step in the decision process mentioned above is to look around for available analytical and statistical plug-ins and their pricing. Basically there are two categories, complete plug-ins (with their own graphical interface and reporting capabilities, etc.) and code-only plug-ins, each with their own advantages and disadvantages. Interfacing with complete plug-ins would not necessarily imply a large change in the Advance code; adding a simple linking module would suffice. This offers the additional advantage that customers can buy a scalabIe solution, either with or without the plug-in.

3.3.2 AdvantageslDisadvantages ofBoth Methods

There are many issues that have to be considered when deciding between using extemal plug-ins and writing the code in-house. Most of these issues, such as price, time, and fIexibility, are pretty obvious, but it is still useful to make a list of all the advantages and disadvantages of both methods.

Chapter 6 itemizes the relevant available plug-ins and also compares the functionalities they offer. It also contains the comparison between in-house development and extemal plug-ins.

3.4 Conclusions and Recommendations

After all the tasks outlined in this chapter have been performed, this report will be completed by a number of concJusions and recommendations regarding the development and implementation of new functionalities in the Advance software. Chapter 7 contains these concJusions and recommendations, taking all the relevant factors and results into account.

(33)

(34)

Chapter 4 Pos si bie

Functional Additions

This chapter identifies the relevant mathematical and statistical techniques that would make a useful addition to Advance. It also gives a practical business application for most of these techniques, and indicates if the specific technique will be easy or hard to implement given the current Advance data structure. The relative importance of these techniques will be determined in Chapter 5.

4.1 Simple Mathematical Functions

The current release of Advance does not contain a number of simple mathematical and statistical functions that are considered to be standard techniques. Functions that come to mind are:

• Statistical indicators, such as the median and mode of a data set, the covariance and correlation coefficient of two data sets, and the rank of a number in a data set; • Combinatorial functions, such as the factorial of an integer number;

• Matrix calculations, such as matrix inversion and calculation of the determinant. The implementation of all these techniques is very straightforward, since none of them use complex algorithms for calculation. The only technique th at might need some more attention would be matrix inversion, since (near-) singularity might occur, and one needs to determine how to deal with that.

Rice (1981) shows that computing an inverse matrix requires about three times as much computation and perhaps twice as much storage as solving a linear system of equations. He also states that calculating the matrix determinant "serves no purpose in linear algebra computation". Additionally, he says that matrix inversion is, in most cases, a "computational waste", since there are "cheaper"'ways to do analysis involving matrix inversion. For instance, solving the vector x from Ax = b can be done more efficiently by computing the LU factorization of A. '

4.2 Linear Regression

Of ten, we try to predict the value of one variabie (called the dependent variabie) from the values of one or more other variables (caJled independent variables). If the dependent variabie and the independent variables are related in a linear fashion, linear regression can be used to estimate this relationship. For example, if we are trying to predict the

(35)

monthly sales for a national fast food chicken chain, we might consider using the following independent variables: national income, price of chicken, dollars spent on advertising during the current month, and dollars spent on advertising during the previous month.

4.2.1 Regression Basics

This subsection discusses the basics of multivariate regression, taken from BowermanJO'Connell (1993), Winston (1991), KaplanJAtkinson (1989), and Gujarati (1988).

Suppose we are using k independent variables to predict the dependent variabIe y and we have n data points of the fonn (Yi, Xli, X2i, ... , Xki), where Xji

=

value of the

/h

independent variabIe for the ith data point and Yi

=

value of the dependent variabIe for the ith

data point. In multiple regression, we model the relationship between Y and the k independent variables by

where Ci is an error term with mean zero, representing the fact that the actual value of Yi

may not equal 130 + 131 Xli +

/32

X2i + ... + 13k Xki. f3i may be thought of as the increase in Y if the value of the ith _{independent variabIe is increased by 1}_and_{all other independent} variables are held constant. Thus, f3i is analogous to aylaxi, where Xi is the ith independent variabIe.

In addition to the assumption that the mean of all possible values of the error tenn Ci

equals zero, the validity of the fonnulas for the confidence intervals and statistical hypothesis tests to be presented later in this section depends on three assumptions that we refer to as the inference assumptions. These assumptions can be stated as follows:

Assumption 1

Assumption 2 Assumption 3

24

The error tenn c has a variance

(i that does not depend on the

value of the independent variables Xl, X2, ... , Xl. This assumption is called homoscedasticity. If the variance of the error tenn depends on Xl, X2, ... , Xl, then we say that heteroscedasticity is present.

Errors are nonnally distributed.

The errors should be statistically independent. In other words, any

one value of the dependent variabIe y is statistically independent of any other value of y.

(36)

Chapter4 Possible Functional Additions

4.2.2 Point Estimation Using Least Squares

Suppose we estimate {3i (i=O, 1, 2, ... , k) by bi. Then our prediction or estimate for Yi is given by

If we choose ba, bj, ... , bk to minimize the sum of the squared residuals, SSE

=

L el, where ei

=

Yi -

Y

i,

then we've obtained the least squares estimates of {3o, {3j,

f3z,

.

.. ,

13k. It can be shown that the least squares estimates of the parameters in the linear regression model can be caIculated by a formula that is expressed by using matrix algebra.

In matrix notation, the linear regression model looks like this: Y

=

X{3 + ê, with

Y = (yj, Y2, ... , Yn)', a column vector containing the values of the dependent variabie.

13 =

(13

0,

f3j, ... ,

13k)' ,

a column vector containing the model parameters.

X = (X_o, Xj, ... , Xk), a nx(k+l) matrix containing the values of the independent

variables. (Here Xj = (xjl, Xj2, ..• , Xjn)' is a column vector containing the values of the

/h

independent variabie, and Xo is a column vector of l' s.)

ê

=

(Ej, E2, ... , En)', a column vector containing the values of the error term.

The least squares estimates b

=

(ba, bj, ... , bk)' can now be caIculated using the following formula:

b

=

(X'Xr\X'y).

If the inference assumptions are satisfied, we can obtain a point estimate of

ei

by caIculating the mean square error

i

= SSE / (n-k-l).

Furthermore, a point estimate of (J is the standard error

f,!E

SE

s

-n-k -1

We expect approximately 68% of the Y values to be within s of

Y

and approximately 95% of the y-values to be within 2s of

y.

(37)

4.2.3 Goodness of Fit

To determine how weil the least squares estimate fits the data points, we first need to discuss three components of variation:

• The sum of squares total or SST measures the total variation of Yi about its mean

y,

so in other words SST

=

L:(yi - y)2

=

L:y? _ n y 2.

• The sum of squares error or SSE is given by SSE = L: (Yi -yi = L: el. The SSE ca1culates the unexplained variation in the dependent variabie.

• The sum of squares regression or SSR ca1culates the explained variation in the dependent variabie, so SSR

=

L: (Yi - y

i

=

L:y? -n y 2.

It ean be shown that SST

=

SSR + SSE. Por a good fit, SSE wil! be smal!, so SSR wil! be large. This is true sinee SST is only a funetion of the values of y.

We can define the multiple coefficient of determination (R2) by R2

=

SSR / SST.

Thus R2 provides an overal! index of how weIl y ean be explained by al! the regressors, i.e. how weIl a multiple regression fits the data. AIso, as we add additional regressors to the model, we ean see how helpful they are in explaining the variation in y, by noting how much they increase R2•

Since adding an independent variabie to the model will never decrease, and usually inerease the R2, the R2 should not be the only criterion when determining which regression model is best suited to the data. Many regression packages produce a variant of the R2 statistic, called the adjusted R2, whieh ean decIine when additional regressors, without much explanatory power, are added to the model:

adjusted R2

=

(1 - (n-I)/(n-k-I)) (1 - R2), where

kis the number of independent variables (x], X2, .•. , Xk), and n is the number of samples.

4.2.4 Hypothesis Testing and Confidence Intervals {or the Least Squares Estimates

If we have incIuded independent variables Xl, X2, .•. , Xk in a multiple regression, we of ten

wish to test

against

26

Ho: ~=O (Xj does not have a significant effect on y when the other independent variables are incIuded in the regression equation)

(38)

(Xj does have a significant effect on y when the other independent variables are inc1uded in the regression equation)

To test these hypotheses, we compute the test statistic b_J

t = -Sbj

Here Sb

j denotes the standard error of bj, the least squares estimate for f3J. Sbj is a point estimate for the standard deviation crb. of bj, ca1culated as follows:

J

Since

Sb

j

has a t-distribution with n - k - 1 degrees of freedom if the inference assumptions hold,

we can use the test statistic to test the hypotheses in the following way:

Sb

j (and often the t-statistic itself) is read from computer output. At a level of significance a (0.05 and 0.10 are comrnon), we reject Ho if

I

t i > t(a12,n-k-I). Usually, variables inc1uded in aregression equation should have significant t-statistics. That is, Ho

should be rejected in favor of Hl for these variables. If an independent variabIe has an insignificant t-statistic, we usually remove the independent variabIe from the regression equation and obtain new least squares estimates for the remaining variables.

The t-statistic can also be used to ca1culate a confidence interval for {3j. If the inference

assumptions hold, a 100(l-a) % confidence interval for f3J is

4.2.5 Multicollinearity

If aregression equation contains two or more independent variables that exhibit astrong linear relationship, we say that multicollinearity is present. This may make the least

squares estimates of the {3/s unreliable. For instance, if Xl denotes the total population of

a country in a certain year, X2 denotes the total female population and XJ denotes the total male population, it is quite obvious that Xl

=

X2

+

XJ. Therefore we can not use all three of these variables in a single regression equation without causing multicollinearity. One of the three independent variables should be dropped from the equation.

(39)

4.2.6 Residual Analysis to Check ln{erence Assumptions

The most common method of assessing whether or not the regression assumptions are

valid is the use of residual analysis and residual plots. Recall that the ith residual, ei, is

defined as

ei=Yi-A

To construct a residual plot, we calculate the residuals el, e2, ... , en> and plot them against some criterion.

1. Using residual plots to check for heteroscedasticity

If the variance of the error term depends on Xl, X2, ... , Xn , we say that heteroscedasticity is present. To see whether the homoscedasticity assumption is satisfied, we should plot the residuals against the following criteria:

• Values of the independent variable Xj, j E {I, ... ,k}.

• Values ofy, the predicted value ofthe dependent variabIe. • The time order in which the historical data have been observed.

If the residuals plotted do not increase (or decrease) with an increase of the criterion, this is an indication of equal variance and of homoscedasticity.

A plot showing heteroscedasticity would look something like this:

iii :J 'ti • 'iii CII a::

•

• Heteroscedasticity

•

• _•

• •

_•

•

• _•

•

Criterion

•

In this residual plot we see an increasing error variance, which means that the constant variance assumption (first inference assumption) of the regression model would be violated in this case.

(40)

2. Using residual plots to check for normality of the errors

If the inference assumptions hold, then each error term Ei has been randomly selected

from anormal distribution with mean zero and variance

cl.

Thus, if the normality

assumption holds, a histogram of the residuals should look reasonably bell-shaped and reasonably symmetrie about zero.

3. Using residual plots to check for autocorrelation

The individual errors should be statistically independent for the inference assumptions to

hold. This assumption is of ten violated when data is collected over time. For such data,

the time-ordered error terms can be autocorrelated. Intuitively, we say that error terms

oceurring over time have positive autocorrelation if a positive error in time period i tends

to produee, or be followed by, another positive error term in time period i + k (a later time

period), while a negative error term tends to be followed by another negative error term.

The sign pattem for positive autocorrelation would typically look like this:

+++++----++++----+++----+

The following residual plot shows positive autocorrelation with the above pattem:

Positive Autocorrelation

•

• _•

•

• _•

_•

Timet jij ::I 'C .~ r---~.---~~----~.--- ---Q) a:

•

Similarly, negative autocorrelation tends to produee a change in sign between

consecutive error terms. The sequence of errors in this case would be similar to: + - + -+ - + - + -+ - + -+

These ideas allow us to give a relatively simple interpretation of the independence

(41)

no positive or negative autocorrelation. In other words, the error terms occur in a random pattem over time, implying that these error terms are statistically independent.

An example of a random looking pattem: +--+-++-+-+++--+--++

4.2.7 General Remarks

Multivariate regression is definitely one of the most requested techniques. It has useful applications in practically every type of business, and would therefore make an extremely valuable addition to Advance. It would reinforce Advance's status as a very general and well-rounded business modeling tooI. Additionally, the fact that Advance is a multidimensional environment for working with data facilitates the implementation of multivariate regression.

The question that remains is what types of multivariate regression should be implemented. The vast majority of business modelers do not use the more advanced variants of regression, such as GLS (Generalized Least Squares). In addition, it rnight not be very wise for Advance to aim for these high-end users in the first pI ace, since it will be hard to come close to the level of expertise available in established statistical packages, such as SAS or SPSS. Therefore it seems to make sense to only implement straightforward multivariate linear regression (OLS or Ordinary Least Squares), if this means a significant reduction in effort or cost from implementing various regression methods.

Multivariate regression is very useful for quantifying and gaining an understanding of the influence that certain variables have on another variabie. For instance, multivariate regression could be used to express the demand for a product as a function of its price, the average competitor's price, and the company's advertising expenditure to promote the product. The resulting quantitative relationship could then be used to predict the demand for the product in future periods.

4.3 Time Series Analysis

Time series analysis is undoubtedly one of the most used methods for forecasting in a business modeling environment. It is very useful for identifying trends and seasonal fluctuations in historical data sets. Time series analysis is a very broad term; it can be split up into a number of categories, such as time series regression (which falls under multivariate regression), exponential smoothing, and Box-Jenkins techniques (ARIMA-modeis).

The largest difference between multivariate regression and other time series models is that other time series models use previous values of the variabie that has to be forecast