Access Data Analysis Cookbook

Chapter 9. Business and Finance Problems

The need to solve business and finance problems is common to many Access users. While you can always export your data to Excel for analysis, it might be easier for you to find ways to solve these problems inside Access. Fortunately, using the capabilities found in the Access database engine and the VBA scripting language, you can tackle nearly any business or finance task.

In this chapter, you’ll learn how to solve a number of common problems, such as computing return on investment, straight-line depreciation, accelerated depreciation, interest, and moving averages. You’ll also learn how to use Access’ PivotTables and PivotCharts, which will help you decode some of the hidden information in your data.

Calculating Weighted Averages

Problem

I want to calculate a weighted average for a series of values.

Solution

You can use a SELECT statement like this to compute a weighted average:

	SELECT Sum(Value * Weight) / Sum(Weight) AS WeightedAverage
	FROM [Table9-1];

This statement will return the value in Figure 9-1 for the data found in Figure 9-2.

Discussion

To compute a weighted average, take the sum of the products of the values and the weights, and then divide the result by the sum of the weights. That is:

	                   Σ Vi * Wi
	Weighted Average = --------
	                     Σ Wi

where Vi represents the i^th value and Wi represents the i^th weight.

Figure 9-1. The computed weighted average ...