MBSS Business Department ~ 2008 Revision



Spreadsheet Assignments

TABLE OF CONTENTS

General Notes and Information:

Basic Spreadsheets 2

Designing a Good Spreadsheet 9

Excel Exercises to be submitted:

❑ Watt’s Tutorials #1 to 6 [website]

❑ Ongoing Example Part 1 10

❑ Quarterly Sales Report 10

❑ Crawford’s Monthly Budget Part 1 11

❑ Crawford’s Monthly Budget Part 2 12

❑ Arties Part 1 13

❑ Dozen Doozies [website]

❑ Watt Tutorials # 7 to 10 [website]

❑ Ongoing Example Part 2 14

❑ Arties Part 2 14

❑ Classroom & Course Information Part 1 15

❑ Payroll Calculations 19

❑ Classroom & Course Information Part 2 20

❑ Original Mark Sheet (part 1) 21

❑ Original Mark Sheet (part 2) 23

❑ Original Mark Sheet (part 3) 25

❑ Charting (aka Graphing) 27

❑ Travel Agent 28

❑ Bank Loan 30

❑ Watt’s Tutorials # 11 to 15 [website]

❑ 44 CD’s 34

Spreadsheet Notes

What is a Spreadsheet?

A spreadsheet is simply an electronic version of manual worksheet that consists of a rectangular grid of rows and columns. It is used to enter labels and numeric data. The spreadsheet program will automatically recalculate the entire worksheet if changes are made to any data item on the sheet.

A spreadsheet program consists of a set of instructions that will allow the user to enter data into rows and columns and then manipulate that data using mathematical and financial functions and formulas, date and time functions, string or logical functions. Often the package will allow the user to sort, graph, and extract information in various formats.

Typical Spreadsheet software packages include:

Multiplan Quattro Lotus 123 Excel Works

Typical Uses for a Spreadsheet include:

- balancing a cheque book

- maintaining inventory records

- maintaining accounting records: income statement and balance sheet, cash flow...

- forecasting budgets

- maintaining statistics: league scores, team statistics, hockey pools...

- tracking investments or amortizing loan payments

- recording student marks and letter grades

Things to note about the worksheet:

1. The worksheet is bordered by a row of letters across the top and a column of numbers down the side. A spreadsheet in Excel can measure in excess of 8192 rows by 256 columns by 256 pages!

2. A cell is the intersection of a row and a column on a specific page. Notice the notation in the top left corner of the worksheet: the notation A:C10 means you are on page A at the intersection point of column C and row 10.

When you are dealing with multiple worksheets in your formulae you will notice the notation becomes more detailed. For example, a formula like A10*B4 becomes A10*Sheet2!B4 if another worksheet is involved. The formula becomes even more detailed if the file is linked to cell(s) in a completely different file.

3. The cell pointer is the highlighted cursor in a given cell that identifies the currently active cell. You can quickly move the cell pointer to another location with either the arrow keys or the mouse (and scroll bars).

4. The control panel is made up of two sections: pull-down menus and speed buttons. You should experiment with most of the menu and button options to see what they accomplish. Use pull-down menu "Help" to gain more insight about an item that you don't understand.

5. The window is that portion of the worksheet visible on the screen. You change the window that is visible on screen by any of the following movement methods (assuming the screen is maximized and is at 100%):

|Arrow Keys |moves cell pointer up, down, left or right one cell |

|Mouse |use the scroll bar or simply point on the desired cell |

|Home |moves the cell pointer column A |

|Ctrl Home |moves the cell pointer to cell A1 |

|Ctrl End |moves the cell pointer to the end of the last occupied cell |

|Page Down |moves cell pointer down 25 rows |

|Page Up |moves cell pointer up 25 rows |

|Tab |moves one cell to the right |

|Shift Tab |moves one cell to the left |

|F5 |the “Go To” window jumps to a specified cell address |

Function Keys:

| |Function Key | SHIFT | CTRL |ALT |CTRL+ SHIFT |ALT+ SHIFT |

|F2 |Edit the active cell |Edit a cell comment | |Save As command | |Save command |

|F3 |Paste a name into a |Paste a function into |Define a name | |Create names by using | |

| |formula |a formula | | |row and column labels | |

|F4 |Repeat the last action|Repeat the last Find |Close the window |Exit | | |

| | |(Find Next) | | | | |

|F5 |Go To |Display the Find |Restore the window | | | |

| | |dialog box |size | | | |

|F6 |Move to the next pane |Move to the previous |Move to the next | |Move to the previous | |

| | |pane |workbook window | |workbook window | |

|F7 |Spelling command | |Move the window | | | |

|F8 |Extend a selection |Add to the selection |Resize the window |Display the Macro | | |

| | | | |dialog box | | |

|F9 |Calculate all sheets |Calculate the active |Minimize the workbook | | | |

| |in all open workbooks |worksheet | | | | |

|F10 |Make the menu bar |Display a shortcut |Maximize or restore | | | |

| |active |menu |the workbook window | | | |

|F11 |Create a chart |Insert a new worksheet|Insert a Microsoft |Display Visual Basic | | |

| | | |Excel 4.0 macro sheet |Editor | | |

|F12 |Save As command |Save command |Open command | |Print command | |

Working with LABELS and VALUES:

There are two general categories under which data can be entered in spreadsheets: LABEL (text) or VALUES (numbers, formulas). There is a huge difference on how the computer interprets each entry.

LABELS (TEXT)

