...Advanced Modelling in Finance using Excel and VBA Mary Jackson and Mike Staunton JOHN WILEY & SONS, LTD Chichester ž New York ž Weinheim ž Brisbane ž Singapore ž Toronto Copyright 2001 by John Wiley & Sons, Ltd, Baffins Lane, Chichester, West Sussex PO19 1UD, England National 01243 779777 International (C44) 1243 779777 e-mail (for orders and customer service enquiries): cs-books@wiley.co.uk Visit our Home Page on http://www.wiley.co.uk or http://www.wiley.com All Rights Reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except under the terms of the Copyright, Designs and Patents Act 1988 or under the terms of a licence issued by the Copyright Licensing Agency, 90 Tottenham Court Road, London W1P 9HE, UK, without the permission in writing of the publisher. Other Wiley Editorial Offices John Wiley & Sons, Inc., 605 Third Avenue, New York, NY 10158-0012, USA Wiley-VCH Verlag GmbH, Pappelallee 3, D-69469 Weinheim, Germany John Wiley & Sons Australia Ltd, 42 McDougall Street, Milton, Queensland 4064, Australia John Wiley & Sons (Asia) Pte Ltd, 2 Clementi Loop #02-01, Jin Xing Distripark, Singapore 129809 John Wiley & Sons Canada Ltd, 6045 Freemont Blvd, Mississauga, ONT, L5R 4J3, Canada British Library Cataloguing in Publication Data A catalogue record for this book is available from the British...
Words: 57326 - Pages: 230
...Advanced Financial Modeling Macros © EduPristine – www.edupristine.com/ca Agenda Introduction to Macros & Advanced Application Building in Excel • Introduction and context Key Concepts in Macros Macros as recorded Robots! Designing application to gather user data • Implementing user forms in Excel Macros • Linking forms to excel cells • Building flexible Charts in Excel Understanding VBA • Understanding the relevant language constructs • Example: Write your First Macro Debugging VBA code • Errors in VBA • Step into VBA Writing more VBA Codes • Random Number Generator: Use of Loop, Excel Functions • Message Box, Input Box Advanced Financial Applications • Breaking circular loops • Monte Carlo Simulation © EduPristine Macros 2 Agenda Introduction to Macros & Advanced Application Building in Excel • Introduction and context Key Concepts in Macros Macros as recorded Robots! Designing application to gather user data • Implementing user forms in Excel Macros • Linking forms to excel cells • Building flexible Charts in Excel Understanding VBA • Understanding the relevant language constructs • Example: Write your First Macro Debugging VBA code • Errors in VBA • Step into VBA Writing more VBA Codes • Random Number Generator: Use of Loop, Excel Functions • Message Box, Input Box Advanced Financial Applications • Breaking circular loops • Monte Carlo Simulation © EduPristine Macros 3 Building user applications in excel requires knowledge...
Words: 4063 - Pages: 17
...value of Equity Shares.The shares were valued as a multiple of 5 times the weighted average EPS for last three years i.e. 2010-2012 considering qualitative factors such as diversified business model and high growth prospects based on valuation of company. The weighted average EPS for 2010-12 is 3 (approx). • Thus shares are issued to PE @ Rs150 per share (EPS 3* 5 times*face value of share 10) • PE investment enables company to raise more loans. The capital thus raised, was also used for advertising and brand building thus increasing the net sales of the company by an average of 60% • Company is raising Debt through Secured Loans hypothecated by company assets as well as Unsecured Loans and a Bank Revolver • PE exits the business in financial year end of March 2015. It offers its stake for sale in the IPO that the company has planned in May 2015 • In May 2015 the company issues an IPO. The offer is both an offer for sale and fresh issue of shares to public. The IPO will provide liquidity to PE and raise capital for further expansion of business. • Total 80,25,000 shares are issued to...
Words: 534 - Pages: 3
...May 1, 2012 To: The Star plus Manufacturing Inc., Executive Members, Chief Executive Officer and All Others that this may concern: On April 24, 2012 the executive consulting offices of Hanns-G LLC, Have continued their investigation and Data Analyzing of the current business processes that have concurrently been in progress at Star plus Manufacturing Inc. Through our Time Spend at Star plus manufacturing Inc., we have conducted the following Business Analytics including but not limited too; Analyzing Qualitative Data, Analytics, Business Intelligence, Test and Learn, Business Processes, Statistics and Customer Dynamics. While Analyzing Qualitative Data; we have conducted Open-ended Questions, accepted written comments on questionnaires in order to generate Single word opinions, Brief Outlooks on company environment. We have also found some finding though daily business observations. During our Analytics practices, we have been able to develop optimal or realistic decision recommendations based on insights derived through the application of statistical models and analysis against existing and/or simulated future data. Business intelligence used a well-established process in guiding organizational change through using Computer-Based Techniques to identify, extract and analyze business data, such as Sales revenue by individual departments and products by each ones associated Costs and Income. Test and Learn methods in order to define the impact that, current strategies are...
Words: 521 - Pages: 3
...MARKETING 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...
Words: 2855 - Pages: 12
...Business Intelligence Journal The Evolution of Information Management By Don Hatcher, Bill Prentice Customers today are demanding better service, lower prices, and higher quality goods. With such a volatile economy in recent years and with so many businesses competing for the same customers, it is imperative for companies to continually improve their customer service or else risk falling victim to their competitors. This is one reason why many organizations are rethinking how they do business. For years, they have accumulated valuable information as a by-product of production while failing to put it to good use. When a company knows its customers’ buying patterns, interests, and demographics, it provides a distinct competitive advantage. This knowledge has become so critical in recent years that the process of managing information has become an industry of its own. How does a company manage its strategic information assets in today’s rapidly changing business environment? What challenges arise out of that task? Are there any preventive measures that can ease the “growing pains” associated with moving from one information paradigm to the next? No matter how simple or convoluted the current information architecture is, evolving companies’ effective use of information can help them achieve a level of sustainable competitive advantage that can be measured on the bottom line. [pic] Figure 1. The Information Evolution Model and its Five Levels The Information Evolution...
Words: 3863 - Pages: 16
...Strategic Planning for eServices1 A Short Tutorial for the Beginners2 Amjad Umar, Ph.D. Senior Technical Advisor, United Nations-GAID Executive Summary Strategic planning of ICT (Information and Communication Technologies)-based services, abbreviated as eServices, is a crucial task for the public as well as private sectors. Given a strategic project (or an initiative), a strategic planning process identifies the main alternatives, the key business/technical issues involved in each alternative, and helps in evaluation and selection of the most viable alternatives before initiating the project. To succeed, the strategic planning process must explore a large number of people, process and technology issues and eliminate surprises. This is not easy because the task of eservices planning in the digital age is considerably complicated due to the changing business and technical landscape and an ever-growing body of knowledge. This short tutorial gives a quick overview of the vast body of knowledge that entails a typical strategic ICT planning process and presents a conceptual framework for further exploration of this important area. 1. An Example – eServices for a City Ms Fran Kuye is mayor of a city with one million inhabitants, located in a developing country.She wants to use the knowledge gained from her MPA (Master of Public Administration) to transform her city to a “Digital City” that heavily relies on eservices to support its citizens. Her overall goal is...
Words: 5384 - Pages: 22
...System Modeling Margaret Francies Strayer University Term Paper System Modeling Theory– CIS 331 Richard Guirguis Due on 12/8/2010 System Modeling Systems modeling or systems modelling is the approach to the study of the use of models to imagine and construct systems in business and IT development. In business and IT development the term "systems modeling" has multiple meaning. System Modeling can refer to analysis and design efforts, simulation and or system dynamics, and a study of the many uses of these models. There are different approaches to modeling: Agent based data and mathematical modeling. Quality management adopts a number of management principles that can be used by top management to guide their organizations towards improved performance. Principles such as: customer focus, leadership, people’s involvement, the process, system approach to management, continual improvement, facts, and a mutual benefit. . Decision making is a reasoning or emotional process which can be rational or irrational. The cognitive perspective is that the decision making process must be regarded as a continuous process integrated in the interaction with the environment. From a normative perspective, the analysis of individual decisions is concerned with the logic of decision making and rationality and the invariant choice it leads to it can be based on explicit assumptions or tacit assumptions. In decision making, objectives must be established and placed in the order of...
Words: 662 - Pages: 3
...ABB Electric Segmentation - Group Case Analysis Team Members: Emeka Ejika John Marceaux Brandon McNabb Todd Teepell Brandon Woods Suppose you are the regional sales manager for ABB Electric, and you have been given a budget for a supplementary direct marketing campaign aimed at 20% of the companies in your region 1. At present, you have information on the Descriptor Data Tab of the ABB Electric Data (Customer Choice).xls spreadsheet about the location of customers (districts 1, 2, and 3) and the sales potential of each account of prospect. Based on this information alone, to what companies would you direct the new direct marketing program? Specify the accounts and customer or prospect types. In order to determine which potential customers to target with a direct marketing campaign we first searched the ‘ABB Descriptor Data’ and identified Customers that we are currently serving to quantify ABB’s market penetration in each District. We then sorted the remaining Customers (Competitor’s clients) by Annual Purchase Volume as it is the only quantifiable metric provided. Next we cross-referenced that with our market penetration in each District. While ABB’s penetration is deepest in District 1 relative to number of customers serviced, District 2 provides the largest source of Annual Purchase Volume by a margin of three times the next closest District (D3). This extreme disparity is the result of one customer with a very large Annual Purchase Volume that is over seventeen...
Words: 1981 - Pages: 8
...The two risks involved with outsourcing a business process (the off-shore ministry of health) 1. Does the outsourced vendor have adequate internal controls in place 2. Is the outsourced vendor able to provide the same or better quality of the product or service that could be achieved by keeping the business process in house. Three recommendations for the two above risks 1. Ensure there are means of monitoring the effectiveness of the outsourced business process 2. Obtain assurance that the internal controls imbedded in the outsourced business process are operating effectively, through internal audits or external reviews of these controls. 3. Periodically Re-evaluate whether the business case for outsourcing the process remains valid. Limitations of Internal control (5 examples) 1. Human judgment is not perfect(subject to bias) 2. Breakdowns can occur because of errors or mistakes 3. Controls can be circumvented by collusion (two or more people) 4. Management can override controls 5. Controls must be cost effective (costs versus benefits)/ 6. External events outside the organizations control. Why is important from a governance prospective to have an outside director on the Board of Directors. 1) Management receives direct compensation for their work and makes decisions that benefit the short run instead of shareholders long term goals. Having independent directors who don’t receive direct compensation but receive...
Words: 725 - Pages: 3
...Promissory Estoppel Brian McDonald and Harry Ledman are co-workers at Food mart. They both love model trains and have them as a hobby. While having a casual discussion about the model trains, Brian tells Harry that after he retires he wants to sell his trains and travel. Brian mentions to Harry, “You are the only person I plan to offer my trains to, because I know that you would take care of them.” This statement set the stage for a verbal contract with a pending date of sale, the date of sale being Brian’s retirement. Harry made plans to purchase the train station by saving money, borrowing money and adding on to his home to prepare for the train set. Brian was aware of all the plans that Harry was making. Upon Brian’s retirement he told the train set to his neighbor, James. Harry sued Brian, claiming breach of contract, or in the alternative, for promissory estoppel. Harry’s claims of promissory estoppel are based on the grounds that a promisor (Brian) made a gratuitous promise (to sell his train set) to a promise (Harry). Harry relied on the promise and made purchases to the fact. Brian was aware of all the purchases that Harry was making and he never said anything to Harry to suggest that he had changed his mind. Harry will ask the judge for compensation for all he spent preparing for the train set, the cost of building a 2,000 sq. ft. addition to his home and the loan from aunt Ida the 2,000 square foot room that Harry built for the model train collection and the...
Words: 438 - Pages: 2
...HO Craftsman Rolling-stock Marketing Plan Keller Graduate School of Management – Online MM522 John Superb Student July 2011 Final Draft Executive Summary HO Craftsman Rolling stock is an Internet based company that will develop, manufacture, and distribute kits of model railroad rolling-stock whose prime component is scale wood. The company mission statement is: HO Craftsman Rolling-stock will supply top quality, wooden, craftsman level kits of HO scale rolling-stock that appeals to the existing model railroad craftsman, and which will be uniquely appealing to first time crafters. HO Craftsman Rolling-stock will offer unparalleled support to their customers and will improve and develop their offerings based primarily on customer input. These wooden kits will be considered craftsman level because of the tools, skill-set, and time involved. Significant effort will be expended to reduce the intimidation level of this kind of kit. This will be the only offering of its type, although kits like these were available until 10 years ago. It is believed that poor marketing was the primary reason for the disappearance of those kits. HO Craftsman Rolling-stock believes that leveraging the strength of the Internet, a well targeted advertising campaign, and development of comprehensive customer assistance will allow these types of kits to become popular again. There is considerable market inertia to be overcome. The...
Words: 5054 - Pages: 21
...CONVERSION TABLES HOBBY CONVERSION CHART SCALE EQUIVALENTS COMPUTED TO THE NEAREST 1/64” 1” = 100’ 1” = 75’ 1” = 60’ 1” = 50’ 1” = 40’ 1” = 30’ 1” = 20’ 1” = 10’ 1/32” = 1’ 1/16” = 1’ 3/32” = 1’ 1/8” = 1’ 3/16” = 1’ 1/4” = 1’ 3/8” = 1’ 1/2” = 1’ 3/4” = 1’ 1” = 1’ 1:1200 1:900 1:700 1:600 1:500 1:400 1:250 1:125 1:400 1:200 1:125 1:100 1:75 1:48 1:32 1:24 1:16 1:12 ACTUAL SIZE 1” = 1’ 1:12 G 1:24 #1 1:32 2” 4” 6” 8” 10” 1’ 2’ 3’ 4’ 5’ 10’ 5/32” 11/32” 1/2” 21/32” 27/32” 1” 2” 3” 4” 5” 10” 5/64” 11/64” 1/4” 21/64” 27/64” 1/2” 1” 1-1/2” 2” 2-1/2” 5” 1/16” 1/8” 3/16” 1/4” 5/16” 3/8” 3/4” 1-1/8” 1-1/2” 1-7/8” 3-3/4” O 1:48 S 1:64 HO 1:87 TT 1:120 N 1:160 Z 1:250 3/64” 5/64” 1/8” 11/64” 13/64” 1/4” 1/2” 3/4” 1” 1-1/4” 2-1/2” 1/32” 1/16” 3/32” 1/8” 5/32” 3/16” 3/8” 9/16” 3/4” 15/16” 1-7/8” 1/64” 3/64” 1/16” 3/32” 7/64” 9/64” 9/32” 13/32” 35/64” 11/16” 1-3/8” 1/64” 1/32” 3/64” 1/16” 5/64” 3/32” 13/64” 19/64” 13/32” 1/2” 1” 1/64” 1/64” 1/32” 3/64” 1/16” 5/64” 5/32” 15/64” 19/64” 3/8” 3/4” 1/64” 1/64” 1/64” 1/32” 3/64” 1/16” 7/64” 5/32” 7/32” 17/64” 35/64” FRACTION TO DECIMAL CONVERSION CHART FRA. DEC. FRA. DEC. FRA. DEC. FRA. DEC. 1/64 1/32 3/64 1/16 5/64 3/32 7/64 1/8 9/64 5/32 11/64 3/16 13/64 7/32 15/64 1/4 .0156 .0312 .0468 .0625 .0781 .0937 .1093 .125 .1406 .1562 .1718 .1875 .2031 .2187 .2343 .2500 17/64 9/32 19/64 5/16 21/64 11/32 23/64 3/8 25/64 13/32 27/64 7/16 29/64 15/32 31/64 1/2 .2656 .2812 .2968 .3125 .3281 .3437 .3593 .3750 .3906 .4062...
Words: 1180 - Pages: 5
...FRACTION/DECIMAL/METRIC CONVERSION CHART 4th 8th 16th 32nd 64th 1/64 1/32 3/64 1/16 5/64 3/32 7/64 1/8 9/64 5/32 11/64 3/16 13/64 7/32 15/64 1/4 17/64 9/32 19/64 5/16 21/64 11/32 23/64 3/8 25/64 13/32 27/64 7/16 29/64 15/32 31/64 1/2 Inch .016 .031 .047 .063 .078 .094 .109 .125 .141 .156 .172 .188 .203 .219 .234 .250 .266 .281 .297 .313 .328 .344 ..359 .375 .391 .406 .422 .439 .453 .469 .484 .500 MM .397 .794 1.191 1.588 1.984 2.381 2.788 3.175 3.572 3.969 4.366 4.762 5.159 5.556 5.953 6.350 6.747 7.144 7.541 7.938 8.334 8.731 9.128 9.525 9.922 10.319 10.716 11.112 11.509 11.906 12.303 12.700 1 15/16 61/64 31/32 63/64 7/8 57/64 29/32 59/64 13/16 53/64 27/32 55/64 3/4 49/64 25/32 51/64 11/16 45/64 23/32 47/64 5/8 41/64 21/32 43/64 9/16 37/64 19/32 39/64 4th 8th 16th 32nd 64th 33/64 17/32 35/64 Inch .516 .531 .547 .563 .578 .594 .609 .625 .641 .656 .672 .688 .703 .719 .734 .750 .766 .781 .797 .812 .828 .844 .859 .875 .891 .906 .922 .938 .953 .969 .984 1.000 MM 13.097 13.494 13.891 14.288 14.684 15.081 15.478 15.875 16.272 16.669 17.066 17.462 17.859 18.256 18.653 19.050 19.447 19.844 20.241 20.638 21.034 21.431 21.828 22.225 22.622 23.019 23.416 23.812 24.209 24.606 25.003 25.400 Kellogg Community College ■ Department of Computer-Aided Drafting and Design STAND5.doc...
Words: 350 - Pages: 2
...CHAPTER 1 What Is Financial Modelling? here are all sorts of complicated definitions of financial modelling, and in my experience there is quite a bit of confusion around what a financial model is exactly. A few years ago, we put together a Plum Solutions survey about the attitudes, trends, and uses of financial modelling, asked respondents “What do you think a financial model is?” Participants were asked to put down the first thing that came to mind, without any research or too much thinking about it. I found the responses interesting, amusing, and sometimes rather disturbing. Some answers were overly complicated and highly technical: n T n n n n “Representation of behaviour/real-world observations through mathematical approach designed to anticipate range of outcomes.” “A set of structured calculations, written in a spreadsheet, used to analyse the operational and financial characteristics of a business and/or its activities.” “Tool(s) used to set and manage a suite of variable assumptions in order to predict the financial outcomes of an opportunity.” “A construct that encodes business rules, assumptions, and calculations enabling information, analysis, and insight to be drawn out and supported by quantitative facts.” “A system of spreadsheets and formulas to achieve the level of record keeping and reporting required to be informed, up-to-date, and able to track finances accurately and plan for the future.” Some philosophical: n “A numerical...
Words: 17366 - Pages: 70