Microsoft Office/Excel Basic Exam

From Wikibooks, open books for an open world
Jump to: navigation, search

Practice[edit]

VOCABULARY[edit]

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]

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[edit]

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 lables on the wedges for percents

EXAM[edit]

VOCABULARY[edit]

http://www.yacapaca.com

Excel Basics Test

PROJECT 1[edit]

College Admission Requirements Instructions[edit]

  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[edit]

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[edit]

Create a Projected Profit Spreadsheet - Instructions[edit]

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[edit]

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.