Microsoft Excel 2007 Chapter 3 – Lab Test B
Creating an Advertising Expense and Sales Revenue Analysis Worksheet
Purpose: To demonstrate the ability to plan a worksheet, create a worksheet, modify a worksheet, create a 3D pie chart,
and use the Goal Seek command.
Problem: You are the owner of a small business. You want to analyze your advertising expenses compared to your sales
revenue for the past six months. Using the data in Table E3B-1, compute the total advertising expenses for the six months
and for each month. Compute a grand total of expenses. For the three newspapers, combine the expenses and compute the
percent this represents of the total monthly revenue. Then compute the total percent of advertising expenses for each of
the other three media.
Instructions: Using techniques developed in the past three chapters, create the worksheet using the sample data in Table
E3B-1. Your solution should be similar to that presented in Figures E3B-1 and E3B-2. Submit the following to your
1. A description of the problem, including the purpose of the worksheet, a statement outlining the results, the required
data, and calculations.
2. A handwritten design of the worksheet.
3. In the range B18:H22 use the =IF function to print the message, Insignificant, for any percent less than 3%.
Otherwise, print the percent.
4. Name the Worksheet Advertising. Save the workbook as Lab Test B – Excel Chapter 3.
4. A printed copy of the worksheet.
5. A 3D pie chart similar to that shown in Figure E3B-2 showing the distribution of advertising expenses for the past
six months. Change the color of the highest percentage to a bright color. This color will depend on the colors of
other slices. Insert text boxes and point out the highest and lowest expenditures. Name the chartsheet Expenses
Chart and move it after the Advertising worksheet.
6. A printed copy of the formulas in the worksheet and a printed copy of the chart.
7. A printed copy of the worksheet where you used the Goal Seek command to determine the effect on total
percent of advertising over revenue when raising the Times expenditures for June to 6000.
8. A printed copy of the worksheet where you used the Goal Seek command to determine the impact on percent of
advertising over revenue when doubling the billboard expenditures for January.
Advertising Expenses and Revenue for Past Six Months
January February March April May June
Times 5000 5000 5000 5000 5000 5000
Tribune 700 700 950 700 1200 950
Blogs 4500 4500 5000 4500 5500 5000
Radio 6550 750 6700 7200 8250 8000
Cable TV 5500 5000 750 5500 7000 5750
WWW 3200 3300 3250 3400 3450 3400
Billboards 4000 4000 4000 4000 4000 4000
Revenue 158000 159350 159000 161000 161550 163800
Table E3B - 1