Labels are used for descriptive text in a spreadsheet. For instance: row and column headings, titles, input data, or explanations. It may also be for numbers that are not going to be mathematically manipulated, as in, telephone numbers or social insurance numbers. In order for data to be placed in a spreadsheet as a Label, the following rules must be conformed:

(i) Must begin with any character that does not represent a number or formula. You can be relatively safe if you use a letter from A-Z as your first character that the entry will be a label.

(ii) If you want a number (say a phone number, social insurance number, or a PEN) to be treated as a Label, it must begin with a single quote (example, enter 555 as ‘555).

Unless you tell the spreadsheet differently, labels will always be left aligned by default.

VALUES (NUMBERS)

Values are numbers that are meant to be mathematically manipulated, for instance, hourly wage or bonus %. Values can also be formulas, for example, gross wage = hourly wage x hours worked.

If you enter a Value as a Label, it cannot be mathematically manipulated.

Values can begin with one of the characters:

|numeric digits |0,1,2,3,4,5,6,7,8, or 9 |

|period |. |

|negative or positive sign |+ or - |

|dollar sign |$ |

Do not include spaces or commas when typing in numbers. The computer will automatically insert the correct formatting when you format the cell.

Common formatting styles include the ones shown on the right.

Make sure to specify the correct decimal amount whenever you are working with numbers.

If a formula starts with a cell reference address, a plus sign, a minus sign or an equal sign must precede the cell address.

For instance, there is a huge difference between typing the following commands:

A2+C12 versus =A2+C12 versus +A2+C12

The first entry is a Label, while the second two entries are Values.

Three types of formula building are:

(i) Arithmetic Formulas

Calculate numeric values using the arithmetic operators:

multiplication (*),

division (/),

addition (+), and

subtraction (-)

(ii) Logical Formulas

Calculates a numeric value by comparing values in two or more cells using logical operators. The most common logical operators include the =functions

(see =function section later in notes)

(iii) Text Formulas

Calculate with labels using a text operator (&).

For example: “Total Due “ & B4+B5+B6

=Functions:

=functions are preprogrammed routines or formulas that have been generated to save the user time. Instead of typing the exact formula, the user can simply use the =function to perform the routine. For example, it is much easier to type =sum(A1..A10) than to type +A1+A2+A3+A4+A5+A6+A7+A8+A9+A10

A function always contains the =function name, and then the values to be manipulated are usually enclosed in brackets. The values are referred to as arguments of the function. Function assignments might contain: actual values, cell addresses, range names, formulas, other =functions, or a combination of the above.

The Watt’s Tutorials will explain step by step how to use the different formulae.

Things to note about using =functions:

(i) Do not leave spaces between the =function's name, brackets or arguments.

(ii) Separate multiple arguments in the brackets with commas or semicolons.

(iii) Enclose a function's arguments in parenthesis.

(iv) Use double quotes around the actual string labels you use as arguments.

Here are the most common =functions we will be using in this course:

|Formula |What the formula does… |Formula's Structure |

|Today |finds out today's date |=today() |

|Average |find the average number in a list |=average(list) |

|Maximum |finds the highest number in a list |=max(list) |

|Minimum |finds the lowest number in a list |=min(list) |

|Sum |find the total amount in a list |=sum(list) |

|Count |counts the # of cells with numbers |=count(list) |

|Counta |counts the # of cells with info. |=counta(list) |

|Countif |counts the # of cells meeting a specific condition |=countif(list,condition) |

