...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
...Used: * Microsoft Excel 2007 (Home edition) * Microsoft Word 2008: (License No: 92464-513-2506215-12994) * Microsoft Expression Web (Student version) Attached Files: * Business Report.docx * Spreadsheet Assignment.xlsm * Website files * home.html * about.html * sit.html * work.html * registration.html * locate.html * contact.html * Formulae sheets Attempted the High Distinction Level Table of Contents 1.0 Executive Summary 3 2.0 Business Problem Opportunity 4 3.0 Recommendations (based on ‘What-if’ analysis) 5 3.1 Production targets 5 3.2 Fixed costs 5 3.3 Sales 6 4.0 Appendices 7 4.1 User instructions 7 1.0 Executive Summary Classic Offices is a company that manufactures and sells chairs. The recent recession of 2009 had a negative global effect on markets all over the world and Classic Offices was adversely affected too. Along with vital information such as income and commission figures, the increment of fixed costs and reduction of profits have been captured in the form of this user-friendly spreadsheet. This spreadsheet has been designed with the consideration of Classic Offices’ internal users in mind. To make interpretation of the spreadsheet easier: * Different years have been colour coded * Appropriate command buttons (e.g. ‘Home’, ‘Print’) are accompanied with icons Internal users of Classic Offices (mainly employees) will be able to view...
Words: 1137 - Pages: 5
...Transshipment, and Assignment Problems Chapter 13: Decision Analysis Faculty Pankaj Dutta Chapter – 11 Linear Programming LPP solution through Excel Solver: The Steps in Implementing an LP Model in a Spreadsheet: 1. 2. 3. 4. Organize the data for the model on the spreadsheet. Reserve separate cells in the spreadsheet for each decision variable in the model. Create a formula in a cell in the spreadsheet that corresponds to the objective function. For each constraint, create a formula in a separate cell in the spreadsheet that corresponds to the left-hand side (LHS) of the constraint. PANKAJ DUTTA IMTCDL Chapter – 11 Linear Programming LPP solution through Excel Solver: Max Z = 350X1 + 300X2 Subject To 1X1 + 1X2 ≤ 200 9X1 + 6X2 ≤ 1566 12X1+16X2 ≤ 2880 X1 , X 2 ≥ 0 1. Organize the data for the model on the spreadsheet. PANKAJ DUTTA IMTCDL Chapter – 11 Linear Programming LPP solution through Excel Solver: Max Z = 350X1 + 300X2 Subject To 1X1 + 1X2 ≤ 200 9X1 + 6X2 ≤ 1566 12X1+16X2 ≤ 2880 X1 , X 2 ≥ 0 1. Organize the data for the model on the spreadsheet. Changing cells Target cell PANKAJ DUTTA IMTCDL Constraint cells Chapter – 11 Linear Programming Max Z = 350X1 + 300X2 Subject To 1X1 + 1X2 ≤ 200 9X1 + 6X2 ≤ 1566 12X1+16X2 ≤ 2880 X1 , X 2 ≥ 0 LPP solution through Excel Solver: 1. Organize the data for the model on the spreadsheet. – Target cell - the cell in the spreadsheet that represents the objective ...
Words: 798 - Pages: 4
...[pic][pic] FIN 222 Corporate Finance Individual Major Assignment [80 marks] This manual outlines the followings. Objective of the assignment Details of questions (Instructions will be provided wherever necessary.) Format requirements Printing requirements Submission requirements ➢ Objective of the Assignment In Lecture 5 (CH11 and CH12), you were introduced to the concept of systematic risk, as measured by beta. Beta is an indicator of an asset’s exposure (or vulnerability) to systematic risk and evaluates the degree to which an asset’s return is correlated with the market return. Beta is a key parameter in the Capital Asset Pricing Model (CAPM) which is most commonly used asset pricing model for the estimation of required rate of returns on a share (i.e. cost of equity=rE). This assignment consists of two parts. Part I directs you to work with real financial data in Excel and aims to take you through the basic exercise of estimating a company beta and computing statistics which can be evaluated as part of investment decision-making process. Part II requires you to write a report based on interpretations of the outputs obtained from the first part. In this manual, you will also find out how to download historical share price, market index data and interest rates from the web portal. ➢ Details of Questions PART 1 [30 marks] To successfully complete PART 1, please read each section carefully. You must read “Format and printing requirement” before you...
Words: 3262 - Pages: 14
...DeVry University Keller Graduate School of Management Creating a Database for Storing and Managing Church Members’ Record for R.C.C.G. Heaven’s Gate Gbolade O. Soneyin gsoneyin@my.keller.edu (770) 598-5006 MIS-535-18371 Managerial Applications of Information Technology Professor Tanya Cannon Thursday, February 12, 2015 Table of Contents Subject of Course Project 3 Business Problem Stated 3 Name of the Organization 3 Proposed Solution 3 General Benefits 4 Audience 4 References 5 Abstract To help Heaven’s Gate move away from tracking its members on spreadsheets and paper, move to a database application that will not only track members, but also track department activities, members contributions, and do it all via the least economically, but yet effective, easily accessible and requiring limited technical or IT expertise to maintain. Keywords: RCCG, Database, Church, Heaven’s Gate, Application, Church Management Company Background Heaven’s Gate is one of many parishes of the Redeemed Christian Church of God (RCCG), headquartered in Lagos, Nigeria; finding out exactly how many parishes of the church existed was next to impossible. In her well-detailed article on the church, Olufunke Adeboye wrote about the church from 1952-2005. Founded by Josiah Olufemi Akindayomi in 1952, after receiving vision to write down the church’s name on a wooden board, this despite the fact the man was unschooled at the time; he cannot read nor know how to write. After writing...
Words: 1607 - Pages: 7
...Excel Reflection: I learned how to write and use formulas in excel. You write formulas by first putting in an equal sign and then adding numbers and symbol. I also learned new ways to format my spreadsheet like merging cells and stuff like that. This program can help you organize better because it forces you to display the information logically or it doesn’t make sense. Also it helps you to calculate because the computer is doing all the math so you are less likely to do mistakes. It is important to learn how to create spreadsheets because your job in the future may include organizing information into a spreadsheet. I think Excel’s most powerful feature is the formulas because people don’t want to make mistakes while calculating. In excel the computer does the map so it will not make mistakes. Examples of Excel in your job is when you have to organize prices for make a budget for the company. Excel could help you in to future because your job could include organizing budgets or information. Also it could help me because I could get a job easier with my technology background. I think Excel is an important program to be taught in school because it helps you learn to be organized. It also needs to be taught because things are becoming less paper and more technology. I didn’t have any problems doing Excel this year because this is my third year doing Excel. I would rate myself a 7 because I am pretty good at Excel but I do not know everything about Excel. Another reason I gave myself...
Words: 288 - Pages: 2
...Problem 1 Print Media Advertising (PMA) Click Link Below To Buy: http://hwcampus.com/shop/problem-1-print-media-advertising-pma/ Problem 1 Print Media Advertising (PMA) has been given a contract to market Buzz Cola via newspaper ads in a major southern newspaper. Full-page ads in the weekday editions (Monday through Saturday) cost $2000, whereas on Sunday a full-page ad costs $8000. Daily circulation of newspaper is 30,000 on weekdays and 80,000 on Sundays. PMA has been given a $40,000 advertising budget for the month of August. The experienced advertising executives at PMA feel that both weekday and Sunday newspaper ads are important; hence they wish to run the equivalent of at least eight weekday and at least two Sunday ads during August. (Assume that a fractional ad would simply mean that a smaller ad is placed on one of the days; that is, 3.5 ads would mean three full-page ads and one half-page ad. Also, assume that smaller ads reduce exposure and costs proportionately.) This August has 26 weekdays and 5 Sundays. The objective is to determine the optimal placement of ads by PMA in the newspaper during August so as to maximize the cumulative total exposure (as measured by circulation) for the month of August. a) Formulate the linear programming model for the problem. b) Use the Graphical method to find the optimal solution. Show all steps. c) Use Excel Solver to find the optimal solution. Copy and paste your spreadsheet and the Answer report in its entirety...
Words: 444 - Pages: 2
...Assignment #1: JET Copies Case Problem Strayer University MAT540: Quantitative Methods October 29, 2013 JET Copies is a company designed to alleviate a longer commute and longer wait time, and possibly have a more cost efficient method for the college students to make copies. The three students James, Ernie, and Terri decided to go into business together with a copying business initiative. Considering what was ahead of the new business, for example, possible machine downtime and days to repair the copier, they had to determine the average number of days that it would take for them to acquire a repair team to fix the machine in the event that it broke down. As discovered, the average time for repair was between one and four days. In order to calculate the average, a probability distribution was developed using Microsoft Excel. From there, the cumulative probability was obtained by adding the probability, P(x), from the previously itemized probabilities where the cumulative summation of a probability is always equal to one (1) or 100%. A random number formula, =RAND(), was plugged into the Microsoft Excel desired cell, in this situation, (H4), which generated a random range of numbers that are greater than or equal to zero and less than one. The interim time between breakdowns were achieved simply by soliciting the experience several staff members in the college of business who were familiar with frequency of the copier’s inconsistent behavior. It was estimated that the...
Words: 679 - Pages: 3
...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
...ForecastX Software (There will be additional ForecastX notes for subsequent chapters) 1. Insert disk and install a. Note: you will have to do this every time you use a campus computer, but the process should only take a minute or two. b. Insert disk. If a McGraw Hill screen pops up, just agree to what it is asking you, and then reduce the window. c. Double click on “My computer” d. Select (click once on) cdrom drive (or whatever the listing is for the drive into which you have inserted your disk), right click over the shaded area, select “open” from the menu, click on “forecast x installer”, click on “setup.exe” (You may find there are two “setup” files, if so, choose the top one, but try the bottom one if the top one doesn’t work.) 2. Add in the forecast x add-in. a. Open excel b. Click on the following: File, options, add-ins. You should see “Forecastx7Toolbar” underneath “Active Application Add-Ins”. To make sure that ForcastX is installed, click on “go” at the bottom of the page, and make sure that there is a check in the check box next to ForecastX in the pop up box that appears, then click on OK. Then close out of Excel, and reopen Excel. You should find ForecastX when you select the Add-Ins bar at the top of the page. c. If you do not see ForecastX in the pop up box, click ok and close Excel. Then close Excel and do step 2b again. If you still do not see ForecastX in the pop up box, Click on...
Words: 1777 - Pages: 8
... 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 use mental...
Words: 4929 - Pages: 20
...ECN601 full course latest all discussions and all assignments Click Link Below To Buy: http://hwcampus.com/shop/ecn601-full-course-latest-all-discussions-and-all-assignments/ ECN601 WEEK 1 DQ 1& DQ 2 LATEST 2015 DQ 1 The price of gasoline goes up and down quickly, yet consumer demand for gasoline stays relatively steady. Does this mean the demand for gasoline is inelastic, or is something else at work? Explain. DQ 2 Of the factors listed in the textbook that are related to demand, identify 1 or 2 that were instrumental in your selection of a University in which to enroll for an MBA. Do you think your decision criteria are typical or atypical? Explain. ECN601 WEEK 2 DQ 1& DQ 2 LATEST 2015 DQ 1 Selecting a complete set of independent variables is always an issue when using regression analysis to estimate demand. For example, analysts often use price and advertising to estimate quantity demanded. However, how do they know that these two variables were sufficient? How do we know that we didn’t omit other variables which would have had a significant impact on demand? How do we know that we didn’t include variables that were not needed? Explain how analysts would answer such questions. DQ 2 Suppose you want to use regression analysis to estimate the selling price of single family homes in a given neighborhood. You assemble data on selling price, square footage, number of bedrooms, number of bathrooms, age of the house, and lot size. Which variable(s) are dependent...
Words: 2022 - Pages: 9
...ECN601 full course latest all discussions and all assignments Click Link Below To Buy: http://hwcampus.com/shop/ecn601-full-course-latest-all-discussions-and-all-assignments/ ECN601 WEEK 1 DQ 1& DQ 2 LATEST 2015 DQ 1 The price of gasoline goes up and down quickly, yet consumer demand for gasoline stays relatively steady. Does this mean the demand for gasoline is inelastic, or is something else at work? Explain. DQ 2 Of the factors listed in the textbook that are related to demand, identify 1 or 2 that were instrumental in your selection of a University in which to enroll for an MBA. Do you think your decision criteria are typical or atypical? Explain. ECN601 WEEK 2 DQ 1& DQ 2 LATEST 2015 DQ 1 Selecting a complete set of independent variables is always an issue when using regression analysis to estimate demand. For example, analysts often use price and advertising to estimate quantity demanded. However, how do they know that these two variables were sufficient? How do we know that we didn’t omit other variables which would have had a significant impact on demand? How do we know that we didn’t include variables that were not needed? Explain how analysts would answer such questions. DQ 2 Suppose you want to use regression analysis to estimate the selling price of single family homes in a given neighborhood. You assemble data on selling price, square footage, number of bedrooms, number of bathrooms, age of the house, and lot size. Which variable(s) are dependent...
Words: 2022 - Pages: 9
...that the methodology used in the official report indeed indicates that your county is in fact a high crime county and to write a short report proposing a "better" methodology, where you are to interpret "better" to mean a methodology that takes your county off the list of high crime counties! Instructions Your first task is to attempt to replicate the results of the report you are given based only on the information contained within the report. As you recall, the report should include both "a verbal description of the metric used including at least your method of calculation and the high crime/low crime threshold" and the raw data used to create the metric. If the attached report you have in front of you did this, you should have no problem recreating the results (you will need to cut/paste the data from the appendix of the report given to you into excel). Should you be unable to replicate the results, if I determine that this was because the original report was not written well enough to be replicable then it will not affect your grade. The second task is to create a NEW metric that a) remove Jay County from the list of "high crime" counties, and b) alters the "high crime"/"low crime" status of as few other counties as possible. Finally, you should write a persuasive report summarizing your results. This should be relatively short (roughly 500 words of text) and should include the following information: 1. A verbal...
Words: 614 - Pages: 3
...understanding of Monte Carlo concepts and Excel capabilities to begin building your own Monte Carlo simulations applied to a wide range of business problems, including the construction of short-term profit plans (otherwise referred to as “Cost-Volume-Profit” models, covered in Chapter 9 of the text). Monte Carlo refers to a widely used approach for solving complex problems using computer algorithms to simulate the variables in the model (e.g., a CVP model). Typically, an algorithm is developed to "model" the problem, and then the algorithm is run many times (from a few hundred up to millions) in order to develop a statistical data set for how the model behaves. Simple Example: Tossing a “Fair” Coin—Heads vs. Tails? For the simplest example, consider the basic coin toss. This is a process which has two possible outcomes (heads or tails), each with a 50% probability. In a million coin tosses, roughly half will be “heads” and half will be “tails.” A simple Monte Carlo simulation would support this conclusion. If you were to develop a spreadsheet with a random number generator resulting in 0 for “heads” and 1 for “tails,” then have the spreadsheet recalculate a million times, you would see that very close to 50% of the recalculations resulted in 0 (“heads”) and the other 50% in 1 (“tails”). The basic approach would be to build a spreadsheet with random number generators that replicate the probabilities in the model. This is the essence of simulation: you are simulating the output(s)...
Words: 2387 - Pages: 10
