Microsoft Office/Excel Basic Exam

Practice

VOCABULARY

Use the web site: http://www.scsite.com/ex2007/learn to practice the vocabulary words.

Play the following games for chapters 1, 2, and 3:

• flash cards
• Who Wants to be a computer Genius?
• Wheel of Terms

The Crossword Puzzle for each chapter is extra credit if you can get the whole puzzle filled in.

PROJECT 1

Use the web site: http://lmigateway.coworkforce.com/lmigateway/analyzer/searchAnalyzer.asp?cat=OCC&session=OCCPROJ&subsession=99&time=&geo=&currsubsessavail=&incsource=&blnStart=True to create a spreadsheet that analyzes

• Colorado
• 2008-2018
• Pick 5 -10 occupations (hold down the CTRL key to pick occupations not next to one another in the list).

1. Create a spreadsheet for the employment and openings data for:
   • Employment 2008
   • Employment 2018
   • Use a formula to compute change in employment from 2008 to 2018
   • Openings due to growth
   • Openings due to replacement
   • Use the AutoSum function to create total openings
2. Create a bar or column graph for openings due to growth and replacement for the occupations you have chosen.

PROJECT 2

Pick 5 stocks from the web site: http://www.nasdaq.com/

1. Create a spreadsheet for the following:
   • List the stock's name
   • List the stock's symbol
   • Choose how many stocks you wish to buy (200 - 1000 of each)
   • Give the date from around 4 years ago that you would have purchased these (pick a date from the history)
   • List how much it cost then as the initial cost
   • Compute the initial cost
   • Give the current cost of the stock
   • Compute the current value of the stock
   • Compute the gain or loss
   • Find the percent gain or loss by taking the gain/loss and dividing by the initial cost.
   • Use conditional formatting to change the color on any percent gain/loss that is negative.
   • Add a row at the bottom for totals
   • Add a row at the bottom for average
   • add a row at the bottom for highest
   • add a row at the bottom for lowest
2. Create a Pie Chart for current value of your stocks
   • do NOT have a legend
   • Have a title
   • Use data labels on the wedges for catagories
   • Use data labels on the wedges for percents

EXAM

VOCABULARY

http://www.yacapaca.com

>Excel Basics Test

PROJECT 1

College Admission Requirements Instructions

1. Visit http://www.collegeincolorado.org
   • Log in
   • Go to the PLAN tab
   • Click on the link for Higher Education Opportunities and Admission Requirements
   • Click on High School Coursework Planner
   • Click on High School Planner Subjects
   • Click on Add Higher Education Institution (add at least four colleges or universities)
   • Click on Summary
2. Create a Spreadsheet of the data for Required and Recommended courses
3. Format the spread sheet professionally, not necessarily how it is shown here
4. Fill in the Total Row
5. Fill in the Average Row
6. Fill in the Total Column
7. Create a Column Chart for the Schools Required and Recommended courses
   • 1 column chart
8. Place the Column Chart on the same sheet as the table of data
9. Create a Pie Chart for each school showing Required Courses
   • 4 pie charts
   • Do not include column A
   • Make sure Row 3 has no merged cells
   • Think about what the pie wedges represent and the value they have.
10. Place the Pie Charts all on one sheet and separate from the table of data - NOT A NEW SHEET

Sample Table for Required Course Analysis

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	College Enrollment Course Requirements
2	Your Name
3		Social Science		English		Math		Science		Foreign Lang.		Other
4		Required	Recommended	Required	Recommended	Required	Recommended	Required	Recommended	Required	Recommended	Required	Recommended	Total
5	School 1
6	School 2
7	School 3
8	School 4
9	Total
10	Average

College Admission Requirements Rubric

admission requirement rubric

Standards	1 Points	2 Points	3 Points	4 Points	5 Points
Format a Spreadsheet Professionally	Missing four or more elements	Missing three elements	Missing two elements	Missing one element	User has taken into account color scheme, cell styles, spacing, and font size and style.
Use of AutoSum Correctly	Major mistakes were made	3 minor mistakes were made	2 minor mistakes were made	1 minor mistake was made	User has used the AutoSum function in the correct cell(s), with the correct range, and with the correct format
Use of Average Function Correctly	Major mistakes were made	3 minor mistakes were made	2 minor mistakes were made	1 minor mistake was made	User has used the Average function in the correct cell(s), with the correct range, and with the correct format
Research Data on the Internet	User needed major help to get data from the web site	User needed assistance three times	User needed assistance twice	User needed assistance on one step	User has found the proper web site, followed the directions to fill in the form correctly to access the data, and can bring up necessary information.
Create Column Chart	Chart is missing four or more items	Chart is missing three items	Chart is missing two items	Chart is missing one item	Column chart contains the proper x-axis labels, y-axis labels, legend, bars, and title
Create Pie Charts	Chart is missing four or more items	Chart is missing three items	Chart is missing two items	Chart is missing one item	Four Pie charts are created with the proper data in the wedges for each one, Data labels were used correctly, a chart title was added.
Make Chart Embedded and Sheet Tab Chart	None of the charts are where they belong	Three charts are not where they belong	Two charts are not where they belong	One chart was not where it belonged	Column chart was embedded with data table, four pie charts are on a separate sheet (not a new sheet)
Print as Two Pages	Many pages were printed - no evidence this person used print preview	There is a fifth page printed	There is a fourth page printed	There is a third page printed	Data table and column chart are printed on one page, four pie charts are printed on one page.

