Modelo de Análisis

Questions answered in this chapter:

■ Is there an easy way to setup a multiple-worksheet workbook in which each worksheet has a similar structure? Can I easily create formulas that involve cells in multiple worksheets?

■ In many business situations you have to set up workbooks in which each worksheet has a similar format or structure. Here are some examples:

■ You want to track your company’s sales in each region of the country in a separate worksheet and then summarize total sales in a summary worksheet.

■ You want to track your company’s sales for each month in a separate worksheet and then summarize year-to-date sales in a summary worksheet.

In this chapter, you will learn how to set up workbooks in which the individual worksheets have a similar structure. In this chapter I also discuss three-dimensional formulas, which allow you to easily write formulas to perform calculations on cells in multiple worksheets.

Answers to This Chapter’s Questions

Is there an easy way to setup a multiple-worksheet workbook in which each worksheet has a similar structure? Can I easily create formulas that involve cells in multiple worksheets?

Let’s suppose you want to set up a workbook that contains a separate worksheet to track sales in each region (East, South, Midwest and West) of the U.S. You also want to summa-rize total sales in a summary worksheet. In each worksheet you want to track your prod-uct’s price, unit cost, and units sold as well as your fixed cost and profits. In the summary worksheet you want to track just total profit and units sold. You want your individual region worksheets to look like Figure 15-1.

FIGURE 15-1 Sales in the East.

To set up this structure, you enter in cell C3 of each worksheet the product price, in cell C4 the unit cost, in cell C5 the units sold, and in cell C6 the fixed cost. Then, in cell C7, you would compute the East region profit with the formula (C3-C4)*C5-C6. Of course, you want the same structure in the worksheets for the other regions. It turns out you need to enter the headings and formula in only one worksheet, and then Excel will automatically copy them to the other region’s worksheets.

To begin, open a blank workbook, which by default contains three worksheets. By clicking the Insert Worksheet icon to the right of the last-named worksheet (or by pressing

Shift+F11), insert two new worksheets so that your workbook contains five worksheets. Name the first four worksheets East, South, Midwest, and West. Name the last worksheet Summary.

All sales will be totaled in the Summary worksheet. (By the way, if you select Options on the File tab and then choose General, you can change the number of worksheets included in a workbook by default.)

To set up the regional worksheets, select the first worksheet (East) and then hold down the Shift key and select the last individual worksheet (West). Now, whatever you enter in the East worksheet is duplicated in the other regional worksheets. Simply type Price in cell B3, Unit Cost in cell B4, Units Sold in cell B5, Fixed Cost in cell B6, and Profit in cell B7. Finally, enter the formula (C3-C4)*C5-C6 in cell C7. Now click the last sheet to leave this data entry procedure. You will see that each regional worksheet has the same headings in Column B and the correct Profit formula in cell C7.

You are now ready to use three-dimensional formulas to compute total units sold and profit.

In cell C5, you will compute total units sold. Remember that you entered units sold for each region in cell C5. Move your cursor to cell C5 of the Summary worksheet where you want to compute total units sold. Type =SUM( and then move your cursor to the first cell you want to total (cell C5 of sheet East). Next, hold down the Shift key and click the last cell you want to total (cell C5 of worksheet West). Lastly, enter a right parentheses in the formula bar, and you will see entered in cell C5 of the Summary sheet the formula SUM(east:west!C5). This formula is an example of a three-dimensional formula. Most Excel formulas operate in two dimensions (rows and columns.) A three-dimensional formula operates in a third dimension:

across worksheets. This formula tells Excel to sum cell C5 in all worksheets, starting with the East worksheet and ending with the West worksheet. Of course, if you wanted to, you could have simply typed this formula in cell C5 of the Summary worksheet. Copying this formula and pasting it to cell C7 of the Summary worksheet computes our company’s total profit.

(See Figure 15-2.)

In Chapter 47, “Consolidating Data,” you will learn how Excel’s Data Consolidate command can be used to summarize data from multiple worksheets or workbooks.

Chapter 15 Three-Dimensional Formulas 119

FIGURE 15-2 Summarizing unit sales and profit.

Problems

1. You own six local coffee shops. The revenues and customer count for each coffee shop are given in the following table.

Shop Revenues Customer Count

1 $8,000 1,950

2 $7,000 1,800

3 $9,000 2,200

4 $8,400 2,000

5 $5,900 1,400

6 $10,100 2,500

Set up a workbook that makes it easy to enter revenue and customer count for each store and create a summary worksheet (using three-dimensional formulas) that computes total weekly revenue and customer count.

121

Chapter 16