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). 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 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 categories Use data labels on the wedges for percents EXAM VOCABULARY http://www.yacapaca.com >Excel Basics Test PROJECT 1 College Admission Requirements Instructions 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 + Sample Table for Required Course AnalysisABCDEFGHIJKLMN1 College Enrollment Course Requirements 2 Your Name3 Social ScienceEnglishMathScienceForeign Lang.Other4 RequiredRecommendedRequiredRecommendedRequiredRecommendedRequiredRecommendedRequiredRecommendedRequiredRecommendedTotal5 School 16 School 27 School 38 School 49 Total10 Average College Admission Requirements Rubric + admission requirement rubricStandards 1 Points2 Points3 Points4 Points5 PointsFormat a Spreadsheet ProfessionallyMissing four or more elementsMissing three elementsMissing two elementsMissing one elementUser has taken into account color scheme, cell styles, spacing, and font size and style. Use of AutoSum CorrectlyMajor mistakes were made3 minor mistakes were made2 minor mistakes were made1 minor mistake was madeUser has used the AutoSum function in the correct cell(s), with the correct range, and with the correct formatUse of Average Function CorrectlyMajor mistakes were made3 minor mistakes were made2 minor mistakes were made1 minor mistake was madeUser has used the Average function in the correct cell(s), with the correct range, and with the correct formatResearch Data on the InternetUser needed major help to get data from the web siteUser needed assistance three timesUser needed assistance twiceUser needed assistance on one stepUser 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 ChartChart is missing four or more itemsChart is missing three itemsChart is missing two itemsChart is missing one itemColumn chart contains the proper x-axis labels, y-axis labels, legend, bars, and titleCreate Pie ChartsChart is missing four or more itemsChart is missing three itemsChart is missing two itemsChart is missing one itemFour 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 belongThree charts are not where they belongTwo charts are not where they belongOne chart was not where it belongedColumn chart was embedded with data table, four pie charts are on a separate sheet (not a new sheet)Print as Two PagesMany pages were printed - no evidence this person used print previewThere is a fifth page printedThere is a fourth page printedThere is a third page printedData 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 ABCDEFGH1FBLA SCREEN PRINTING2Meeker High School3Year 1Year 2Year 3Year 4Year 5... Total4Volleyball5Football6Golf7Cross Country8Boy's Basketball9Girl's Basketball10Wrestling11Track12Baseball13Rec. Center14Total Shirts Sold15Total Profit16Amount Paid Back to Donator17Running total Paid Back18Total left on Loan1920ASSUMPTIONS21Original Cost of Equipment$8,000.0022% Profit back to Donator50%23Markup per Shirt$5.0024Sales Growth2%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 + What-If Analysis RubricStandards 1 Points2 Points3 Points4 Points5 PointsFormat a Spreadsheet ProfessionallyMissing four or more elementsMissing three elementsMissing two elementsMissing one elementUser has taken into account color scheme, cell styles, spacing, borders, fill colors, and font size and style. Creates Spreadsheet CorrectlyMajor mistakes were made3 minor mistakes were made2 minor mistakes were made1 minor mistake was madeRecreates the spreadsheet correctly with no errorsMakes a LOGICAL guess on numbers of shirts soldMajor mistakes were made3 minor mistakes were made2 minor mistakes were made1 minor mistake was madeUser 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 workUser needed assistance three timesUser needed assistance twiceUser needed assistance on one stepUser 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 workUser needed assistance three timesUser needed assistance twiceUser needed assistance on one stepUser 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 workUser needed assistance three timesUser needed assistance twiceUser needed assistance on one stepUser 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 backUser needed major help to get the formula to workUser needed assistance three timesUser needed assistance twiceUser needed assistance on one stepUser can create the formula to compute running total paid back for each year accurately using absolute and relative references. Computes the total left on loanUser needed major help to get the formula to workUser needed assistance three timesUser needed assistance twiceUser needed assistance on one stepUser can create the formula to compute the total left on the loan for each year accurately using absolute and relative references. Creates Conditional FormattingUser needed major help to get the formula to workUser needed assistance three timesUser needed assistance twiceUser needed assistance on one stepUser can create conditional formatting on total left on loan to show negative values as a different color font and fill color. Uses What-If AnalysisUser needed major help to get the assumptions to workUser needed assistance three timesUser needed assistance twiceUser needed assistance on one stepUser can use the assumptions to adjust the data in the spreadsheet to answer questions for projection data. Prints the spreadsheetOnly printed one page and it is not fit to one pageMissing two and/or and not fit to a pageMissing a page and more than one is not fit to a pageMissing a page, or not fit to one pagePrint 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.