Microsoft Office/Excel Basic Exam
Practice
[edit | edit source]VOCABULARY
[edit | edit source]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
[edit | edit source]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).
- 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
- Create a bar or column graph for openings due to growth and replacement for the occupations you have chosen.
PROJECT 2
[edit | edit source]Pick 5 stocks from the web site: http://www.nasdaq.com/
- 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
- 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
[edit | edit source]VOCABULARY
[edit | edit source]>Excel Basics Test
PROJECT 1
[edit | edit source]College Admission Requirements Instructions
[edit | edit source]- 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
- Create a Spreadsheet of the data for Required and Recommended courses
- Format the spread sheet professionally, not necessarily how it is shown here
- Fill in the Total Row
- Fill in the Average Row
- Fill in the Total Column
- Create a Column Chart for the Schools Required and Recommended courses
- 1 column chart
- Place the Column Chart on the same sheet as the table of data
- 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.
- Place the Pie Charts all on one sheet and separate from the table of data - NOT A NEW SHEET
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
[edit | edit source]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
[edit | edit source]Create a Projected Profit Spreadsheet - Instructions
[edit | edit source]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:
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
- Fill in the estimated number of shirts each group would sold for year 1.
- 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
- Find the total shirts sold for each year
- Compute the Total Profit = total shirts sold * Markup per shirt
- Make sure you look at your absolute and relative references on your formulas
- Compute Amount Paid back to Donator = 50% of Profit
- Make sure you look at your absolute and relative references on your formulas
- 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
- 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
- Create a Conditional Formatting on the Left on Loan to change the color of font and fill color when the value goes negative.
- Print the spreadsheet on one page
- Print the Formula View to one page
- 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
- Print the spreadsheet on one page
Create a Projected Profit Spreadsheet - Rubric
[edit | edit source]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. |