Principles of Financial Modelling

CHAPTER 6Designing the Workbook Structure

INTRODUCTION

This chapter discusses issues relating to the design of the overall workbook structure. These include the use of linked workbooks, the number of worksheets, the role of each worksheet and the data structures used. It is probably fair to say that insufficient attention is generally paid to such issues. We present a set of generic base practice structures in terms of the placement of data and calculations, as well as tools and techniques to select or consolidate data that is used across multiple worksheets.

Designing Workbook Models with Multiple Worksheets

This section presents some general principles relating to overall model structure, especially the use of linked workbooks, and the role of each worksheet in models with multiple worksheets.

Linked Workbooks

In principle, models should be self-contained within a single workbook, and not have links to other workbooks. The main reason is to avoid potential errors that can otherwise easily arise, yet be hard to detect:

• If structural changes are made to a source workbook (e.g. the addition/deletion of rows or columns, renaming of the workbook or of a worksheet) when the destination workbook is not open, then such changes will generally not be reflected in the formulae in the (closed) destination workbook. For example, the link may be altered to refer to last month's oil price rather than this month's. As a result, one cannot know a priori whether the linked cells in a ...