Spreadsheet Resources and Assignments All of your spreadsheets will require calculations of one kind or another. Study carefully from the types of formulas below to learn their basic use. Make sure you’ve gone through the accompanying PowerPoint presentation in the standard before attempting to do these formulas or assignments. Come back to this page as needed for correct formulas. The 6 spreadsheet assignments are

below this page.

Formulas & Functions Formulas (and functions) always begin with the “=” sign. The equals sign tells Excel that a calculation is coming. Simple formulas usually just involve working with two cells (example =C1+C3). A more complex formula typically includes a range (column or row) of cells and or multiple steps/operations.

Mathematical Operators + * /

Addition Subtraction Multiplication Division -

(usually for more basic formulas) Example: Example: Example: Example:

Formulas for Ranges of Cells

=B2+B5 (to add two numbers) =B2-B5 (to subtract two numbers) =B2*B5 (to multiply two numbers) =B2/B5 (to divide two numbers)

(usually for working with rows or columns of numbers) Formulas always begin with the “=”. This symbols tell Excel that a calculation is coming. Formulas do not include spaces and can include values and/or references to other cells. Ranges include only the first and last cell names in the range. To Sum up a Range: To Average: Lowest # Highest # Count #s Count labels Conditional Statement:

=SUM(Range) - Example: =SUM(B2:B15) =AVERAGE(Range) - Example: =AVERAGE(B2:B15) =MIN(range) - Example: =MIN(B2:B15) =MAX(range) - Example: =MAX (B2:B15) =COUNT(range) - Example: =COUNT(B2:B15) =COUNTA(range) - Example: =COUNTA (B2:B15) =IF(condition, then x, else y) – Ask instructor

On every Spreadsheet: Do: Merge and Center spreadsheet titles Use formulas Left align text but Right align numbers Center align and bold column headings Set decimal places in a given column the same Format numbers for what they are ($ % etc.) Be creative with fill and color use Insert Name, Date, and Period in a Header Center the spreadsheet vertically and horizontally One Workbook with six spreadsheets Include your Name, Date and Period in the header

Don’t: Use a calculator Left align or center numbers Don’t use numbers in formulas where you should us cell coordinates Leave errors in your work

Spreadsheet #1 You now are the manager of a video rental store called Entertainment Today. You must prepare a report to convey the number of videos rented for each month, by category, and provide the totals following a specific format. The data is as follows:

INSTRUCTIONS: 1. Enter the data below into Excel 2. Widen columns as necessary to accommodate the longest item in the column. 3. Your Spreadsheet will not align exactly like this one. 4. Enter formulas to calculate a total for each month and each category. 5. Format your numbers to zero decimal places and add 1,000 separators 6. Bold and center align the column headings. 7. Make numbers always right aligned 8. Merge and Center titles over the spreadsheet 9. Name your spreadsheet "RENTALS".

REDBOX KIOSK #455B QUARTERLY RENTALS REPORT June New Releases 1108 Western 188 Comedy 186 Exercise 128 Movie Classics 105 Sports 133 Disney 1207 Action Films 550 Science Fiction 119 99-Cent Special 1206 Total

July 1191 150 189 119 110 119 1322 630 131 1224

August 1133 191 176 131 149 126 1409 459 126 1301

Total

Spreadsheet #2 You have done such a good job at work that your boss has encouraged you to teach a class at night. Below are the test scores for your first session.

INSTRUCTIONS: 1. Enter the data below and widen columns as necessary. 2. Your Spreadsheet will not look exactly like this one. 3.Calculate the total and average columns from correct formula. 4.Format the average column to 1 decimal place. 5.Calculate the average, highest, and lowest score for each individual test only. 6.Format all numbers appropriately. 7.Bold and center align the column headings and “merge and center” title. 8 Place the blank gray box under Total and Average columns to block it out. 9 Name your Spreadsheet CLASS

COMPUTER CLASS SPREADSHEET Student Name Allen, Sam Bell, Wilson Brown, Todd Carter, Steve Harrison, Susan Hood, Barbara Johnson, Christine Anderson, Robert Nguyen, Lui Jimenez, Irina Lau, Jonathan Average Test Score Highest Test Score Lowest Test Score

1st Test 82 91 77 62 96 91 88 69 78 86 81

2nd Test 91 65 82 75 97 88 88 78 92 85 71

3rd Test Total Average Points Score 95 96 84 77 96 90 82 71 84 75 91

Spreadsheet #3 Title: INTEREST AND PAYOFFS Include these column headings in your spreadsheet:  Principal  Interest Rate  Time  Interest Amount  Total Payoff Here is your data for Principal, Rate, and Time(in days) 7000 85000 13000 5300 8032 12987 10500 9323 6987 17879

0.134 30 0.167 90 0.24 180 0.198 270 0.234 360 0.234 30 0.214 90 0.167 180 0.236 270 0.222 360

Calculate Interest Amount and Total Payoff: Interest Amount = (principal x Interest Rate x (Time/365)) Total payoff = (principal + Interest Amount) Include these at the bottom of the Payoff column:   

Total Payoff (will be under the payoff column only) Average Payoff (will be under the payoff column only) Highest Payoff (will be under the payoff column only)

