...Modeling and Solving LP Problems in a Spreadsheet Chapter 3 C.T. Ragsdale. 2008. Spreadsheet Modeling & Decision Analysis, 5th E. Revised, Thompson 1 Section 1 EXCEL SOLVER 2 Introduction • Solving LP problems graphically is only possible when there are two decision variables • Few real-world LP have only two decision variables • Fortunately, we can now use spreadsheets to solve LP problems 3 LP Solvers • Conventional – MPS (IBM) – LINDO, GINO – GAMS – AMPL • Algebraic Language • Spreadsheet Modeling • The company that makes the Solver in Excel, Lotus 12-3, and Quattro Pro is Frontline Systems, Inc. Check out their web site: http://www.solver.com – Frontline Solver, Premium Solver, Risk Solver – What’s Best? 4 Steps in Implementing an LP Model in a Spreadsheet 1. Organize the data for the model on the spreadsheet. 2. Reserve separate cells in the spreadsheet for each decision variable in the model. 3. Create a formula in a cell in the spreadsheet that corresponds to the objective function. 4. For each constraint, create a formula in a separate cell in the spreadsheet that corresponds to the left-hand side (LHS) of the constraint. 5 The Simple Farm Model Again! max π = x1 + 1.5x2 s/t x1 + 2x2 ≤ 160 3x1 + 2x2 ≤ 240 x1 ≥ 0, x2 ≥ 0 6 Implementing the Model See file FarmEx original.xls 7 Cell Labels 8 How Solver Views the Model • Target cell - the cell in the spreadsheet that represents the objective function...
Words: 2610 - Pages: 11
...Baker, 2004. The Art of Modeling with Spreadsheets. New York: Wiley & Sons. Copyright © 2004 by John Wiley & Sons, Inc CHAPTER 1 INTRODUCTION MODELS AND MODELING Modeling is the process of creating a simplified representation of reality and working with this representation in order to understand or control some aspect of the world. While this book is devoted to mathematical models, modeling itself is a ubiquitous human activity. In fact, it seems to be one of just a few fundamental ways in which humans understand the world. As an example, a map is one of the most common models we encounter. Maps are models because they simplify reality by leaving out most geographic details in order to highlight the important features we need. A state road map, for example, shows major roads but not minor ones, gives rough locations of cities but not individual addresses, and so on. The map we choose must be appropriate for the need we have: a long trip home across several states requires a regional map, while a trip across town to find a new doctor’s office requires a detailed street map. In the same way, a good model must be appropriate for the specific uses intended for it. A complex model of the economy is probably not appropriate for pricing an individual product. Likewise, a back-of-the-envelope calculation may be inappropriate for acquiring a multibillion-dollar company. Models take many different forms: mental, visual, physical, mathematical, and spreadsheet, to name a few. We...
Words: 4929 - Pages: 20
...Interpretation of Modeling and Simulation Can there be a better fit for a company out there that uses Microsoft Excel to conduct modeling to simulate their business growth other than Microsoft itself? Microsoft has made billions of dollars selling their Microsoft Office Suites and their home computer operating systems around the world. They have become one of the largest and well known companies in the world. The way that I see it, is that Microsoft’s business model, in a nutshell, is to provide goods and services in the form of software and support to paying customers. One question that may be asked in this day an age is with the advances in cloud computing and Google’s “free” gadgets, will the traditional Microsoft’s business model come to an end? What will Microsoft’s business analysts do in order to change their business model? One can only imagine that it will need to be modeled and simulated first to avoid potentially expensive mistakes. I must start off by saying that before I started my Systems Modeling Theory online class through Strayer, I thought that it may just be one of the hardest classes to understand that I’ve ever taken throughout the course of my college career. I can say now after having completed the last nine weeks of class that I was correct. What’s hard, is not understanding the concepts of why businesses would use Excel models for their business growth but rather the actual use of Excel itself. With all of the bells and whistles...
Words: 1781 - Pages: 8
...and therefore, there is no savings. I don’t make enough money to hire an accountant, so I have decided to use the spreadsheets model so I can make better decisions about the problem. Spreadsheet models are also mathematical models, and its mathematics is more accessible than algebra and calculus. The spreadsheet is the second best way to do many kinds of analysis and is therefore the best way to do most of modeling. Spreadsheet based decision modeling is widely used in business today. The functionality provided within the current integrated packages allows users to develop very complex models. The purpose that I need the spreadsheet model is not going to be a complex model because it is for personal use. Budget spreadsheet is a basic practical tool to manage budget and thereby investments. I will use this to manage my money for any required purpose. Whether it is personal budget, family budget, household budget or home budget one can learn and manage to maintain budget using a budget spreadsheet. A budget spreadsheet will make it easy to manage income and expenses. It will also help me reduce the cost and gain benefits from my investments. Spreadsheet model will also allow me to avoid future debts or losses and it is better to do some math instead of loosing valuable money. There are a wide variety of budget spreadsheets available; I will choose a budget spreadsheet that considers many aspects so I am able to...
Words: 784 - Pages: 4
...Use of Spreadsheets in Business and Technology Marty Montgomery August 25, 2013 Many corporations depend on spreadsheets as a fundamental (Institute of Corporate Auditors, 2006) device in their financial reporting and operational processes. As a result, the use of spreadsheets is an essential part of the information and decision-making structure for these organizations. In developing and using spreadsheets, organizations need to balance their ease and flexibility against the value of reliable information for management’s application. As users of spreadsheet applications such as Microsoft Excel® or Lotus 1-2-3® have become more sophisticated, so have spreadsheets. Once used to support simple functions such as logging, tracking and totaling information, spreadsheets with enhanced formulas and built-in advanced features are now used to support such business functions as complex valuation models. The use of macros and multiple spreadsheets which are linked together allows users to build very complicated—and sometimes convoluted—models and other business functions with minimal or no documentation. In addition, these complex spreadsheets are not usually supported by the same control environment as formally-developed, purchased applications. For example, the developers and users of spreadsheets are usually not trained in structured programming, testing, version control or systems development life cycles, and spreadsheets are rarely restricted from unauthorized access by security...
Words: 949 - Pages: 4
...Pachamanova, David P. Kopcso Mathematics and Science Division, Babson College, Wellesley, Massachusetts 02457 {dpachamanova@babson.edu, kopcso@babson.edu} T his article illustrates how simulation can be used in the classroom for modeling customer behavior in the context of customer lifetime value estimation. Operations research instructors could use this exercise to introduce multiperiod spreadsheet simulation models in a business setting that is of great importance in practice, and the simulation approach to teaching this subject could be of interest also to marketing and accounting instructors. At Babson College, the spreadsheet simulation exercise is part of an integrated one-case teaching day of the marketing, accounting, and operations research disciplines in the full-time MBA program, but the exercise is directly transferable to stand-alone courses as well. In our experience, students have felt empowered by the ability to incorporate their ideas about customer behavior directly into customer lifetime value models, and have appreciated the ease with which simulation enables them to obtain intuition about the sensitivity of their estimates to different assumptions. Key words : customer lifetime value models; spreadsheet simulation models; cross-disciplinary integration;...
Words: 7074 - Pages: 29
...Required Textbook: 1. Financial Modeling by Simon Benninga, MIT Press, Third edition. Other Recommended Books: 1. Corporate Finance, Stephen A. Ross, Randolph W. Westerfield, and Jeffrey Jaffe (RWJ), Irwin/McGraw-Hill. (you should have this from the core class). 2. Damodaran on Valuation, Wiley. 3. Spreadsheet Modeling in Corporate Finance by Craig Holden, Prentice Hall. 4. Analysis for Financial Management, Robert C. Higgings, Irwin/McGraw-Hill. 5. MBA’s Guide to Microsoft Excel 2000, Stephen L. Nelson, Redmond Technology Press. Course Packet: The course packet contains the cases that will be assigned during the semester. Assignments and lecture notes will be distributed in class. Exams: There will be one take-home final exam. The final exam will be based on spreadsheets built using techniques for financial modeling developed throughout the course. The exam will require individual ability to perform advanced financial modeling using a spreadsheet. Class Participation: A vibrant class discussion will raise the level of enjoyment and learning for all of us. Therefore, it will help if you take the time to prepare and do the readings before the class. You should read the assigned chapters before coming to the lectures. In addition to discussing the material, you should also be familiar with the modeling techniques and spreadsheet tools used in each class. Grading Policy: 1. Late assignments are never accepted for credit unless there are extenuating circumstances...
Words: 460 - Pages: 2
...is not the best tool to use for complex designs and loads as ours was. It worked great for the simple beam deflection in the last project. Many times hand calculations are the long way of doing things. In this case, it took much longer to figure out the correct way of adding fixtures, load, and connections in SolidWorks. Many of the material properties in SolidWorks are also not the same as they are in our book. Simply said, choosing which method to use is the hardest decision to make. Sometimes FEA analysis is better, and in some cases, hand calculations are the easiest. Background: We have been given the task to design a shaft to specific guidelines using inch size pillow blocks. We will perform appropriate calculations using modeling techniques and verify the design in SolidWorks FEA program. The problem is 7-5 in the book and is shown below: The problem statement: A geared industrial roll shown in the figure is driven at 300 rev/ min by force F acting on a 3-in-diamter pitch circle as shown. The roll exerts a normal force of 30 lbf/in of roll length on the material being pulled through. The material passes under the roll. Design a shaft for the situation of the industrial roll with a design factor of 2 and a reliability goal of 0.999 against fatigue failure. Pick a shaft size that does not deflect greater than 2”. We have been given this problem to see how we will approach a design problem like this and I hope I have approached it correctly. I plan to figure...
Words: 856 - Pages: 4
...Spreadsheet Modeling Assignment 1. Objectives The purpose of this assignment is to use some of the more advanced tools that Excel provides to develop a model of a complex business problem. In particular, you will use the following Excel features: 1. Importing data from text files 2. Sorting data 3. Database functions 4. Pivot Tables 5. Data Tables 6. Goal Seeking 2. What You Will Need 1. Access to a computer with a printer and Excel 2007. 2. Note that the instructions in this handout are written for Excel 2007. There are significant differences in user interface between Excel 2007 and 2003. 3. Download the following three files: Mymodel.xls, labor.txt, and demand.txt. 3. What You Will Hand In 1. a CD containing the model your group develops - on the outer label, write the course number, group number, the name of each group member 2. two full-page charts (with your course number and group number as a footer) 3. four worksheet printouts (with your course number and group number as a footer) 4. Make sure your CD is free of any computer virus. A contaminated CD will result in a 50% penalty in grade! 4. Due Date Check the course syllabus for assignment due date. A penalty will be assessed against assignments handed in late (including during class). 5. Background You work for the NOVA Corporation, a mid-tier supplier of hoses and hose assemblies...
Words: 4251 - Pages: 18
...Calculating NPV 57–62 Calculating NPV for production capacity decision 58–62 Portfolio management 173–178, 345–346, 387–394, 442–444, 689–691 Pension fund management 178–182 Financial planning 210–214, 676–681, 734–735 Arbitrage opportunities in oil pricing 215–219 Currency trading 220 Capital budgeting 290–295 Estimating stock betas 396–401 Hedging risk with put options 407–408 Stock hedging 407–408 Asset management 409–410 New product development 503–504, 574, 673–676, 715–722 Bidding for a government contract 513–518, 523–533, 653–657 Investing with risk aversion 557–560 Land purchasing decision 575 Risk analysis 582–583 Liquidity risk management 651–653 Estimating warranty costs 657–661 Retirement planning 681–685 Modeling stock prices 685–686 Pricing options 686–689, 691–693 Investing for college 732 Bond investment 733 HUMAN RESOURCES AND HEALTH CARE Fighting HIV/AIDS 23–24 DEA in the hospital industry 184–189 Salesforce allocation problems 454–456 Assigning MBA students to teams 462 Selecting a job 484–492 Selecting a health care plan 519–521 Drug testing for athletes 535–538, 539–542 MARKETING Determining an advertising schedule 133–141, 373–376, 465–471, 480–483 Estimating an advertising response function 369–373 Retail pricing 422–427 Estimating a sales response function 437–441 Cluster analysis of large cities 445–449 Classifying subscribers of the WSJ 450–453 New...
Words: 68271 - Pages: 274
...EDWIN KATO 15406 La Loma, Houston, Tx HOUSTON, TX 77083 edio2004@yahoo.com 832-774-9218 QUALIFICATIONS SUMMARY Financial Analyst with more than 2 years of increasingly responsible employment with a large corporation. Diversified, progressive experience in the analysis of financial / economic data supporting enterprise decision making including marketing and investments; financial planning, forecasting, and modeling; company valuation; credit underwriting; and portfolio management. PROFESSIONAL EXPERIENCE Financial Analyst, OppenheimerFunds, Denver, CO 2014 - 2015 Successfully secured $100 Million in additional funding by developing a profitability / financial viability model of company’s 350 stores Evaluated and recommended an in-house delivery system in OppenheimerFunds, CO, which resulted in a $100,000 annual savings by eliminating need for outside carrier Created an optimal staffing model for stores resulting in annual payroll work hours savings of 20% Streamlined the corporate planning process, creating a centralized planning platform that reduced budget preparation time by two weeks despite the budget doubling in size over a two-year period Conducted cost benefit analysis to outsource the store’s in-house packaging facility resulting in a $1.5 million savings Produce annual budgets exceeding $500 million and forecast models on a departmental, business segment and consolidated basis working with functional department heads and senior management Analyze and interpret...
Words: 428 - Pages: 2
...ENGINEERING FOR EXCEL TUTORIAL VERSION v130522 Tutorial Customer Choice (Logit) Marketing Engineering for Excel is a Microsoft Excel add-in. The software runs from within Microsoft Excel and only with data contained in an Excel spreadsheet. After installing the software, simply open Microsoft Excel. A new menu appears, called “ME XL.” This tutorial refers to the “ME XL/Customer Choice (Logit)” submenu. Overview The customer choice (logit) model is an individual-level response model that helps analyze and explain the choices individual customers make in a market. The customer choice model helps firms understand the extent to which factors such as the price of a brand or its ease of installation influence a customer's choice. A brand's purchase probability at the individual level can be aggregated to determine the brand's market share at the market level. Firms also can use customer choice analysis to develop marketing programs tailored to specific market segments, or even to individual customers. Further, if a company has purchase data about its products versus those of its competitors (product choice data), as well as some observed independent variables (e.g., gender, price, promotion), it can use customer choice modeling to answer such questions as: Does a customer’s gender influence his or her purchase decision regarding our product(s)? Do competitor’s promotions affect the purchase of our product(s)? How do our promotions affect our sales rates...
Words: 2855 - Pages: 12
...The Measure of Risk - Stock Beta Stock Beta Calculator http://spreadsheetml.com/finance/stockbetacalculatorspreadsheet.shtml Copyright (c) 2008-2009, ConnectCode Pte Ltd. All Rights Reserved. ConnectCode accepts no responsibility for any adverse affect that may result from undertaking our training. No statements in this document should be construed or thought to represent investment advice of any type since the sole purpose of the explanation is to illustrate the technique. Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. All other product names are trademarks, registered trademarks, or service marks of their respective owners Table of Contents 1. 2. Stock Beta .............................................................................................................................. 1-1 Stock Beta Calculator ........................................................................................................... 2-2 2.1 Inputs ......................................................................................................................... 2-2 2.2 Outputs ....................................................................................................................... 2-2 Advance Stock Beta Calculator ........................................................................................... 3-3 Customizing the Stock Beta Calculator .............................................................................. 4-4...
Words: 2050 - Pages: 9
...tables are used, this is the related formula: FV = PV * FVIF Equation 2 in the Week 1 Lecture) Example: You place $100 today in a bank deposit account that pays 10% annual interest. In other words, interest on your $100 worth of principal is paid once a year and will be received one year from today. What will your deposit account be a year from today? Algebraically, the relationship is: FV = $100(1.1) = $110 Using Excel, try entering the following formula into a single spreadsheet cell (hit the "Enter" key on your keyboard after typing in the formula): =100*1.1 If you entered the formula correctly, the cell will display a value of 110. As an aside, while Excel contains numerous shortcuts for present- and future-value calculations, we will not consider these shortcuts for two reasons. First, they do not promote a thorough conceptual understanding of the algebraic processes that necessarily are involved; second, these shortcuts may not (under a variety of circumstances) accurately reflect the modeling required for the more complex cash flow processes that occur under real world conditions. B. Future Value (FV) of a Current Single Lump Sum Compounded Once Each Period for Multiple Periods FV = PV(1+i)n Note: Since FV = PV(1+i)1 = PV(1+i), the formula above also can be applied to the single-period case (where n = 1). (If the tables are used, this is the related formula: FV = PV * FVIF Equation 2 in the Week 1 Lecture) Example: You place $100 today in a bank deposit...
Words: 1410 - Pages: 6
...Finite Mathematics and Its Applications, 6th Ed. , by Goldstein, Schneider, and Siegel, Prentice Hall, 1997 Introduction In any introductory mathematics course designed for non-mathematics majors, it is important for the student to understand and apply mathematical ideas in a variety of contexts. With the increased use of advanced software in all fields, it is also important for the student to effectively interact with the new technology. Our goal is to integrate these two objectives in a supplement for the text Finite Mathematics and Its Applications, by Goldstein, Schneider, and Siegel. The package consists of interactive tutorials and projects in an Excel workbook format. The software platform used is the Microsoft Excel 5.0 spreadsheet. It was chosen for the following reasons: • • • suited to applications encountered in a finite math course widespread use outside of academia ease of creating reports with a professional look Use of Excel 5.0 was put into effect in the author's sections of the Finite Mathematics II course in the Spring 1996 semester. It was expanded to cover the Finite Mathematics I course for the Fall semester of 1996. Using a combination of specially designed projects and tutorials, students are able to analyze data, draw conclusions, and present their analysis in a professional format. The mathematical and computer skills learned with such an approach is an asset that they can carry with them to other courses as well as to their future places of...
Words: 3176 - Pages: 13
