|
Excel 2007
Practice Test Instructions
Problem: During
this Excel Practice Test you will work with a spreadsheet that breaks
down the advertising expenses for Your Last Name Hardware Outlet. They
have an annual advertising budget which totaled just over $40,000 last
year with a projection of $52,000 this year. They advertise in a
variety of ways including newspaper, radio, television, and internet.
In Part 1 of the
practice test you will add data and perform calculations with functions
and formulas. In Part 2 you will follow several steps to format the
two spreadsheets. In Part 3 you will construct a pie chart that shows
the breakdown of the advertising expenses for 2007. You need Excel 2007
to complete the following. You can modify the instructions for the
older versions of Excel and for QuattroPro.
Part 1 –
Calculating the Cost of a Newspaper Ad
An original draft of
an Excel workbook has been created for you with some of the advertising
data for Your Last Name Hardware Outlet. Save this file to your disk.
Open the workbook. Make the following additions and corrections:
1. Insert a row
above the Third Page Row (A7) with the following information:
Size of Ad: Quarter Page
Column Inches: 5.833
Row Inches: 6
Number of Runs Per Month: 12
Color: No
2. Insert a column with the title
Cost for Total Inches to the left of the column titled Color
(between columns F and G).
3. Format the column headings as
follows:
·
Apply the Heading 3 cell
style
·
Change to center alignment
·
Word wrap the multiword
column titles so that they display on two lines. To do this, manually
widen these columns and use Alt + Enter to start the second line.
4. Assign the NOW function to Cell
A3. Format it to the 3/14/2001 style and left align the date.
5. Set up formulas to perform the
calculations in Columns D, F, G and I. Set up the formulas for the
first ad size in Row 5 and then copy them down for each of the other ad
sizes. Remember to use an absolute cell reference when referring to
the ad rates provided at the bottom of the spreadsheet in Cells B16,
B17, and B18. Below you will find additional instructions for each
formula.
a. Total Inches (Cell D5):
multiply the column inches by the row inches
b. Inches Per Month (Cell F5):
multiply the Total Inches by the Number of Runs per Month
c.
Cost for Total Inches (Cell
G5): Enter an IF statement where if Inches Per Month exceeds 200 the
rate of $4.10 is multiplied by the inches per month. Otherwise, the
rate of $4.40 is multiplied by the inches per month. Use cell addresses
in the IF statement so that if the rate changes in the future the
formula will not need to be corrected.
NOTE: On-Campus Students. If you have
trouble constructing IF statements, please view the four videos
available on DriveM:\1700\2007\Excel Movies located in lab 311 in
Elizabeth Hall on the main campus.
d. Total Price (Cell I5): Enter
an IF statement where an additional $195 (Cell B18) is added to the
price if the ad is in color. Otherwise, the price remains the amount
displayed in Cell G5.
HINT: If the logical test of the
IF statement refers to a word in a cell, quotation marks
“ “
must be placed around the word.
6. Use functions to calculate the
following summary figures:
Cell B12: Lowest Total Price
Cell B13: Highest Total Price
Cell B14: Average Total Price
7. Set up Conditional Formatting on
the total price column values where totals over $1,000 are displayed in
Light Red fill with Red Bold text. (This is a preset condition—Under
Highlight Cell Rules, select greater than, key in 1,000 and then select
the preset color).
8. Switch to Sheet 2. In Column D
calculate the percent change for each type of advertising from 2006 to
2007. Calculate the change for newspaper ads and then copy the formula
down to the other types of advertising. To calculate the percent change
for newspaper ads find the difference between the two years and then
divide that amount by the 2006 amount.
9. In Cell E5 of Sheet 2 set up an IF
statement that displays the word “increase” if the percent change in
newspaper costs is above zero. Display the word “decrease” if the
percent change in newspaper costs is below zero. Copy the If statement
through cell E8.
10. Use a function to calculate the
total advertising costs for 2006 and 2007 in Cells B9 and C9. Notice the
green triangle that displays in the top left corner Cells B9 and C9.
Click on each cell. A Trace Error Option Button displays because the
formula in that cell refers to a range that has additional numbers
adjacent to it (the year). Click the button arrow and select Ignore
Error for each cell.
11. Save the workbook as an Excel 2007
file with the xlsx extension. Name it [ Last Name First Name] Excel
PracPro.xlsx.
Part 2 – Format
Worksheet
1. Make Sheet 1 active. Change the
theme of the workbook to the Origin theme.
2. Format the title and subtitle
cells as follows:
·
Apply the Title cell style
to cells A1 and A2.
·
Change the font size for A1
to 26 points.
·
Merge and center the title
and subtitle across Columns A through I.
·
Change the background color
of Cells A1 to I2 to Ice Blue, Background 2, Darker 50%.
·
Change the color of the
text to Indigo, Text 2, Lighter 80%.
3. Apply the following number
formatting:
·
Figures in inches: Number
Style with three decimal places
·
Dollar figures: Currency
style with two decimal places, floating dollar sign
4. Apply the following other
miscellaneous formatting
·
Center align the entries in
Columns B, C, E, and H
·
Widen Column A to 20
characters.
·
Change the height of Row 4
to 40.
·
Autofit cells B4 through
I14.
·
Change the name of Sheet 1
to Newspaper and Sheet 2 to Chart
5. Take the
following formatting steps on the Chart Sheet:
·
Use the Format Painter to
copy the formatting of the title and subtitle from the Newspaper Sheet
to the Chart Sheet. Merge and center titles across cells A1:H1 and
A2:H2.
·
Change the number style in
cells D5:D8 to percent with two decimals.
·
Shift the data over two
columns. Select cells A4:E9 and drag them over to cells C4:G9.
·
Check for any error
messages after moving the data.
·
Apply Heading 3 to column
headings
·
Apply Total style to C9:G9
·
Make sure all worksheet
data is best fit.
Take any additional
formatting steps to make your Chart sheet match the key.
Part 3 – Constructing a Pie Chart of
Advertising Costs
1. Make the sheet you named Chart
active.
2. Create a 3D pie chart illustrating
the breakdown of advertising costs for 2007. Move the chart so that it
covers the range of cells B12:G30.
3. Apply the Style 2 chart style.
Change the color of the Television slice to a solid fill, Brown,
Accent 5, Darker 25%.
4. Insert a center overlay title with
the words Breakdown of 2007 Advertising Costs
5. Delete the legend and display
category and percent data labels. Position the labels outside end.
6. Explode the largest slice 15%.
7. Format the Chart Area setting up
no fill and no border. You will need to select Chart Area from the
Shortcut Menu to make these changes. Or right click and choose format
chart area.
NOTE:
Practice creating and formatting
other types of charts to prepare for the production test. For example,
create and format a clustered bar chart.
Final Steps
1. Insert your name, course number,
and instructor’s name as a centered header to both the Newspaper and the
Chart worksheets. Change view back to normal view.
2. Practice printing. Here are the printing steps you will take on
the real production test:
Save the final version of the workbook as an Excel 2007 file with the
xlsx extension. Then print the two worksheets in landscape format
scaled to fit on one page. Go into Page Setup under the Page Layout Tab
to change orientation and scaling.
Print a second version of the Salespersons’ Earnings worksheet
displaying the formulas. Keep the orientation at landscape but scale to
fit on two pages wide. To display the formulas press CTRL +
Accent Mark (`). The Accent Mark is above the Tab key on your
keyboard. Widen columns so that the entire formula displays. (Be sure
to adjust the columns back to best fit after printing.)
|