Make Sure you: 1) Widen columns as necessary to just fit the widest number in the column. 2) Bold and center the column headings and “merge and center” the title. 3) Calculate Total, Average, and Highest for the Total Payoff Column ONLY. 4) Format the Interest Rate column to number with 3 decimal places. 5) Format all dollar amounts with 1,000 separators and 2 decimal places. 6) Name your spreadsheet INTEREST.

Spreadsheet #4 Given the data below, complete the report with the payroll details for a 2-week pay period ending last Friday Payroll Register for the pay period ending: {insert last Friday's date here} Name

Hours Worked Regular OT

Gross Earnings Pay Rate Reg. Pay OT Pay Total Pay

Homer Simpson Gray Davis Tiger Woods Barbara Tran Sean Connery

80 55 80 80 80

1

Chris Pine Adam Smith Jack Kevorkian Nancy Reagan Andy Griffith Shania Twain

80 29 80 72 80 80

10

John Candy Luis Armstrong Carl Lewis Florence Griffith Mia Farrell William Clinton

26 80 80 80 80 65

Harriet Tubman Betty Rubble Julia Roberts Pete Frampton

80 40 80 41

5

8 4

7

4

7.75 6.60 13.25 6.25 10.50 12.50 14.00 12.50 10.00 6.75 7.25 8.50 12.50 6.95 7.75 7.75 9.00 9.25 8.40 8.51 9.91

INSTRUCTIONS: 1) 2) 3) 4) 5) 6) 7)

FICA

Deductions Fed With Insur Pension Total Ded.

Enter the data above and widen columns as necessary. Calculate remaining columns from the formulas. Format all decimals to 2 places, except the hour’s columns. Use appropriate formats and alignments). Total all columns except rate. Fit on ONE page. Name it "PAYROLL".

FORMULAS Regular Pay- multiply pay rate & # of hours worked O.T. Pay- multiply pay rate & # of OT hours worked * 1.5 Total Pay- add regular pay and OT pay FICA- 8 percent of total pay Federal Withholding- 12 percent of total pay Insurance- 1.8 percent of total pay Pension- 2.1 percent of total pay Total Deductions- Add all deductions Net Pay- Subtract Total Deductions from Total Pay

Net Pay

Spreadsheet #5 Budget Create a budget to keep track of your money. Make sure you do the following: 1) Format all numbers to “Currency” with 2 decimal places. 2) Adjust column width appropriately. 3) Bold and center align the column headings and “merge and center” the title 4) Calculate totals for each type of expense and for each month. 5) Include a grand total for all 4 months of expenditures. 6) Add another column to calculate the percentage of total expenses for each category by dividing each category total by the grand total. 7) Add another row to calculate the percentage of total expenses for each month by dividing each monthly total by the grand total. 8) Show how much is going into savings. (Total income minus total expenses) ***Hint: Since you have fewer months than expenses you might be better off putting expenses as rows and months as columns so it’ll fit on one page.

Your income and expenses: JANUARY: Income $900; Rent $400; Car payment $199; Groceries $125; Utilities $89.50; School Expenses $25.75; Movie Tickets $21 FEBRUARY: Income $1000; Rent $400; School books $278.97; Groceries $79.16; Utilities $76.57; Car Payment $199.61 MARCH: Income $1400; Rent $400; Car payment $199; Groceries $73.93; School Expenses $55.99; Medical Expense $100; Car Insurance $350; Utilities $69.51

APRIL: Income $1100; Rent $425; IRS expenses $279.54; Car payment $199; Utilities $58.88; Groceries $88.85; Misc Expenses $15.01

1) Use additional tools to make the report attractive. (Possibilities include: fill colors, text colors, font and size changes, word art, etc.) 2) Make sure it all fits on one page. 3) Name it "BUDGET".

Spreadsheet #6

BEST VACUUM: Salesmen by Territory Scenario: You have been hired to create a spreadsheet for the BEST Vacuum company sales supervisor. She needs information below to be included in a neat and organized way so she can keep track of all of the salesmen’s productivity. The spreadsheet must be well formatted as you have learned and then saved for future updates. Layout the following info: (You make up names and numbers.) Territory - (4 territories – Name them: North, South, East, West) Salesmen Names - (Have 4 salesmen per territory) Day Sales - (# of vacuums sold during the day) Night Sales - (# of vacuums sold during the night) Along with the columns above Calculate the following: Total Sales - (per salesman) Territory Sub-totals - (add sales for each territory) Territory Incremental - (*** please don’t ask the teacher how to get this. What might this mean? Boss is not available! So, you look up the word “incremental” and YOU decide what to do.)

% of Total Sales - (% of that territory for each salesman) hint: salesman total divided by territory sub-total *** please don’t ask the teacher how to get this. Give it YOUR best shot.

Below the data portion, the supervisor needs a summary with the following information for each territory AND for salesmen: Highest Individual Sales Lowest Individual Sales Average Territory Sales Number of Total Salesmen Relative Sales Range -- (difference between highest ind. sales and lowest ind. sales) 1) Format columns, numbers, titles, and headings appropriately. 2) Make sure it all fits on one page for your supervisor. 3) Name your spreadsheet BEST.