Calculating Data in Worksheets

About calculations in worksheets

You can use worksheets to perform calculations on data in your database. The following sections describe the types of calculations that you can perform.

Growing data

You can grow data in a worksheet by an amount or percentage over a range of cells. The first data value in the range remains unchanged and is used as the base value upon which the calculation is performed. Each subsequent value in the range grows by the specified amount or percentage as compared to the base value.

Example: Growing data

Suppose that in each month of the last quarter of 2000, Volume Sales for Product A are reforecast to be 5% higher than the month before. By orienting a worksheet to reflect 2000 forecast data with months in the across position, you could select three cells representing Product A Volume Sales for October through December.

If you grew the data values across columns by 5%:

• October's values would remain unchanged

• November's values would be 5% higher than October's

• December's values would be 5% higher than November's

Increasing data

You can increase data in a worksheet by a constant amount or percentage. You can increase values in individual cells or over a range of cells spanning rows, columns, or both.

Example: Increasing data

Suppose that, for each month of the fourth quarter of 2000, Volume Sales for Product A are reforecast to be 5% higher than the corresponding figures in the previous forecast. By orienting a worksheet to reflect 2000 forecast data with months in the across position, you could select the three cells representing Volume Sales for Product A for November through December. You could then increase each of these values by 5%. Each new value would, therefore, be 5% higher than its original value.

Spreading data

You can use the Spread Data tool to spread data in a worksheet from a parent value in a hierarchy to its descendants. There are three different ways to spread data:

• Evenly, so that each descendant receives an equivalent amount

• Proportionally, so the descendants retain their original relationships to the parent

• Based on the proportions in another dimension value

You can spread data either down columns or across rows in a worksheet.

Example 1: Spreading data evenly

Suppose that Expenses for the fourth quarter of 2000 are initially forecast to be $60,000 and is then reforecast to be $120,000. Suppose, too, that you want to determine the effect of the reforecast on the figures for each month in the quarter, and you want the change to be evenly spread to each month. You could use an even spreading method to generate the figures shown in italics.

	Oct 00	Nov 00	Dec 00	Q4 00
Expenses (forecast 1)	$10,000	$20,000	$30,000	$60,000
Expenses (forecast 2)	$40,000	$40,000	$40,000	$120,000

Example 2: Spreading data proportionally

Suppose that Expenses for the fourth quarter of 2000 are reforecast from $60,000 to $120,000. Suppose, too, that you want to determine the effect of the reforecast on the figure for each month in the quarter, and that you want each month's figure to retain its original proportion to the total for the quarter. You could use a proportional spreading method to generate the figures shown in italics.

	Oct 00	Nov 00	Dec 00	Q4 00
Expenses (forecast 1)	$10,000	$20,000	$30,000	$60,000
Expenses (forecast 2)	$20,000	$40,000	$60,000	$120,000

Example 3: Spreading data based on another dimension value

Suppose that Expenses for the fourth quarter of 2000 are reforecast to be $120,000 and that you want to determine the effect of the reforecast on the figures for each month in the quarter so that monthly figures adopt the proportions in another dimension value (in this case, Sales). You could use the spread data tool to generate the figures shown in italics.

	Oct 00	Nov 00	Dec 00	Q4 00
Sales	$60,000	$90,000	$30,000	$180,000
Expenses	$40,000	$60,000	$20,000	$120,000

Example: Spreading data to visible elements only

In this example:

• Expenses for the fourth quarter of 2000 are forecast to be $60,000

• You want to determine the effect of the forecast on the figures for each month in the quarter

• You want the change to be evenly spread to each month

In the worksheet, the months of Nov 00 and Dec 00 are visible, but Oct 00 is not. In the following scenario, there is no value for October, so the $60,000 is distributed evenly between Nov 00 and Dec 00.

	Oct 00	Nov 00	Dec 00	Q4 00
Expenses (forecast 1)		$30,000	$30,000	$60,000

In the next scenario, Oct 00 has a value of $10,000. Spread deducts this value from the $60,000 leaving $50,000 to spread evenly between Nov 00 and Dec 00.

	Oct 00	Nov 00	Dec 00	Q4 00
Expenses (forecast 1)		$30,000	$30,000	$60,000
Expenses (forecast 2)	$10,000	$25,000	$25,000	$60,000

Related information

For more information about calculating data in worksheets, search for the following topics in the Financial Analyzer Help system:

"Growing Worksheet Data"

"Increasing Worksheet Data"

"Spreading Worksheet Data"