|Left |counts char. from left side of cell |=left(cell,# of char.) |

|Right |counts char. From right side of cell |=right(cell,# of char.) |

|Rank |ranks a specific cell in al list |=rank(cell,list) |

|Vlookup |vertically looks up a cell in a table and returns info.|=vlookup(cell,table,col.) |

|If |Returns back one of two options. |=if(logical test,true,false) |

Order of Precedence (Order of Operations)

The computer will do mathematical operations in a very specific manner, similar to the idea of BEDMAS in a regular math class. The computer assigns each operator a precedence -- operators with the higher precedence number are done first. For instance, 5+1*3=8, not 18. If you want a certain operation to occur before another, then use brackets. For instance, in the above example, if you had put brackets around the 5+1, the answer would have been 18.

Here is a table for the Order of Precedence:

|Operator |Description |Precedence |

|^ |the power of (exponential) |7 |

|- + |negative or positive |6 |

|* / |multiplication, division |5 |

|+ - |add or subtract |4 |

|= < > |equal, not equal |3 |

|< > |less than, greater than |3 |

|< = |less than, equal to |3 |

|#not# |logical NOT |2 |

|#and# |logical AND |1 |

|#or# |logical OR |1 |

|& |string combination |1 |

Solve the following mathematical questions using the above precedence order:

1. 5+3*2 4. 5+4*8/4-3

2. (5+3)*2 5. (5+4)*8/(-2+6)

3. 5^2*2 6. 5+3*4^2/6-2*3^4

Cell Referencing & Operators

CELL REFERENCING

Cells are identified by their coordinates. Example, B5 represents column B row 5. A specific piece of information is contained in this one cell.

When building formulas, cells can be referenced or identified in three ways:

(i) Relative Referencing:

- default setting for the spreadsheet package

- appears most commonly during the COPY and MOVE procedures

- column or row references are adjusted automatically to reflect new column or row

designations in relation to the previous position of cell pointer or formula.

(ii) Absolute Referencing:

- in identifying the co-ordinates of a cell, the spreadsheet will not adjust the row or

the column references.

- co-ordinates of a cell are absolute -- they do not change. If you copy or move that

absolute cell, it will not change its fixed address.

(iii) Name Referencing:

- identifies a cell or block of cells by a label or assigned name.

- usually used with larger spreadsheets or when naming tables.

OPERATORS

Operators are just symbols that are used to compare and/or join items in the building of formulas. As mentioned earlier, the computer will do operators according to BEDMAS, from left to right. The operators are broken into three categories:

|Arithmetic Operators | |Logical Operators | |String Operator |

|Exponential |^ | |Less than |< | |Join |& |

|Multiplication |* | |Greater than |> | | | |

|Addition |+ | |Greater than, equal |>= | | | |

|Subtraction |- | |Equal to |= | | | |

| | | |Not equal to |< > | | | |

Formatting Cells

To format cells, simply highlight the cells and click with the right mouse. Select the “Format Cells…” option. This will lead to a new window that offers a variety of formatting opportunities, depending on what you are trying to accomplish.

Layout of a Good Spreadsheet

Main titles appear in column A (Documentation, Assumptions, Worksheet, Summary & Table) and all the other work begins in column B.

A B C D E

|DOCUMENTATION: | | | |

| | | | | |

| |Your full name | | |

| |Date (use formula) | | |

| |Exercise name | | |

| | | | | |

| | | | | |

|ASSUMPTIONS: | | | |

| | | | | |

| |PST = |7% | | |

| |GST = |7% | | |

| |Discount of |$10.00 |if subtotal is over |$100.00 |

| | | | | |

| | | | | |

|WORKSHEET: | | | |

| | | | | |

| |Always center a title over all the columns |

| | | | | |

| |Item |Qty. |Price |Extension |

| | | | | |

| |Dog |1 |$45.00 |$45.00 |

| |Leash |1 |$7.00 |$7.00 |

| |Food |5 |$1.25 |$6.25 |

| | | | | |

| | | |Subtotal |$58.25 |

| | | |Taxes |$8.16 |

| | | |Discount |$0 |

| | | |Net Due |$66.41 |

| | | | | |

| | | | | |

|TABLES: | | | | |

| |Table 1: Discounts | |

| |Number Bought |Discount | |

| |0 |to 9 |10% | |

| |10 |to 19 |15% | |

| |20 |or more |25% | |

| | | | | |

| | | | | |

|SUMMARY: | | | |

| | | | | |

| |usually minimum, maximum, average, and count formulae |

| | | | | |

“Ongoing Example”

Instructions:

1. This spreadsheet will be set up for you following the guidelines shown on the previous page (see Layout of a Good Spreadsheet). In future assignments you will be expected to set this up. Read all instructions before you begin.

2. Your spreadsheet will be an inventory of all the equipment in a computer lab. Your job is to create formulas to calculate the subtotal, taxes and total amount of the inventory. You must also fill in the Summary Section. Use cell references. Refer to page 7 of this document for a list of Excel formulae that you can use.

3. Scroll over the red triangles in the upper right corner of the cells to receive information about data, formula, formatting or just to receive hints.

4. Remember that you can select a cell, grab the lower right corner and drag either vertically or horizontally to copy formula into rows or columns. You should not be entering the formula repetitively.

5. Open up Ongoing Example and begin. Don’t forget to save your work in the appropriate format; LASTNAME,FIRSTNAME-ASSIGNMENTNAME.

6. Drop your finished copy into the hand in conference on FirstClass.

To see a sample of this project, click here.

“Quarterly Sales Report”

Instructions:

1. Open the Excel document Quarterly Sales Report. Save a copy on your hard drive. Use the appropriate format; LASTNAME,FIRSTNAME-ASSIGNMENTNAME.

2. Fill out the documentation section. Your assumptions section will have two assumptions – the ideas of $1,000 (next month’s revenue is $1,000 higher than present month’s revenue) and 35¼%. (expenses are 35¼% of the revenue).

3. Formulae:

a) next month’s revenue is $1,000 higher than present month’s revenue.

b) expenses are 35¼% of the revenue

c) net income = revenue – expenses

d) Overall % Profit = (total net income) ( (total revenue)

4. Formatting notes:

a) currency to 2 decimal places (use “currency”, not “accounting” when formatting)

b) percentages to 2 decimal places

c) center the titles over multiple columns – separate lines for each title

d) do a spell check (use “Tools > Spelling” or simply press F7)

5. Fill in the Summary Section with the appropriate formulae.

To see a sample of this project, click here.

“Crawford’s Monthly Budget” (part 1)

Background:

