Business Modeling with Spreadsheets with CD, 2nd Edition

Business Modeling with Spreadsheets with CD, 2nd Edition

Edited by Thin-Yin Leong · Michelle Cheong
McGraw Hill Higher Education August 2010

Specifications

ISBN-13
9780071078887
Publisher
McGraw Hill Higher Education
Publication
August 2010
Format
Paperback + CD-ROM , 472 pages
Jurisdiction
International ? Countri(es) for reference only

Details

The true potential of spreadsheet software lies in their application in problem discovery and situation analysis, which enables business and financial analysts to work rapidly on unstructured problems. Business Modeling with Spreadsheets offers a rich set of business contexts and notes that make learning spreadsheets interesting and acquiring of skills more effective and enduring.

 

Arises from the authors' extensive experience in teaching spreadsheet modeling in undergraduate's and master's courses in SMU, this book is wholly dedicated to the art of exploratory spreadsheet modeling. Unlike other books, it is designed to be modeling-centric, i.e. using the spreadsheet mainly as a computing tool. Real problems that readers encounter on a day-to-day basis are presented, with the aim of helping them derive applicable principles and link principles to practice. Undergraduates, graduate students and professionals whose work involves managing business development, finance, accounting and operations will find this book a refreshing learning guide and handy reference resource.

 

The book now offers 101 spreadsheet exercises and tools, and includes a new chapter featuring an additional 101 business challenges that the readers can do as modeling projects. The coverage now spans Excel 2007, Excel 2003, as well as OpenOffice.org Calc in dedicated sections, making it easier for any person, whether a novice, beginner, or expert user, to learn business modeling using basic and advanced spreadsheet features, as well as programming in Visual Basic for Application and OpenOffice.org Basic. Completed spreadsheet workbooks in all three spreadsheet versions are also provided.

 

New to this Edition

This second edition has been revised to better order the exercises in each chapter and notes in the appendices. New exercises and appendix notes are added to give a wider range of topic coverage, and an exercise per chapter has been selected for which detailed workings are shown. Exercises and tools now total 101, an increase of 29 from the first edition. A lot more than before, there are now 51 tips. The tips from the last edition have been totally reorganized and new ones added. Some of the larger tips given before are split into smaller bite sizes so that the individual ideas are separated and more accessible. The ordering of the tips now better fits the way they are delivered in our classes alongside the exercises.

 

The appendix notes are also rearranged to better fit the learning process and Appendix A is now split into Appendices A-1, A-2, and A-3, with each covering the spreadsheet features and functions of Excel 2007, Excel 2003 (and older versions) and OpenOffice.org Calc, respectively. Calc is included for the need to generalize spreadsheet work beyond Microsoft ExcelTM. Appendix B which deals with Visual Basic and macros is similarly split intoAppendices B-1 and B-2.

 

To extend the modeling learning further, a new chapter (Chapter 9) and 14 additional accompanying workbooks are added as examples of completed student projects. A total of 101 projects (inclusive of example projects) are provided.

Table of Contents

Preface
Acknowledgments
About the Authors


Chapter 1: SPREADSHEET MODELING
Ex 1: ACHILLES AND THE TORTOISE
Ex 2: COMMUNITY WATER TANK
Ex 3: PALIBALO FERRY
Ex 4: ALEX PROCESSING
Ex 5: DATA CENTER ASSETS
Ex 6: FINANCIAL STATEMENT
Ex 7: GROUP TRIP EXPENSES
Ex 8: 100 METERS IN 9 SECONDS
Ex 9: GINI INDEX


Tip 1: Enter data only once
Tip 2: Hard-coding
Tip 3: Data, Model, View
Tip 4: Computer as an analysis tool
Tip 5: Smart guessing


Chapter 2: PROBLEM SOLVING AND SPREADSHEET ENGINEERING
Ex 10: F1 NIGHT CITY RACE
Ex 11: INKJET PRINTER
Ex 12: FINANCIAL PROJECTION
Ex 13: WEB STORE
Ex 14: CASINO HOTEL
Ex 15: VILLAGE COFFEE
Ex 16: INVEST IN EDUCATION
Ex 17: STOPPED WATCH


