Nội dung

Financial Modeling with Crystal Ball and Excel JOHN CHARNES John Wiley & Sons, Inc. Financial Modeling with Crystal Ball and Excel Founded in 1807, John Wiley & Sons is the oldest independent publishing company in the United States. With offices in North America, Europe, Australia and Asia, Wiley is globally committed to developing and marketing print and electronic products and services for our customers’ professional and personal knowledge and understanding. The Wiley Finance series contains books written specifically for finance and investment professionals as well as sophisticated individual investors and their financial advisors. Book topics range from portfolio management to e-commerce, risk management, financial engineering, valuation and financial instrument analysis, as well as much more. For a list of available titles, please visit our Web site at www.WileyFinance.com. Financial Modeling with Crystal Ball and Excel JOHN CHARNES John Wiley & Sons, Inc. c 2007 by John Charnes. All rights reserved. Copyright
Published by John Wiley & Sons, Inc., Hoboken, New Jersey. Published simultaneously in Canada. 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 as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 750-4470, or on the Web at www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permission. Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives or written sales materials. The advice and strategies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Neither the publisher nor author shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages. For general information on our other products and services or for technical support, please contact our Customer Care Department within the United States at (800) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002. Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books. For more information about Wiley products, visit our Web site at www.wiley.com. Library of Congress Cataloging-in-Publication Data: Charnes, John Martin. Financial modeling with crystal ball and Excel / John M. Charnes. p. cm.—(Wiley finance series) ‘‘Published simultaneously in Canada.’’ Includes bibliographical references. ISBN 13: 978-0-471-77972-8 (paper/cd-rom) ISBN 10: 0-471-77972-5 (paper/cd-rom) 1. Finance–Mathematical models. 2. Investments–Mathematical models. 3. Crystal ball (Computer file) 4. Microsoft Excel (computer file) I. Title. HG106.C485 2007 332.0285 554—dc22 2006033467 Printed in the United States of America. 10 9 8 7 6 5 4 3 2 1 In Memory of Gerald Daniel Charnes, 1925–2005 Contents Preface xi Acknowledgments xv About the Author xvii CHAPTER 1 Introduction Financial Modeling Risk Analysis Monte Carlo Simulation Risk Management Benefits and Limitations of Using Crystal Ball CHAPTER 2 Analyzing Crystal Ball Forecasts Simulating A 50–50 Portfolio Varying the Allocations Presenting the Results CHAPTER 3 Building a Crystal Ball Model Simulation Modeling Process Defining Crystal Ball Assumptions Running Crystal Ball Sources of Error Controlling Model Error CHAPTER 4 Selecting Crystal Ball Assumptions Crystal Ball’s Basic Distributions Using Historical Data to Choose Distributions Specifying Correlations 1 2 2 4 8 8 11 11 21 27 28 28 29 32 33 35 36 36 54 63 vii