Marc Crawford (ex coach of the Vancouver Canucks and current coach of the Los Angeles Kings) and his wife, Helene, are designing a family budget. Marc wants to set up a spreadsheet that would show his family's income, expenses, and savings per month. Marc makes $4,500 per month, while Helene makes $3,800 per month. Marc expects his family's total income will increase 5% per month (that is, February’s income is 5% higher than January's income, March is 5% higher than February, etc.). Helene says they spend 80% of their combined income on expenses. The difference between their combined income and expenses goes into their savings account.

Instructions:

1. Prepare a worksheet showing with the following headings: Date, Income, Expenses, and Savings. The worksheet should run for a 48-month period. Start with this present month and continue for 47 more months. Include a column for the year. Don’t forget a title for the worksheet.

2. Use an Assumption Table to put in all the appropriate background information… you will have several key data, like each of their beginning salaries, the 5% monthly increase, the 80% towards expenses and so on. Make sure the assumption data has it’s own cell apart from it’s description so that you can use it in cell references.

3. Use formulas to calculate the Expense and Savings amounts. Use cell references to the assumption data in the first month for their combined income. Format currency to 2 decimal places (use “currency”, not “accounting” when formatting).

4. Insert the picture of Marc Crawford into the spreadsheet. Use line drawing, color, filling and other enhancing formatting ideas to make your spreadsheet look good.

5. Make sure you have all four sections: documentation, assumptions, worksheet and summary. Save as LASTNAME, FIRSTNAME-Crawford#1.

“Crawford’s Monthly Budget” (part 2)

Changes:

Open Crawford#1 and Save As Crawford#2, you can then begin part 2. Marc and Helene have decided to make only a 1-year budget (instead of 48 months), but make it much more detailed in nature.

1. They have decided to breakdown the large category of "Expenses" and into smaller expense categories called "Household", "Entertainment", "Transportation" and “Miscellaneous”. Therefore, eliminate the single 80% expense column and create four columns. Also, create an Expense Subtotal column that will add the four expenses. Here are the new columns under Expense and how each is calculated:

Household: 40% of combined incomes

Entertainment: $500 per month

Transportation: $700 per month, plus 3% of combined incomes

Miscellaneous 5% of combined incomes

Subtotal: add the four expenses together

2. Helene was given a raise in pay from $3,800 to $4,600.

3. Instead of 5% raise each month, their combined income receives a $10,000 raise at the beginning of every second month (example: Sept. = $5,000, Oct. = $15,000, Nov. = $15,000, Dec = $25,000…) Enter a formula pattern for the first five months and then drag down the pattern to apply it to the rest of the months.

4. Use line drawing techniques to separate items or to join similar items, for example:

|Date |Revenue |Expenses |Savings |

| |Combined Income |Household |Entertainment |Transportation |Miscellaneous |Subtotal | |

| | | | | | | | |

“Arties (part 1)”

General Information:

You have been hired to develop a small spreadsheet program that will calculate what a customer owes after they have had their car serviced at Arties. Set up a spreadsheet with good layout including documentation, assumptions, worksheet and summary.

Worksheet

Merge and Center across all the columns, the following 3-line heading:

Artie’s Auto Garage & Repair Station

Billing Sheet Record

For the Previous Accounting Week

|Bill # |Labour |Parts |Subtotal |Taxes |Total Due |

|510 |42.68 |260.57 | | | |

|463 |89.47 |93.28 | | | |

|473 |34.79 |74.96 | | | |

|522 |125.79 |108.46 | | | |

|423 |85.33 |131.42 | | | |

|538 |25.88 |6.87 | | | |

|443 |100.42 |78.83 | | | |

|552 |64.26 |85.49 | | | |

|543 |15.62 |6.13 | | | |

|403 |96.76 |112.49 | | | |

|483 |123.03 |455.22 | | | |

|Totals: |? |? |? |? |? |

Instructions/Hints:

1. Begin by entering in the above data. Make sure to use formulae whenever possible.

2. Helpful hints for the formulas:

a) Taxes: calculates the total amount of tax due on bill number.

A customer pays both GST and PST for Labour, but only PST for parts. PST currently is 7%, GST is 5%.

b) Total Due: this is the Taxes added to the Subtotal.

3. Sort the table from highest Total Due to lowest Total Due.

4. Total up each of the columns.

5. Change all the column headings to be 90o and shaded a nice color.

6. Add some good line drawing, alignment and other formatting to make it look really good.

“Ongoing Example Part 2”

Instructions/Hints:

1. Open up your previous work on Ongoing Example.

2. Scroll over the red triangles in the upper right corner of the cells to receive information about data, formula, formatting or just to receive hints.

3. Now that you have completed Watt’s Tutorials # 6-10, re-do the Summary section with formula. Refer to page 8 of this document for a list of formulae that you can use.

4. Add three more summary items and use formula to find the answer:

a. Number of items in the list

b. Number of HW items

c. No. of items that have an item cost of at least $700.

5. Save this new document in the appropriate format as Ongoing 2 and drop a copy into the hand in bin.

“Artie’s Garage Part 2”

Instructions/Hints:

1. Add a new column called “Payment Method” just to the right of the Total Due column. In this column the word “Cash” or “Credit” will appear. Artie codes his bills: any bill number in the 400’s was paid by cash and any bill number in the 500’s was paid by credit card. [Hint: will need to use =if formula]

2. Add a new column called “Payment Discount”. If a person paid with cash, then they receive a 4% discount. This discount is based on their subtotal amount. If a person pays by credit card they do not receive any discount. [Hint: use =if]

3. Add a new column called “Volume Discount”. If a person’s subtotal is $200 or more at Artie’s, they will receive a $20 discount. If a person’s subtotal is under $200, then there is no discount. This column total should provide the owner with information on how much money was lost to volume discounts given. [Hint: use =if]

4. Add a column called “Total Invoice”. This is the Total Due, less any payment discount or volume discounts.

5. Artie and his bookkeeper made a couple errors:

a) Bill 473 should have been $34.79 for Parts and $74.96 for Labor, he has it backwards right now.

b) Three bills were found and need to be added to the worksheet:

Bill # Labor Parts

555 64.26 85.49

444 15.62 6.13

556 96.75 112.49

c) Due his lateness of fixing a car, Artie has dropped the Labor cost of bill #543 to $0, the Parts are still $6.13

6. He wants you to sort the bills in order of bill number (lowest to highest)

7. He wants column totals at the bottom of all the columns except: bill # and payment method.

8. Artie wants a Summary section where he could see the following information:

a) the total number of bills he put out last week

b) the number of people who paid by cash

c) the number of people who paid by credit card

d) the average subtotal amount

e) the number of subtotal amounts over $200

f) the average total due

9. Make sure that the summary section contains formulae!

“Classroom & Course Information” (part 1)