Tip 6: Systemic view
Tip 7: Systematic modeling
Tip 8: Black-box diagram
Tip 9: Influence diagram
Tip 10: Vitals at the top
Tip 11: Color coding
Tip 12: Macro security


Chapter 3: DATA LOOKUP AND LINKUP
Ex 18: ECHO OFFICE SUPPLIES
Ex 19: TRANSPORT COST
Ex 20: CCH KINDERGARTEN
Ex 21: CHOP SENG PROVISIONS
Ex 22: OCEAN SHIPPING
Ex 23: WEIGHT MANAGEMENT
Ex 24: TOPIX WEIGHTS


Tool 1: LIST MANAGEMENT
Tool 2: MY INVESTMENT PORTFOLIO
Tool 3: FORMS AND LINKS
Tool 4: DATA IMPORT


Tip 13: Table headers
Tip 14: Switch off the lines
Tip 15: Worksheet protection
Tip 16: Linking cells across workbooks
Tip 17: Orderly workbook
Tip 18: Proper file names
Tip 19: Version control
Tip 20: Concurrent editing


Chapter 4: RELATIONSHIPS
Ex 25: TIME VALUE
Ex 26: BUY, LEASE, OR RENT
Ex 27: FLEXIBLE LOAN
Ex 28: HOME LOAN IN CRISIS
Ex 29: LEASE AND BUYBACK
Ex 30: CHARITY DONATION
Ex 31: SOTHIN AND SONS
Ex 32: BLACK'SCHOLES
Ex 33: BINOMIAL OPTION
Ex 34: FOREIGN INVESTMENT
Ex 35: GAME SCORING
Ex 36: SAILING


Tip 21: Working in the vacuum
Tip 22: Entered before'
Tip 23: Point and drag
Tip 24: Right the first time
Tip 25: Break it up
Tip 26: Model documentation


Chapter 5: MAKING ASSUMPTIONS
Ex 37: WXYZ CONSTRUCTION
Ex 38: FLY-BY-DAY AIR
Ex 39: VILLAGE WATER
Ex 40: EXTREME EXPLORERS
Ex 41: WALK PLANS
Ex 42: CRAZY AUCTION
Ex 43: MARKET CLEARING PRICE
Ex 44: BUY, SHARE, AND RENT
Ex 45: MAKE A DEAL
Ex 46: FAST SODUKU
Ex 47: GOOGLE ADWORDS


Tip 27: Keep it simple
Tip 28: Verify and validate
Tip 29: Test cases
Tip 30: Back-testing


Chapter 6: RISKS AND UNCERTAINTIES
Ex 48: MONTE HALL
Ex 49: CHANGE DISPENSING
Ex 50: FREQUENCY DISTRIBUTION
Ex 51: DATA SIMULATION
Ex 52: JOHN LIM'S RETIREMENT
Ex 53: PORTFOLIO SIMULATION
Ex 54: FORECAST EYEBALL
Ex 55: YANKEE FRUITS
Ex 56: WONDERCOOKIES 

Tool 5: STATISTICAL LIES
Tool 6: STATISTICS REVIEW
Tool 7: PROBABILITY FUNCTIONS
Tool 8: RESAMPLING
Tool 9: LUCKY DRAW
Tool 10: CARD GAME
Tool 11: PICK A NUMBER
Tool 12: BINGO


Tip 31: Modeling versus models
Tip 32: Types of models
Tip 33: Keeping it alive
Tip 34: RAND in formulas


Chapter 7: PROCESSES AND TIME
Ex 57: TIMER-CLICKER
Ex 58: XDB BANK
Ex 59: CALL CENTER
Ex 60: TANDEM QUEUES
Ex 61: EMERGENCY SERVICES
Ex 62: TIME AND TIDE


