CURRENT PRESS RELEASES ARCHIVE
View our Latest Press Releases. Subscribe to the TMAP email
list if you would like to be notified of new postings on the site.
2008-09-28 BACK
Make Spreadsheets work for you
Make Spreadsheets Work for You This article originally appeared in the Texarkana Gazette on September 28, 2008 By George Boger, Ph.D., Assistant Professor of Management at Texas A&M University-Texarkana Today Microsoft's Excel spreadsheet program is arguably the most widely used business analysis tool. The success of Excel lies in the visual grid layout, a formula component and the ability of the computer to quickly perform calculations. Tables of data can be entered into cells of the grid along with row and column labels. Formulas allow users to write entries into cells to perform calculations involving the data. For example, if a column lists several cost figures, a total cost can be calculated by summing the individual cost figures using a summing formula. If you change a cost figure, the formula will automatically calculate a new total cost value. The breakthrough of spreadsheets is that you don't need a computer programmer to write programs to read your data and sequence through calculations to generate reports. You can analyze your business data using formulas and some additional capabilities built into Excel. These capabilities include Excel functions, which are small programs that perform calculations that would otherwise be impossible with formulas, and add-ins. Add-ins – also called macros – are computer programs written usually by programmers that can greatly enhance Excel's calculation abilities. Add-ins are often written and sold over the Web by companies which operate independently of Microsoft. Some add-ins are sophisticated enough to rival the capabilities of programs written many years ago for large mainframe computers. For example, the Solver add-in that is shipped with every copy of Excel can be used to analyze optimization problems on your personal computer. Optimization problems are ones in which limited resources such as raw materials or labor must be allocated in the most efficient way, usually to maximize profit or minimize cost. Examples include finding a product mix that maximizes profit, making forecasts of future sales with minimal error, determining a workable staff schedule, or developing a portfolio of investments. References to optimization have appeared in the business press such as Harvard Business Review's "Competing on Analytics" (Thomas Davenport, January 2006) and Business Week's cover story "Math Will Rock Your World" (Stephen Baker, January 23, 2006). Stephen Baker expands on his article in a new book "The Numerati" (Houghton Mifflin, 2008). While these articles extol the efficiencies of optimization, they don't provide textbook style examples of optimization problems. Luckily there is a file containing example optimization problems that is shipped with every copy of Excel. The file SOLVSAMP.XLS is included with Excel to demonstrate how Solver can be used to analyze various optimization problems. You can find this example file by searching your hard drive using the Explorer search capability. It is usually stored in the SAMPLES folder within subfolders in the Programs File folder. To work with SOLVSAMP.XLS you will need the Solver add-in. To learn how to load Solver into Excel, search the Excel help files using "Solver" as the keyword. It is loaded in the way that add-in programs are usually loaded into Excel by using menu selections. In Excel 2007, Solver will appear in the Analysis group of the Data ribbon. In earlier versions of Excel, Solver appears in the Tools menu. After opening the SOLVSAMP.XLS, activate the Product Mix spreadsheet. Enter values into the Changing Cells for the number of TV sets, stereos and speakers to produce and observe the total profit in the Target Cell. These cells are identified in the spreadsheet by bolded color borders. Did you use more parts than available from inventory to make these three products (compare the Constraint Cells)? Run Solver – the parameter boxes are already completed, and find out how many products to produce in order to maximize profit without exceeding the number of parts in inventory. Explore the other six spreadsheets and Solver parameter boxes. An explanation about the problem presented in a spreadsheet is given. Except for the Quick Tour spreadsheet, the Solver parameter boxes are completed and an optimal solution can be found by running Solver. Sometimes it's easier to understand what optimization problems are by exploring spreadsheets than it is to read articles about them. The company Frontline Systems developed Solver and has a distribution arrangement with Microsoft to ship a limited capability Solver add-in with every copy of Excel. More powerful versions can be purchased at http://solver.com. Additional free example files are also available from the Solver Web site. Solver isn't the only spreadsheet add-in available to solve optimization problems. WhatsBest! from LINDO Systems is a popular add-in that performs similar tasks. Both Solver and WhatsBest! are available for the Lotus 1-2-3 spreadsheet program. Dr. George Boger is an assistant professor of management in the College of Business at Texas A&M University-Texarkana where he teaches a course in management science using spreadsheets. He can be e-mailed at george.boger@tamut.edu.
TOP
Make Spreadsheets work for you
Make Spreadsheets Work for You This article originally appeared in the Texarkana Gazette on September 28, 2008 By George Boger, Ph.D., Assistant Professor of Management at Texas A&M University-Texarkana Today Microsoft's Excel spreadsheet program is arguably the most widely used business analysis tool. The success of Excel lies in the visual grid layout, a formula component and the ability of the computer to quickly perform calculations. Tables of data can be entered into cells of the grid along with row and column labels. Formulas allow users to write entries into cells to perform calculations involving the data. For example, if a column lists several cost figures, a total cost can be calculated by summing the individual cost figures using a summing formula. If you change a cost figure, the formula will automatically calculate a new total cost value. The breakthrough of spreadsheets is that you don't need a computer programmer to write programs to read your data and sequence through calculations to generate reports. You can analyze your business data using formulas and some additional capabilities built into Excel. These capabilities include Excel functions, which are small programs that perform calculations that would otherwise be impossible with formulas, and add-ins. Add-ins – also called macros – are computer programs written usually by programmers that can greatly enhance Excel's calculation abilities. Add-ins are often written and sold over the Web by companies which operate independently of Microsoft. Some add-ins are sophisticated enough to rival the capabilities of programs written many years ago for large mainframe computers. For example, the Solver add-in that is shipped with every copy of Excel can be used to analyze optimization problems on your personal computer. Optimization problems are ones in which limited resources such as raw materials or labor must be allocated in the most efficient way, usually to maximize profit or minimize cost. Examples include finding a product mix that maximizes profit, making forecasts of future sales with minimal error, determining a workable staff schedule, or developing a portfolio of investments. References to optimization have appeared in the business press such as Harvard Business Review's "Competing on Analytics" (Thomas Davenport, January 2006) and Business Week's cover story "Math Will Rock Your World" (Stephen Baker, January 23, 2006). Stephen Baker expands on his article in a new book "The Numerati" (Houghton Mifflin, 2008). While these articles extol the efficiencies of optimization, they don't provide textbook style examples of optimization problems. Luckily there is a file containing example optimization problems that is shipped with every copy of Excel. The file SOLVSAMP.XLS is included with Excel to demonstrate how Solver can be used to analyze various optimization problems. You can find this example file by searching your hard drive using the Explorer search capability. It is usually stored in the SAMPLES folder within subfolders in the Programs File folder. To work with SOLVSAMP.XLS you will need the Solver add-in. To learn how to load Solver into Excel, search the Excel help files using "Solver" as the keyword. It is loaded in the way that add-in programs are usually loaded into Excel by using menu selections. In Excel 2007, Solver will appear in the Analysis group of the Data ribbon. In earlier versions of Excel, Solver appears in the Tools menu. After opening the SOLVSAMP.XLS, activate the Product Mix spreadsheet. Enter values into the Changing Cells for the number of TV sets, stereos and speakers to produce and observe the total profit in the Target Cell. These cells are identified in the spreadsheet by bolded color borders. Did you use more parts than available from inventory to make these three products (compare the Constraint Cells)? Run Solver – the parameter boxes are already completed, and find out how many products to produce in order to maximize profit without exceeding the number of parts in inventory. Explore the other six spreadsheets and Solver parameter boxes. An explanation about the problem presented in a spreadsheet is given. Except for the Quick Tour spreadsheet, the Solver parameter boxes are completed and an optimal solution can be found by running Solver. Sometimes it's easier to understand what optimization problems are by exploring spreadsheets than it is to read articles about them. The company Frontline Systems developed Solver and has a distribution arrangement with Microsoft to ship a limited capability Solver add-in with every copy of Excel. More powerful versions can be purchased at http://solver.com. Additional free example files are also available from the Solver Web site. Solver isn't the only spreadsheet add-in available to solve optimization problems. WhatsBest! from LINDO Systems is a popular add-in that performs similar tasks. Both Solver and WhatsBest! are available for the Lotus 1-2-3 spreadsheet program. Dr. George Boger is an assistant professor of management in the College of Business at Texas A&M University-Texarkana where he teaches a course in management science using spreadsheets. He can be e-mailed at george.boger@tamut.edu.
TOP