Tennis Inc. sells tennis rackets and tennis balls. For the year, company management budgeted the following:

	Tennis Rackets	Tennis balls
Sales Revenue	$1,200,000	$1,600,000
Unit Sales price	$80	$4

At the end of the year, the following actual sales and variances occurred:
Actual sales of Tennis Rackets	22,000	units
Sales Price Variance of Tennis Rackets	$63,000	U
Sales Revenue of Tennis Balls	$1,660,000
Sales Volume Variance of Tennis Balls	$200,000	U

Required: Use cell references and an IF function for the determination of U or F designation. You will need to use the SumIF function when determining total variances and determining the U or F designation.

1. Compute Budgeted sales volume for each product:

Tennis Rackets

Tennis Balls

2. Compute the sales volume variance for tennis rackets.

Sales Volume Variance for tennis rackets

3. Determine the actual sales volume of tennis balls

4. Determine the actual sales price of tennis balls

5. Compute sales price variance for tennis balls.

6. Using the SUMIF determine the total sales variance for all of the products. Remember variances should not show negative numbers so you should use absolute terms where needed. On the designation of U or F for the total you will use the SUMIF that is nested in an IF statement.

Sales Price Variance of Tennis Rackets

Sales Volume Variance for Tennis Rackets

Sales Price Variance of Tennis Balls

Sales Volume Variance for Tennis Balls

Total Revenue variances

7. Explain what conditions might have contributed to the revenue variances? You need to explicitly discuss each of the products and how these variances impacted the total revenue variances.

Answer rating: 100% (QA)

To determine the U or F designation for the total variance we can use the SUMIF function nested in an IF statement IFSUMIFA1A4UB1B4SUMIFA1A4FB1B4UF In ... View the full answer