Tool 13: WEEK SCHEDULE
Tool 14: YEAR PLANNER ' DAYS AND WEEKS
Tool 15: WEEK PLANNER
Tool 16: INVESTMENT JOURNAL
Tool 17: COUNTDOWN


Tip 35: Invisible macro buttons
Tip 36: Snap to fit
Tip 37: Less clutter
Tip 38: Six months later


Chapter 8: ANALYZING THE DATA
Ex 63: HOTEL APEX
Ex 64: LIFE EXPECTANCY
Ex 65: DUREX GLOBAL SEX SURVEY
Ex 66: MY HAIR PLACE
Ex 67: ABC SERVICES
Ex 68: OFFSHORE DEVELOPMENT
Ex 69: GRAND GROCERY
Ex 70: PORTFOLIO ANALYSIS
Ex 71: PORTFOLIO PLANNING


Tool 18: SPECIAL STATISTICS
Tool 19: DATA FIT
Tool 20: GOODNESS OF FIT


Tip 39: Modeling analysis and data
Tip 40: Real client
Tip 41: Work with the client
Tip 42: Spontaneity and planning
Tip 43: Enterprise resource


Chapter 9: MODELING PROJECTS
Project 1: B. TANKEE
Project 2: VOLUNTEERS MANAGEMENT
Project 3: FAMILY SERVICES CENTER
Project 4: NEW CAR PURCHASE
Project 5: THE BETTER BOOKSTORE
Project 6: CAKE LEGACY
Project 7: ANIMAL EXCHANGE
Project 8: SUZIE ICE RINK
Project 9: METRO GET-AWAY
Project 10: FREELANCE DANCERS
Project 11: LOVE-IN-THE-AIR
Project 12: FAMILY CLINIC
Project 13: LL LEGAL
Project 14: CAT-TV


Community and Individual
Project 15: AUTO CHALLENGE
Project 16: FREE BUS STOPS
Project 17: ALUMNI GIVING
Project 18: VWO FINANCIAL RESERVES
Project 19: Z-LINK
Project 20: COMMUNITY MAPPING
Project 21: STUDENT SOCIETY
Project 22: WATERWAYS WATCH
Project 23: PLANNING 5Cs
Project 24: CPF LIFE
Project 25: BUY OR RENT PROPERTY
Project 26: CALORIES AND NUTRITION


Business and Services
Project 27: CAFE STOP
Project 28: KJC RESTAURANTS
Project 29: SOUND EQUIPMENT
Project 30: VIDEO RENTAL
Project 31: KARAOKE
Project 32: BLACKJACK
Project 33: NEWSPAPER READERSHIP
Project 34: KINDERGARTEN
Project 35: UNIVERSITY LIBRARY
Project 36: INTERNATIONAL CONFERENCE


New Venture
Project 37: FRANCHISE
Project 38: MULTI-LEVEL MARKETING
Project 39: BRIDAL SALON
Project 40: DIFFERENT CUTS
Project 41: LINGERIE XY
Project 42: VEGGIE MANIA
Project 43: FRIED TOFU
Project 44: GRAB-FOOD EXPRESS
Project 45: SINGAPORE FLYER
Project 46: GRASS SKIING
Project 47: BIKE TOUR


Finance and Administration
Project 48: NBA PLAYER SALARY
Project 49: SALARY INCREMENT BUDGETTING
Project 50: RATIO ANALYSIS
Project 51: INTERNAL CREDIT RATING
Project 52: LOAN DELINQENCIES
Project 53: UNSECURED LOAN
Project 54: INTERNET VALUATION
Project 55: FIRM VALUATION
Project 56: PROPERTY VALUATION
Project 57: HEALTH INSURANCE PREMIUM
Project 58: RELATIVE-VALUE EQUITY TRADING
Project 59: FIXED INCOME ARBITRAGE