PROJECT 2

Create a Projected Profit Spreadsheet - Instructions

The FBLA is going to purchase a screen printing machine to start a business of creating shirts for the various teams and clubs of the school. They will need to find an organization to put up the initial cost of $8000 for the machine. They proposal will be to pay back this organization the full amount by making payments of 50% of the profit on each batch/sale. Analyze the data to figure out about how long it will take to pay back the loan.

Create the following Table:

Table for Tracking Profits

	A	B	C	D	E	F	G	H
1	FBLA SCREEN PRINTING
2	Meeker High School
3		Year 1	Year 2	Year 3	Year 4	Year 5	...	Total
4	Volleyball
5	Football
6	Golf
7	Cross Country
8	Boy's Basketball
9	Girl's Basketball
10	Wrestling
11	Track
12	Baseball
13	Rec. Center
14	Total Shirts Sold
15	Total Profit
16	Amount Paid Back to Donator
17	Running total Paid Back
18	Total left on Loan
19
20	ASSUMPTIONS
21	Original Cost of Equipment	$8,000.00
22	% Profit back to Donator	50%
23	Markup per Shirt	$5.00
24	Sales Growth	2%

Italic text

1. Fill in the estimated number of shirts each group would sold for year 1.
2. Compute the number of shirts sold for year 2 = prior year *(1+sales growth)
   • Compute the rest of the years sales with the same basic formula
   • You might have to have more than 5 years to pay this off
   • Make sure you look at your absolute and relative references on your formulas
3. Find the total shirts sold for each year
4. Compute the Total Profit = total shirts sold * Markup per shirt
   • Make sure you look at your absolute and relative references on your formulas
5. Compute Amount Paid back to Donator = 50% of Profit
   • Make sure you look at your absolute and relative references on your formulas
6. Compute Running Total Paid Back = Running Total Paid Back previous year plus Amount Paid Back this year
   • You will need to think on your own
   • Logically how much do you have paid back the first year?
   • Logically how can you find out how much is paid back the first two year?
   • What can your formula be so you can use the fill handle?
   • Make sure you look at your absolute and relative references on your formulas
7. Compute Total left on Loan = Original cost of the Equipment - Running Total Paid Back
   • Make sure you look at your absolute and relative references on your formulas
8. Create a Conditional Formatting on the Left on Loan to change the color of font and fill color when the value goes negative.
9. Print the spreadsheet on one page
10. Print the Formula View to one page
11. Adjust the Assumptions to figure out how the loan could be paid off in one less year.
    • You may not change that the equipment costs $8,000.00
12. Print the spreadsheet on one page

Create a Projected Profit Spreadsheet - Rubric

What-If Analysis Rubric

Standards	1 Points	2 Points	3 Points	4 Points	5 Points
Format a Spreadsheet Professionally	Missing four or more elements	Missing three elements	Missing two elements	Missing one element	User has taken into account color scheme, cell styles, spacing, borders, fill colors, and font size and style.
Creates Spreadsheet Correctly	Major mistakes were made	3 minor mistakes were made	2 minor mistakes were made	1 minor mistake was made	Recreates the spreadsheet correctly with no errors
Makes a LOGICAL guess on numbers of shirts sold	Major mistakes were made	3 minor mistakes were made	2 minor mistakes were made	1 minor mistake was made	User makes a logical guess for our high school on the numbers of shirts we could sell.
Uses Sales Growth Correctly to compute future years sales.	User needed major help to get the formula to work	User needed assistance three times	User needed assistance twice	User needed assistance on one step	User can create the formula to compute sales growth and figure future sales for each year accurately using absolute and relative references.
Computes total profit using assumptions	User needed major help to get the formula to work	User needed assistance three times	User needed assistance twice	User needed assistance on one step	User can create the formula to compute total profit for each year accurately using absolute and relative references.
Compute amount paid back to donator	User needed major help to get the formula to work	User needed assistance three times	User needed assistance twice	User needed assistance on one step	User can create the formula to compute amount paid back to donator for each year accurately using absolute and relative references.
Compute the running total paid back	User needed major help to get the formula to work	User needed assistance three times	User needed assistance twice	User needed assistance on one step	User can create the formula to compute running total paid back for each year accurately using absolute and relative references.
Computes the total left on loan	User needed major help to get the formula to work	User needed assistance three times	User needed assistance twice	User needed assistance on one step	User can create the formula to compute the total left on the loan for each year accurately using absolute and relative references.
Creates Conditional Formatting	User needed major help to get the formula to work	User needed assistance three times	User needed assistance twice	User needed assistance on one step	User can create conditional formatting on total left on loan to show negative values as a different color font and fill color.
Uses What-If Analysis	User needed major help to get the assumptions to work	User needed assistance three times	User needed assistance twice	User needed assistance on one step	User can use the assumptions to adjust the data in the spreadsheet to answer questions for projection data.
Prints the spreadsheet	Only printed one page and it is not fit to one page	Missing two and/or and not fit to a page	Missing a page and more than one is not fit to a page	Missing a page, or not fit to one page	Print the original spreadsheet filled in, prints in regular and formula view. Then prints again with the what-if assumptions filled in. A total of three pages should be printed forcing each to fit to one page.