# Microsoft Office/Excel Basic Exam

## Contents

## 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).

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

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

## EXAM[edit]

### VOCABULARY[edit]

Excel Basics Test

### PROJECT 1[edit]

#### College Admission Requirements Instructions[edit]

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

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:

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]

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. |