Transportation and Distribution
Project 60: SHUTTLE BUS SERVICE
Project 61: COMMUTER PLUS
Project 62: RENT-A-CAR
Project 63: CHARTERED BUS
Project 64: SS SUPERMART
Project 65: SUBWAY SCHEDULE
Project 66: SUBWAY FARE
Project 67: CRUISE ITINERARY DESIGN
Project 68: CRUISE TERMINAL DESIGN
Project 69: AIR FLEET EXPANSION
Project 70: SERVICE FACILITY LOCATION
Project 71: TRAFFIC GROWTH


Manufacturing and Operations
Project 72: TECHNOLOGY CHOICE
Project 73: FLEXIBLE CAPACITY
Project 74: CAR DEMAND
Project 75: INSPECTION SAMPLING
Project 76: ECONOMIC ORDER QUANTITY
Project 77: NEWSVENDOR PROBLEM
Project 78: INVENTORY ABC
Project 79: MULTIPLE PERIOD INVENTORY SYSTEMS
Project 80: PRODUCTION SEQUENCE
Project 81: BUCKET BRIGADE
Project 82: BEER GAME SOLITAIRE
Project 83: XOS TIRES


Information Technology and Communication
Project 84: SOFTWARE DEVELOPMENT PLANNING
Project 85: SOFTWARE PROJECTS PORTFOLIO
Project 86: SOFTWARE DEVELOPMENT VENDOR MANAGEMENT
Project 87: IT OUTSOURCING
Project 88: ON-LINE TRADING
Project 89: TRADE PROCESSING
Project 90: INCIDENTS MANAGEMENT
Project 91: BANDWIDTH PLANNING
Project 92: WIRELESS NETWORK DESIGN


Global and Environmental
Project 93: GLOBAL OIL PRODUCTION
Project 94: RENEWAL ENERGY
Project 95: SAVE THE FORESTS
Project 96: AGROFORESTRY
Project 97: FISHING STOCK
Project 98: POTABLE WATER
Project 99: SUSTAINABLE PLANET
Project 100: PANDEMIC CONTROL
Project 101: MALARIA


Tool 21: NEW EXERCISE
Tool 22: NEW PROJECT
Tool 23: MODELING TOOLS
Tool 24: LEARN
Tool 25: USEFUL MACROS
Tool 26: SPIN WHEEL
Tool 27: LIMITED TRIAL
Tool 28: TEXT DATA IMPORT AND EXPORT
Tool 29: RENAME FILES
Tool 30: DATATABLESOLVE


Tip 44: Excellent spreadsheet projects
Tip 45: Not system development
Tip 46: Not a one-off effort
Tip 47: Confidentiality constraint
Tip 48: Data security concerns
Tip 49: Short names
Tip 50: Project files
Tip 51: Learning from others


Appendix A1: EXCEL 2007 FEATURES AND FUNCTIONS
Note 1: SPREADSHEET WINDOW
Note 2: COMPUTATION
Note 3: CELL REFERENCING
Note 4: FORMATTING
Note 5: DATA VALIDATION AND LOOKUP
Note 6: RANDOM NUMBERS AND RESAMPLING
Note 7: AUTOMATIC TABULATION
Note 8: CHART
Note 9: DATE, TIME, AND TEXT
Note 10: LOGICAL FORMULA AND FUNCTION
Note 11: DATA MANAGEMENT
Note 12: TARGET AND OPTIMIZATION
Note 13: DATA ANALYSIS
Note 14: EXCEL 2007 vs EXCEL 2003
Note 15: MIGRATING TO OPENOFFICE.ORG CALC


Appendix A2: EXCEL 2003 FEATURES AND FUNCTIONS
Note 1: SPREADSHEET WINDOW
Note 2: COMPUTATION
Note 3: CELL REFERENCING
Note 4: FORMATTING
Note 5: DATA VALIDATION AND LOOKUP
Note 6: RANDOM NUMBERS AND RESAMPLING
Note 7: AUTOMATIC TABULATION
Note 8: CHART
Note 9: DATE, TIME, AND TEXT
Note 10: LOGICAL FORMULA AND FUNCTION
Note 11: DATA MANAGEMENT
Note 12: TARGET AND OPTIMIZATION
Note 13: DATA ANALYSIS
Note 14: EXCEL 2003 vs EXCEL 2007
Note 15: MIGRATING TO OPENOFFICE.ORG CALC


