BSM 140 Microsoft Excel Project-Based Final

BSM 140
Microsoft Excel
Project-Based Final

Directions:
Pretend you recently
started a new Party Planning Business from Home. Each of the worksheets in the
Final’s Data File will be reviewing different aspects of your business. Each
section will be having you demonstrate skills learned in class.

UNDERSTANDING Spread Sheets

Understanding Excel Worksheet
For
this first exercise you are comparing different media Sources for Advertising. The
data in the worksheet represents different media outlets and how many people
they reached on specific dates. Follow the steps below:

1.
Click in cell A5 and change the cell name from Qwest Advertising to Northwest
Advertising.
2.
Click in Cell A14 and type Total customers
reached in that cell.
3.
Click in Cell I4 type in Totals.
4.
In Cell C14
create a formula to add up all the customers reached on January 1.
5.
Using Auto fill copy the formula for the total
in the range of D14:H14
6.
In cell I5
use the Auto-sum button to total up the range C5:H5
7.
Using the auto-fill drag the formula to the
rest of the column down to I12.
8.
Using your page layout view type in the center header: Customers Reached by
Company and in the right footer box
type your full name.
9.
Change orientation of the document to
landscape
10.
Using the view tab remove the gridlines to
your document.

Modules 1-4 working with
Formulas and FUNCTIONS, FORMATTING Worksheets and Working with Charts

Modules 1-4 Worksheet
For
this section you are researching locations throughout the state of CA to market
your party planning services. You have chosen some of the wealthiest cities in
the state as they are more likely to want your services and can afford them.
Follow the instructions below to conduct your research on where the best
location to market will be.

11.
In Cell B16
manually create a formula that totals up Malibu’s Party Prices for the years
2003-2011.
12.
Using the auto-fill drag the formula into the
range C16:G16 so there are totals in
that entire row.
13.
In Cell B17,
use a function to find the average for Malibu’s Party Costs for the years
2003-2011. Then copy the function in the Cell Range C17:G17.
14.
Continue to fill in the functions for the Minimum cost per city and Max Cost per city.
15.
Delete the Montecito Column (column C).
16.
In cell A20
Type in the cell Percent of the National
Average. Auto fit the column if necessary.
17. Using Absolute Cell References in cell B20 create a formula that calculates
what Percentage Malibu’s average (B17)
total is of the National Total (J2).
Autofill all the remaining cities (Row
20)
18.
Select the results you just created and format
them to contain a % sign. (example 73%)
19. Create a
3D clustered Column Chart (the first option in menu) using the City Header
names and the Average Costs for each city (hint nonconsecutive selecting). Move the chart so that it is in the
blank area to the right of your data. Adjust the size so that it is large
enough to see all data, but small enough that it isn’t blocking any data.
20. Title the
chart: Average Cost by City. Insert a vertical Axis title: Prices.
21. Change the
Chart Style to Style 9. Change each city’s bar to different colors of your
choice.
22. Click on
cell A1 and Change the font to: Bookman old Style, bold, 16 pt. font and apply
a background fill color of your choice.
23. Select
Range A1: L1 and center and merge
the title so that it covers all of your data.
24. Format
cells with money, to include $ and round to 0 decimal points.
25. Select H2: J2 and format it to Neutral style.
26. Select the
Range B5:F13 apply conditional
formattingà Choose Greater than-à Input 2500 and format it with Green
Fill with dark Green Text.

Modules 3: Analyzing Data Using Formulas
Module 3 Worksheet
For this section you are determining if
individuals are eligible for a raise. Follow the instructions below.

27. In the
range E3:E7 use a function to determine if the individual qualifies for a raise
based upon the following criteria: Yearly Sales are equal to or greater than
$32,000 and an Evaluation Rating equal to or greater than six.

Modules 5 and 6 Working with Tables and Managing WORKBOOK DATA
Modules 5 and 6 / Modules 5
and 6 (2)
For this section you will be altering 2
worksheets that contain information for your 2 most popular party themes.
Format information for easier viewing and create a table so that you may apply
more advanced changes to your data. Follow the instructions below.

28.
In Cell A1 of both sheet tabs module 5 and 6 and module 5 and 6 (2) begin typing the headers across row 1 (this
should fill the range A1:E1): Item| Total
Inventory of Item | Number of Items Used | Cost Per Item | Category of Item.
29.
Auto fit the column width so that you can read all
the headers.
30.
In the Sheet tab MODULES 5 and 6 Change the tab
name to Pirate Party and give it a tab color.
31.
In the sheet tab Modules 5 and 6 (2) change the
name to Circus party and give it a different tab color.
32.
Click into the Pirate Party Sheet and select the
entire data field range A1:E9 and turn it into a table.
33.
Change the format of the table and choose
different colors for your table (pick something pirate-y J )
34.
Click into F1 and type the Header: Included in Party Package
35.
In the new table column apply a data validation
rule list with only Yes or No responses.
36.
Enter the
following list into the range F2:F9 column (1 answer per cell).
1.
Yes
2.
Yes
3.
Yes
4.
No
5.
No
6.
Yes
7.
No
8.
No
37.
With the pirate sheet active click in cell A1. Use
the find and replace feature to find all instances of Pirate and replace it
with the word Buccaneer. Replace all—Press OK.
38.
Group the two-party sheets and add the sheet
name in the center header. Insert your name in the center footer.
39.
In the Pirate sheet click into cell A1, then click
the Sort and filter icon and set up a custom sort. In the Sort by Choose
category of item in A-Z order. Then add a level and sort then by: Cost per item
and sort Largest to smallest.
40.
include a total row at the bottom of the table.
In the new total row in cell B10 click the list arrow and use the SUM function
to add all the Total Inventory of Item column.
41.
While still in the Pirate Party sheet, create a
filter for the inventory that is $15 dollars or more. Copy all information
(fields, total row, etc.) and paste the results below your table in cell A20.
42.
Save. You are finished!

Last Completed Projects