80 Points

Do not print this document. It is designed to be used as an electronic document. You must use an Internet-connected PC to do this assignment. The assignment contains links to online content that are crucial for your work. You will not be able to do this work from a printed page.

This assignment uses a new teaching approach that we are developing for teaching applications skills. We call it Watch-Practice-Apply. This new approach employs three teaching tools:

10-Minute Tutor SAM Training Assignment to Submit for Grade
Watch...
10-Minute Tutors are short Flash video presentations that you view in your browser window to see the skills performed and explained prior to trying them yourself. They do not cover all the skills or provide much detail; just enough to give you the idea. This is 10 minutes well invested!
Practice...
SAM is our online training and testing system. We use it here in combination with the 10-Minute Tutor in order to provide you with detailed training for all of the skills you are required to learn that may be on the test.
Apply...
After viewing the 10-Minute Tutor and practicing the skills in SAM, you are provided with step-by-step instructions for creating your own file to submit for a grade. The file is created over the course of working through the lesson modules.

We have broken the important aspects of Microsoft Excel into five modules:

  1. An Introduction to Spreadsheets
  2. Working with Cells and Cell Data
  3. Working with Functions and Formulas
  4. Formatting Spreadsheets
  5. Creating Charts, Inserting Pictures, and Printing

Module 1 provides an overview of Excel and how it is used. It also provides a look at an example spreadsheet that is very similar to the one you will submit for a grade. Modules 2-5 each includes a 10-Minute tutor, SAM training, and instructions for the file you will submit for your grade.

We hope you find this new approach an effective way to learn. After you finish the assignment, please use the feedback link provided at the end to let us know what you think of this system. It will take less than a minute and it will assist us in refining our teaching tools for future assignments and future semester.


1

An Introduction to Spreadsheets

10-Minute Tutor
Excel 1. An Introduction to Spreadsheets

 

Click the 10-Min Tutor link to learn about
• An Overview of Spreadsheets
• Example: Hannah's Big Trip
• What If?
• Spreadsheet Uses

2

Working with Cells and Cell Data

10-Minute Tutor
Excel 2. Working with Cells and Cell Data

Use the 10-min tutor and SAM to learn about
• Selecting Objects
• Entering and Editing Data
• Adjusting Columns & Rows
• Copying Data
• Using the Fill Handle
• Inserting and Deleting
and more!

SAM Training
Excel: Working with Cells and Cell Data
(located in the ASSIGNMENT SKILLS TRAINING section of SAM)


15 Points