Appendix A3: CALC FEATURES AND FUNCTIONS
Note 1: SPREADSHEET WINDOW
Note 2: COMPUTATION
Note 3: CELL REFERENCING
Note 4: FORMATTING
Note 5: DATA VALIDATION AND LOOKUP
Note 6: RANDOM NUMBERS AND RESAMPLING
Note 7: AUTOMATIC TABULATION
Note 8: CHART
Note 9: DATE, TIME, AND TEXT
Note 10: LOGICAL FORMULA AND FUNCTION
Note 11: DATA MANAGEMENT
Note 12: TARGET AND OPTIMIZATION
Note 13: DATA ANALYSIS
Note 14: EXCEL 2007 vs EXCEL 2003
Note 15: MIGRATING FROM MICROSOFT EXCEL


Appendix B1: VISUAL BASIC FOR APPLICATION
Note 16: MACRO RECORDING
Note 17: VISUAL BASIC GRAMMAR
Note 18: MORE PROGRAMMING
Note 19: SOLVER AND ADD-INS
Note 20: AUTOMATIC PROCEDURES AND EVENTS
Note 21: RUN-TIME ERROR HANDLING
Note 22: SPREADSHEET PROGRAMMING APPROACH


Appendix B2: OPENOFFICE.ORG BASIC
Note 16: MACRO RECORDING
Note 17: OOo BASIC GRAMMAR
Note 18: MORE PROGRAMMING
Note 19: EXTENSION AND ADD-INS
Note 20: AUTOMATIC PROCEDURES AND EVENTS
Note 21: RUN-TIME ERROR HANDLING
Note 22: SPREADSHEET PROGRAMMING APPROACH


Index

About the Author

Thin-Yin Leong

 

Dr Leong is Practice Associate Professor of Information Systems at the Singapore Management University. A/P Leong was formerly a faculty member of the National University of Singapore's Business School. Over the years, he had taught courses in operations management, logistics and supply chain management, and business spreadsheet modeling in undergraduate, MBA and executive programs. In between academia, A/P Leong had spent more than a decade's in the industry and was last Senior Vice-President (Information Technology) with the port operator that runs the world's busiest container terminal, where he spearheaded its operations improvements, and systems and technology development. He also provided many models which supported management decisions in strategy, capacity development, operational planning and scheduling, and financial and tax issues. One of his current pet teaching and research interests is spreadsheet modeling and analysis, leveraging his past experience for research as well as industry applications. Even while in academia, he had been active in business consulting and had worked with many multinational corporations and local companies, particularly those in the electronics, petrochemical, medical, hospitality, banking, cargo logistics, passenger transport, and the government sectors.

 



Michelle Cheong

 

Dr Cheong is Practice Assistant Professor of Information Systems at the Singapore Management University and Director of the School's Masters and Professional Programs. Cheong had worked eight years in the industry developing and implementing enterprise IT solutions for manufacturing companies. She had led teams to develop complex IT systems which were implemented enterprise-wide covering business functions from sales to engineering, inventory management, planning, production, and distribution. She consulted for precision engineering, printing, aerospace and brewery companies in business process re-engineering for IT solutions implementation. During her PhD studies, she taught, as a teaching assistant, a course in analytical methods and flow models. Currently, she teaches the undergraduate spreadsheet modeling course together with the first author. As Director of the School's Masters and Professional Programs, she worked closely with the Dean's office to conceptualize, design and develop a new master's program, which was successfully launched in 2007.

 

Out of stock
This title is currently unavailable for purchase.
  • Free HK shipping over HK$1,000
  • International shipping to 35+ countries
Save