Store Plan by Product Category
	Projections for This Year
	Sales Last Year	Budgeted Sales	Sales Growth	Profit Percent	Budgeted Profit Dollars
Lumber	$7,000,000	$7,000,000	0.00%	35.00%	$2,450,000
Plumbing	$5,600,000	$6,160,000	10.00%	35.00%	$2,156,000
Electrical	$4,900,000	$5,145,000	5.00%	25.00%	$1,286,250
Seasonal	$3,500,000	$3,780,000	8.00%	50.00%	$1,890,000
Totals	$21,000,000	$31,120,000	48.19%	24.40%	$7,593,250

The purpose of this exercise is to conduct a data audit to evaluate the integrity of the mathematical outputs that have been added to a worksheet. Read the scenario below, then open the Excel workbook related to this exercise. Use the AnswerSheet worksheet for any written responses required for this exercise.

Scenario

You are the manager of a large do-it-yourself hardware store that is part of a national retail chain. Your assistant manager has constructed a financial plan by product category for the upcoming year. The Financial Plan worksheet contains several mathematical outputs used to calculate the expected sales and profit for the company. The following is a list of key elements and calculations used on this worksheet:

Cells shaded in yellow are intended for data entry values. For example, last year sales results in column B are typed into the cells. Also, the expected growth rates in column D and profit percentages in column E are also typed into the cells. These values fluctuate from year to year, and the assistant manager intends to create a few scenarios for the budget by changing the growth rates and expected profit percentages for each product category.

Table 4.4contains a list of the formulas that should have been used to produce the outputs on the Financial Plan worksheet.

Table 4.4 Formulas for Financial Plan

Purpose	Formula	Location
Budgeted Profit Dollars	Budgeted Sales x Profit Percent	F4:F7
Budgeted Sales	Sales Last Year x (1 + Sales Growth)	C4:C7
Total Profit Growth	(Total Budgeted Profit Dollars Total Budgeted Sales)	E8
Total Sales Growth	(Total Budgeted Sales Total Sales Last Year) Total Sales Last Year	D8

Assignment

Use the Show Formulas command to audit the formulas on the worksheet. Compare the formulas on the worksheet to the formulas shown on Table 4.4. Record your observations on the AnswerSheet worksheet.

The assistant manager intends to use the Financial Plan worksheet to create a few scenarios for the budgeted sales and profit dollars. Change a few values in the Profit Percent column and record your observations on the AnswerSheet worksheet.

Look at each value in the Totals row (row 8) on the Financial Plan worksheet. Are there any values that do not make sense? Record your observations on the AnswerSheet worksheet.

Given the information provided for this exercise, make any necessary corrections to improve the integrity of the Financial Plan worksheet.

Use this worksheet to answer any written questions for this exercise.
answer in the merged blank cell next to each question number.
Question	Response
1
2
3
4