Assignment to Submit for Grade
Click to view the example file for this portion of the assignment.

  1. Create a new file in Excel, and name it after your FSU network username (i.e. aaa04.xls).
  2. [3 pts] Create your own personal budget, as was done in the 10-minute tutorial (use the link above under 10-Minute Tutor to access the tutorial). Your personal budget should cover 12 months, rather than 6, beginning with the current month.
  3. [2 pts] Include a title in cell A1 that includes your full name (i.e. Kim Novak's Budget).
  4. [3 pts] Include at least 12 expense items, do not copy from the example, or other students -you can use fictitious or real data (don't include private information).
  5. [3 pts] Fill in fictitious or real amounts spent on each item for the 12 month period. Try out the copying techniques shown in the tutorial.
  6. [2 pts] Add a label and value in cells A2 and B2 that indicates your monthly income.
  7. [2 pts] Change column widths so that all data is visible.

Save your work for future use in the remaining steps below.

3

Working with Functions and Formulas

10-Minute Tutor
Excel 3. Working with Functions and Formulas

Use the 10-min tutor and SAM to learn about
• Using Functions
• Copying Functions
• Using Formulas
• Using Absolute References
• Advanced Formulas
• The IF Function
and more!

SAM Training
Excel: Working with Functions and Formulas


30 Points

Assignment to Submit for Grade
Click to view the example file for this portion of the assignment.
Continue working with the spreadsheet you started in the previous step.

  1. [3 pts] Use the SUM function to display the SUM for each expense over 12 months, and for each month as was done in the 10-Minute Tutor. Insert "Item Total", and "Total" labels to identify these values as was done in the example.
  2. [3 pts] Use a formula (equation) below each monthly column that indicates how much money is left over each month after paying expenses. Insert a "Leftover" label to identify these values as was done in the example.
  3. [3 pts] Insert a label and formula at the bottom of your spreadsheet that shows your total earnings for the year (the sum of your 12 monthly paychecks).
  4. [3 pts] Which expense (rent, groceries, cell phone, etc) did you spend most on over the 12 month period? Show this in your speadsheet by inserting another label (call it Maximum Yearly Expense.) and function (MAX()) at the bottom of your spreadsheet that shows the maximum yearly expense. Your Maximum Expense Amnt should only show the highest expense amount and not what it was for. For example, if your largest yearly expense was $3000 for rent , your spreadsheet will show Maximum Yearly Expense $3000.00.
  5. [3 pts] What was the average monthly total over the 12 month period? Show this in your speadsheet by inserting another label (call it Average Monthly Total.) and function (AVERAGE()) at the bottom of your spreadsheet that shows the average total amount spent each month. For example, if you spent $100 each month for the first 6 months and $200 each month for the second 6 months, the Average Monthly Total would display $150.
  6. [4 pts] Insert a label and formula (equation) at the bottom of your spreadsheet that shows how much money you have left over at the end of the year.
  7. [4 pts] Use the IF() function to display a message based on the amount of money you have left over at the end of the 12 months. Feel free to makeup your own goal. In the example it was a trip to Paris. Yours should be something different. Insert a label identifying the goal.
  8. [2 pts] Change column widths so that all data is visible.
  9. [2 pts] Open Sheet2 in the workbook by clicking its tab, and type "Leftovers" in cell A2. Then use a formula that displays the amount you have left over (see 13 above) in cell B2 of the new worksheet. Tip: To reference another cell, simply enter = and the cell name, for example =Q24. To reference cells in another sheet use =Sheetname!cellname for example =Sheet1!Q24. Search Excel Help on keywords "about cell and range references" for additional information.
  10. [3 pts] Change the name of Sheet1 to the current year, and change the name of Sheet2 to next year.

Save your work for use in the remaining steps.

4

Formatting Spreadsheets

10-Minute Tutor
Excel 4. Formatting Spreadsheets

Use the 10-min tutor and SAM to learn about
• Data Types and Number Formats
• Aligning Cell Contents
• Sorting Lists
• Formatting Fonts
• Applying Borders and Shading
• Applying Conditional Formatting
and more!

SAM Training
Excel: Formatting Spreadsheets


15 Points

Assignment to Submit for Grade
Click to view the example file for this portion of the assignment.
Continue working with the spreadsheet you started in the previous step.

  1. [1 pt] Change all dollar amounts to currency format.
  2. [2 pts] Sort your expense items along with their associated values alphabetically as was done in the 10-Minute Tutor.
  3. [2 pts] Use Merge and Center to center the title of the spreadsheet above all budget data.
  4. [2 pts] Change the title to a large, bold, font --feel free to take artistic liberties with the font type.
  5. [1 pt] Change all labels in the spreadsheet to bold.
  6. [2 pts] Change all cell contents, except the title, to a 12 point font of your choice.
  7. [1 pt] Change column widths so that all data is visible.
  8. [2 pts] Use cell fill color, and cell borders to give your spreadsheet an attractive appearance (use colors and style of your own choice). Both fills and borders must be used in at least one area of the spreadsheet.
  9. [2 pts] Use conditional formatting on the cell that contains your yearly leftover amount (see step 13) to automatically change the cell fill color to red if the number is negative.
  10. Save your work for use in the remaining steps.
5

Creating Charts, Inserting Pictures, and Printing

10-Minute Tutor
Excel 5. Working With Graphics

Use the 10-min tutor and SAM to learn about
• Creating Charts
• Inserting Images
• Printing Spreadsheets
and more!

SAM Training
Excel: Working with Graphics


20 Points

Assignment to Submit for Grade
Click to view the example file for this portion of the assignment.
Continue working with the spreadsheet you started in the previous step.

  1. [10 pts] Select the cells containing all of your expense labels, and all of the expense totals - as was done in the 10-Minute Tutorial. Use the Chart Wizard button on the toolbar to create a Pie chart. Select options of your choice to create an attractive chart that is easy to read. Insert the chart below your budget data.
  2. [3 pts] Insert a clip art image of something appropriate next to the chart.
  3. [3 pts] Set the Print Area for the spreadsheet to include the cells containing data, the chart, and clip art image --everything from cell A1 through the bottom right corner of your clip art.
  4. Use print preview to view your work.
  5. [2 pts] Change the page orientation to Landscape. Note that your spreadsheets may require more than one page.
  6. [2 pts] Type your full name in the footer of the spreadsheet along with the date you submit your work for a grade.
  7. Save your work and submit it to your Instructor for grading prior to the due date listed on the course agenda.

Now that you are done, take a moment to let us know what you think of this new leaning system. Click here to access the student feedback form.