Indiana University Northwest A106 Dorin (DOC)

Document Sample

```					                                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
instructor.

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
Figure E3B-1
Figure E3B-2

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 8 posted: 12/19/2011 language: pages: 4