Next Generation Excel: Modeling In Excel For Analysts And MBAs (For MS Windows And Mac OS), 2nd Edition

Chapter 19

Multi-Page Budgets—Going to the Third Dimension

This chapter will be using Excel with more than just rows and columns. The objective is to use sheets, in addition to rows and columns, in the same workbook.

So far, most spreadsheets in this book used models with rows and columns only. You are now going to utilize the third dimension in the model, namely sheets. You may want to prepare Payroll reports for four or five weeks on separate sheets and compile the results (going to the third dimension) into the last sheet for a summary. Other possible applications include summing four quarters’ sheets into an annual report, inventory reports of different locations consolidated into a corporate total inventory report, or a summary budget for a number of departments.

The first part of this chapter illustrates the preparation of the multidimensional Excel workbook. What you want to do is make all the sheets in the workbook identical in format. You will also be inserting the same basic information on all the sheets. After these preparations, you will finally compile the results of all the sheets in the summary sheet.

A PAYROLL EXAMPLE

This example demonstrates how to set up a monthly payroll report for five weeks on five separate sheets. After you have the data for the five weeks, you are going to sum the data into the totals sheet. First, you are going to format six sheets, one for each week and one for the totals, in an Excel workbook. It is like preparing a template.

If you open ...