General Information:

You have been given a page from a teacher’s day that has student and test information (see following). The teacher wants you to setup a marking program that will calculate each student’s overall percentage.

Instructions/Hints:

1. You do not need a Documentation or Assumption section for this exercise… start in cell A1.

Fill in the Course and Block as if it were this course.

2. Add all the students into the worksheet (last name first). Include yourself with perfect marks… of course. The worksheet should show: course, block, student name, division, student number, test name, possible mark, test date, student’s score, and a new column “student’s overall percent”.

3. In the Legend section, beneath the “Detention”, include an “Excused” comment with a symbol of “Ex”. Any student who was excused for an acceptable reason should have an “Ex” instead of a mark.

4. At the bottom of each appropriate column, show the:

highest, lowest, and average test score for each task.

5. In a Summary Section state the highest, lowest, and average overall percent for the class.

6. Add another mark, “Homework Check 3”, out of 10, done on Oct. 19th. Here are the scores for the class:

J. Smith: 8 J. Ollphen: 5 C. Maye: 8 M. Smyth: *see note below

L. Leung: 7 B. Ollphen: 5 A. Leung: 6 S. Ferguson: 7

R. Smith: 9 R. Victory: 10 B. Thompson: 5

*Smyth did not write this test; he was sick and brought a note.

He receives an “Ex” – it will not affect his overall mark percent.

7. A new student transferred into the class. His name is William Smith. The teacher excused his first three tasks, but he was responsible for the other three – his marks were 5, 25, and 9 respectively. He is in Division #5 and his student number is 53301.

8. To make it easier to find students, put them in alphabetical order by last name.

Formulae/Formatting:

➢ Set the Overall Percent to 1 decimal place, while all the marks can be set to zero decimal places.

➢ Duplicate the following exact page into the computer (even vertical information). If you prefer, scroll down two pages for a sample spreadsheet. Observe the line types and how the actual data has been aligned inside the cell. For instance, where you typed in “Course” and “Block”, there is room above and below the words

➢ You can get the dark triangles in the Legend by using Insert Shapes(Basic Shapes

Part One Sample

[pic]

Part Two Sample

“Payroll Calculations”

Instructions/Hints:

Set up a Payroll spreadsheet using the appropriate layout. There are lots of opportunities in this assignment to use =IF.

It is expected that you will use this formula whenever possible.

1. . Click here to open the “Payroll Calculations” template.

You will see employee names and column headings. You will notice that all the column headings have red comment triangles to help you with the formulae.

2. If a person works in the Office their regular hourly wage is $15.

If a person works in Sales their hourly wage is $8, plus they are paid a commission. The commission is 7.5% of their gross sales.

3. A regular workweek is 40 hours – for all these hours the person is paid their regular wage. If a person works over 40 hours, s/he is entitled to an overtime wage for all the hours worked over 40. The overtime rate is 1½ times the person’s regular hourly.

Examples:

person works 34 total hours: 34 hours of regular

person works 47 total hours: 40 hours of regular and 7 hours of overtime

4. The company must collect an “Income Tax Expense” from all its employees, but the actual percent that a person pays is based on their gross pay. Those employees making over $3,000 must pay a tax rate of 35%, while all others only pay 25%.

5. The company collects 8¼% of their gross pay as “Benefit Expense”. This pays for the employee’s medical and dental expenses. All employees pay this expense.

6. Sort the list by employee name (A to Z).

7. Using WordArt, type in an appropriate title. Insert two pieces of clipart, one on each side of the title, into the worksheet section.

8. Create a summary section with totals for all columns except hours worked and wages.

“Classroom & Course Information” (part 2)

General Information:

Open your “Classroom & Course Information” (part 1) as use Save As to change it to part 2.

Instructions/Hints:

1. Danielle Steele, a new student, just transferred into class. She is in Division #4, and her student number is 44777. She just had to write the last two assignments, she was excused from first four. She scored perfect on each of them.

2. Add a new assignment called “Homework Check 4” done on October 26st. It was marked out of 8. All students received “8” except for both the Ollphens, who both received “0”

3. Create a new column, called Letter Grade, using the following grade distribution:

A: 86-100% C: 60-66%

B: 73-85% C-: 50-59%

C+: 67-72% I: less than 50%

Hint: You have two choices;

• a nested IF formula where the FALSE value is the start of a new IF formula…on and on until the last lettergrade

• a vlookup formula where you will need to create a table of values to reference; select F1 for help on this

4. In the Overall Percent column you must create a formula that can be copied down and does not create an error message from excused marks. A nested IF formula will work well here.

5. In the Summary Section add the highest, lowest, and average percentage.

6. Make a new column and call it “Notification”. If a student has received the letter grade “I”, then notification to a parent/guardian must occur. Therefore, please put the phrase “phone home” if a student has received an “I”; otherwise the cell should read “n/a”.

7. Insert a new column, after the overall percent column, called “Rank”. This column will show the student where s/he ranks in the overall class (example: 1, 5, 10,..).

Alice Maye transferred out of the school. Totally remove her from your mark book.

8. Resort the table from lowest student number to highest student number.

“Original Mark Sheet (Part 1)”

General Information:

In the past, you have always recorded your marks manually in a book and then calculated the grades on a calculator. You have recently learned a spreadsheet application program and would like to convert the student information onto the computer, which will automatically do all your grading. In the documentation section, add “Part 1”.

Explanation:

Some explanation of some concepts:

a) In the homework section, a (means the h/w was done, an ( means it was not completed.

b) Each homework check is worth 4 marks: either 4 for completing or 0 for not completing.

c) Half marks are not rounded up for assignments or tests.

d) The teacher was not original in naming the tasks, s/he just called them #1, #2, #3, etc.

In the Worksheet Section:

Provide the following information in this section:

a) Student’s name

b) Student’s mark

c) What the task was out of

d) Overall Percentage (not a percentage for each item… just one overall percentage)

In the Summary Section:

Put the following summary items in:

a) the number of students in the class

b) the overall lowest %

c) the overall highest %

d) the overall average %

Formatting:

Put in a proper documentation and assumption section

All percents to 1 decimal place

Change the task names from #1 (and so on) to some real names (like “Definitions”) and put them vertically on your worksheet.

Sort the students alphabetically by last name.

This worksheet should all fit onto 1 page

a) Use formulae and cell referencing whenever possible.

Student Information:

Allison EVANS

Tests: Assignments: H/W Checks: ((((((

Brandon MARSHALL:

Tests: Assignments: H/W Checks: ((((((

Crystal BANKHEAD:

Tests: Assignments: H/W Checks: ((((((

Daryl gRAY:

Tests: Assignments: H/W Checks: ((((((

Add your Full Name

Score yourself with marks that will give you between 86% and 90%… you score an “A”, but you still have some room for improvement.

See the following page for a sample if you are unsure how to set the worksheet up.

[pic]

“Original Mark Sheet (part 2)”

General Information:

After you have successfully completed part one, copy the grade book onto Sheet 2. In the documentation section add “Part 2”. You are going to make the following changes and alterations:

Formatting:

All percents to 2 decimal places

Sort the students from highest percentage to lowest percentage

This worksheet should all fit onto 1 page

a) Use formula and cell referencing and make sure that a formula is efficient… that is it can be used for an entire row or column and does not have to be adjusted.

Changes to statistics:

Daryl’s test out of 20 should have been 10, not 0

The last assignment was scaled down, it is now out of 10 not 12. The students’ actual task scores did not change, only what it was scored out of.

One more h/w check was done, everyone did their h/w except for Allison.

Weighting the different sections:

The teacher has decided to weigh each section differently, that is s/he wants more emphasis put on the students tests and assignments. The teacher wants the following sections to be worth the following proportions of the student’s mark:

Tests: 50%

Assignments: 35%

Homework Checks: 15%

Therefore, you will have to add a new column that shows want percentage a student got in each section. You should be able to tell Brandon what his overall assignment percentage is or what Crystal’s overall HW mark is.

You will still have an overall percentage for each student… it will now be “weighted”.

Adding a new student:

Add Evelyn VEERGIN to the class list. She has the following marks for the above tasks:

Tests: 16, 26½, and 6

Assignments: 4½, 3, 7, and 9

Homework Checks: (((((((

Changing to include a Bonus Mark:

The teacher has offered an incentive to complete more h/w. If a student completes 75% of their h/w, then they will receive an additional 2% on their overall total mark. This means that they completed 75% of their h/w tasks, not that they received 75% in their overall marks.

That means that if you finished with 66% overall, and you did at least 75% of your h/w, you get a bonus of 2% and finish with 68%. This means you move up a lettergrade!

Please make sure you show the following column information:

The final percentage before the bonus.

A column that shows if the person gets a bonus (it will say “yes” or “no”).

The overall final percentage after the bonus has been factored in.

This is sort of an example of what is expected:

[pic]

“Original Mark Sheet (part 3)”

General Information:

After you have successfully completed part two, copy the grade book onto Sheet 3. Add “Part 3” to the documentation section. You are going to make the following changes and alterations:

Add a Letter Grade column:

You want to include a letter grade beside the student’s overall percentage. Use an IF or a VLOOKUP formula. Here are the grade distributions:

A 86% and higher

B 73% to 85%

C+ 67% to 72%

C 60% to 66%

C- 50% to 59%

I less than 50%

Add a Credit? column:

You want to add a new column labeled CREDIT? This credit column will show whether a student passed or failed. Beside the student’s lettergrade, indicate a “pass” or “fail”. Use and IF formula. Here are the credentials for receiving which comment:

Pass 50% or higher on their overall final percentage

Fail less than 50% on their overall final percentage

Add a Work Habits column:

You want to add a new column labeled WORK HABITS. This column will state one of four comments, depending on the student’s overall final percentage. Use and IF formula. Here are the credentials for receiving which comment:

Very Good received 90% or more on their overall final %.

Good received 70% to 89% on their overall final %.

Satisfactory received 50% to 69% on their overall final %.

Needs Improvement received less than 50% on their overall final %.

This is sort of an example of what is expected:

[pic]

Charting the results:

Plot a graph that shows each student’s homework percent, assignment percent, test percent and overall percent. Customize aspects of the chart so it reflects the idea of being a school mark graph.

“Charting (aka Graphing)”

Goal:

You are going to research three different topics on the Internet. After manipulating the Internet information, you are then going to graph the results of your research.

First Task:

Open up the file called “Graphing – 3 Charts on the website. This file will have an example and the instructions for you to complete this exercise.

“Travel Agent”

General Information:

Develop a detailed cost estimation worksheet for a travel agency. The client only needs to come into the office and state six pieces of information: the number of people traveling, the number of days on the trip, the destination, the hotel, vehicle rental type and a table that you make up. You will punch in that data and an amount due will automatically be calculated and displayed for the person. This assignment will be produced on only one spreadsheet but can use multiple sheets within.

Information about the Travel Company:

1. The company must collect 12½% in travel tax for each tour package.

2. The company uses tables to lookup cost information about destination, hotel, vehicle, and one other table that you have created.

a) Destination costs are on a PER PERSON basis.

b) Car costs are based on DAILY rates (regardless of # of people using the vehicle)

c) Hotel costs are based on ONE person staying in the room -- charge an extra $5/person/night for each additional person.

d) Create one more original table that you might think would be handy for a travel company.

3. Helpful formulas:

a) Extensions = multiply the unit cost by how many days or number of people

b) Taxes = a percentage of the subtotal

c) Amount Due = add subtotal to taxes

Tables:

Destination Options Hotel Options Vehicle Rental Options

|Locale |Cost per | |Hotel |Cost per | |Vehicle |Cost per |

| |Person | |Name |Day | |Type |Day |

|Alaska |$1,200 | |Four Seasons |$125.00 | |Jeep |$25.50 |

|Bahamas |$2,600 | |Hyatt |$140.75 | |Cadillac |$42.70 |

|Caribbean |$2,400 | |Motel 6 |$39.50 | |Temp |$16.00 |

|Greece |$3,300 | |Stay 'n Save |$59.00 | |Mini-Van |$22.85 |

General Layout:

Set up your worksheet using the standard format. Also, create an original letterhead for your travel agency. Include a fake address and contact information. In the worksheet section create an “invoice” template with color, borders, and clipart to make your travel agency stand out. Make sure you include an organized display for the details of the trip. Cell reference all the travel agency data from you tables. You should only have to input customer information when you use your template. Use formula to calculate all the extensions. Use a formula to calculate “extra person”.

Note: this example below is based on only 3 tables – you need to include your own original table too.

Clients Names: Mr. & Mrs. M. Naslund Length of Stay: 14 days

Number of People: 2 Taxes: 12½%

Option Unit Cost Days People Extension

Destination: Greece $3,300.00 2 $6,600.00

Hotel: Hyatt $140.75 14 1,970.50

extra person $5.00 14 1 70.00

Vehicle: Motorcycle $15.85 14 221.90

Subtotal $8,862.40

Taxes 1,107.80

Amount Due $ 9,970.20

Exercises:

Note: copy your invoice template to do each exercise:

1. Do the Markus Naslund example above. Markus and his wife would like to travel to Greece, stay at the Hyatt, and rent a motorcycle. They will be gone for 14 days.

2. Create a trip for you and your best friend. The whole trip should cost between $9,000 and $10,000.

3. Mr. Precise would like to combine two price quotes on one invoice. He wants to spend between $12,000 and $15,000 on the two trips combined. He is undecided where he would like to go, where he would like to stay and what type of vehicle -- he only knows that he wants both trips to be completely different (cannot use the same location, car, or hotel). For the first trip he will be traveling with his wife. The second trip will just be he. Both trips are 7 days in length. Provide the details of his whole tour package.

Before you hand this in rename each sheet with an appropriate name (if you used more than one) and set View to 75% for ease in marking. Make sure the entire invoice can be viewed on screen.

“Bank Loan”

General Information:

You have saved up a $2,000 down payment towards the purchase of a new car. The car you want to buy costs $8,500. With a parent co-signing, you will need to finance the difference using a bank loan. You want to pay back the loan over 5 years.

There are two banks (“A” and “B”) that have offered you a loan:

Bank A says you can have an 8% (per year) loan that you pay back bi-weekly (every two weeks)

Bank B says you can have a 7½% (per year) loan that you pay back monthly (once a month)

To make it easier to figure out dates, assume you take the loan out on January 1st (of this current year).

The actual amount of money you borrow from the bank is called the Principle.

Assumption Section:

1. Provide the following: Car Price, Down payment, Bank Loan Amount.

2. Provide the following information for Bank A and Bank B:

Bank Name (make up a bank name)

Interest Rate (per annum rate)

Payment methods (put in terms of # of payments per year)

These values will need to be cell referenced in your table formulae. A usable spreadsheet allows for changes to values such as interest rate that are automatically applied to the entire table.

Worksheet Section:

You will need to construct two tables one for Bank A and one for Bank B. You will need to calculate the following amounts for each loan:

These will be the headings of the columns:

Payment #.... 1 to ?

Payment Amount this is a fixed amount you pay each month or every two weeks

Interest Paid amount (of the payment amount) that was actually interest. This is the money that the bank makes from you or the cost to borrow. The formula is Interest Rate x Loan Amount/# of payments in a year.

Principle Paid amount (of the payment amount) that was actually paid on the Principle. This amount decreases the principle owed.

Balance… the amount remaining to be paid on the loan.

Take totals of each column.

HINT: The best formula for calculating loan payments is the “Payment” formula “PMT”.

=PMT(rate/nper, npay,-pv) where:

Rate   is the interest rate for the loan.

Nper   is the total number of payments for the loan per year.

Npay is the total number of payments for the life of the loan.

Pv   is the present value, or the total amount that a series of future payments is worth now; also known as the Principal which is also known as the amount of the initial loan = $6500

Tip: To find the total amount paid over the duration of the loan, multiply the returned =PMT value by Npay.

The PMT formula calculates the payment for a loan based on constant payments and a constant interest rate.

Examples:

The following formula returns the monthly payment on a $10,000 loan at an annual rate of 8 percent that you must pay off in 10 months:

=PMT(8%/12, 10, 10000) equals -$1,037.03

For the same loan, if payments are due at the beginning of the period, the payment is:

=PMT(8%/12, 10, 10000, 0, 1) equals -$1,030.16

The following formula returns the amount someone must pay to you each month if you loan that person $5,000 at 12 percent interest and want to be paid back in five months:

=PMT(12%/12, 5, -5000) equals $1,030.20

=PMT(C$5/12,D$5,-B$5) this formula is used when you have a list of prices and payments and want to copy the formula to other cells (remember to put the minus (-) sign before the price of the item).

=PMT can be used to determine payments to annuities other than loans. For example, if a person wants to save $50,000 in 18 years by saving a constant amount each month, =PMT can be used to determine how much must be saved. If an assumption is made that 6 percent interest can be earned on savings, =PMT can be used to determine how much to save each month.

=PMT(6%/12, 18*12, 0, 50000) equals -$129.08

If a person pays $129.08 into a 6 percent savings account every month for 18 years, the result will be $50,000.

SEE NEXT PAGE FOR A SAMPLE.

Questions to be answered in the Summary Section:

1. Compare the amounts for total interest paid and total amount of loan for each Bank in a colored chart.

2. Which bank did you end up paying more interest to? How much more?

3. What is one positive and one negative characteristic of a bi-weekly loan?

4. Of all two loans which one would you recommend to another student looking for a similar loan? Why?

5. How much would you have to put as your down payment for Bank A if you wanted your Payment Amount to be as close to $40 each two weeks as possible… round off your down payment to the nearest $25 amount.

6. How much would you have to put as your down payment for Bank B if you wanted your Payment Amount to be as close to $90/month as possible? Round off your down payment to the nearest $25 amount.

Make your spreadsheet presentable with color. Set a good layout where the summary section can be seen readily for quick review.

[pic]

44 CDs

1. Open the “Last 20 CD entries” and “44 CDs – setting it up” files.

2. Copy and paste the info from “Last 20 CD entries” into “44 CDs – setting it up”. Format all the font size/style to be the same. There should be no blank rows between the information.

3. Type in the first 24 entries (see left column) to your list… this will give you a total of 44 CDs. Hint: if you copy the list on the left and paste it into Excel this will save you some typing. You can do some editing in Excel to make it look proper.

4. Sort the worksheet first by Artist (A to Z), then break any “ties” by Year (most recent release to oldest release).

5. Main Title: Using WordArt, create a title for your worksheet.

6. Put the following ideas into an Assumptions section:

2003 CDs have a wholesale price of $8.20 and retail price of $18.25

2002 CDs have a wholesale price of $7.22 and retail price of $17.99

2001 CDs have a wholesale price of $6.52 and retail price of $16.99

2000 CDs have a wholesale price of $5.95 and retail price of $13.99

All other CDs (before 2000) have a wholesale price of $4.42 and retail price of $9.99. Update the assumptions table to reflect the above info.

7. Formulas:

Profit ($) = Retail Price – Wholesale Price

Profit (%) = (Retail Price – Wholesale Price) ( (Wholesale Price)

8. Create a new column called “Department” which will tell the reader what section of the store the music is in. CDs that were released prior to 2000 are in the section called “Older Music”, while the more recent releases (2000 to now) are in the “New Releases” section.

[Use =if formula... look in Cheat Sheet for example]

9. Sales Discount (%)

When the store puts on a sale, the have a unique way of determining their sales percent. They do the following: for each year the CD has been released, they take off 3%. That is, you need to subtract the year the CD was released from the current year, and then multiply this amount by 3%.

10. Sale Price ($)

This is the retail price minus the applied sales discount.

11. Column Totals: provide totals at the bottom of wholesale price, retail price, profit($) and sale price ($)

12. Formatting:

All money set to “currency” and to 2 decimal places.

Ensure all data is readable (stretch out column widths).

Format the worksheet with lines and borders to make it look more appealing to the reader.

-----------------------

For example: =(+B4+C2)/D15

or =(GST+PST)*C3

The result of this formula will be the words Total Due showing up in front of the sum of cells B4, B5 and B6

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

Overall

h/w

percent |Overall

Assign.

percent |Overall

Test

percent |Final %

before bonus |Receive

bonus? |Final %

after bonus | |60% |69% |65% |65.7% |no |65.7% | |77% |69% |70% |70.7% |yes |72.7% | |

1195 Lansdowne Drive

Coquitlam, BC ( V3C 7Y8

Phone: (604)464-5793 Fax: (604)464-5796

First 24 Entries

1. Sarah Brightman – La Luna (2000)

2. BB King – Riding with the King (2000)

3. Madonna – Music (2000)

4. Bare Naked Ladies – Maroon (2000)

5. Diana Krall – When I Look in Your Eyes (1999)

6. Sting – Brand New Day (1999)

7. Charlotte Church – Voice of An Angel (1999)

8. Emmylou Harris – Red Dirt Girl (1999)

9. Moby – Play (1998)

10. Santana – Super Natural (1998)

11. Creed – Human Clay (2000)

12. Various Artists – Coyote Ugly (2000)

13. Britney Spears – Oops!… I Did It Again (1999)

14. N Sync – No Strings Attached (1998)

15. Siuseppe Verdi – Andrea Bocelli: Verdi (2000)

16. your own entry (a 2002 entry)

17. your own entry (a 2002 entry)

18. your own entry (a 2002 entry)

19. your own entry (a 2003 entry)

20. your own entry (a 2003 entry)

21. your own entry (a 2003 entry)

22. your own entry (any year)

23. your own entry (any year)

24. your own entry (any year)

Pictures

Internet: Two CD covers from your favourite CDs in the list. Put one cover on each side of your main title in the Worksheet section. Remember how you got the CD cover for the scavenger hunt?!

Clipart: Put one piece of appropriate music clipart in the documentation section.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download