Complete these projects to see how well you've mastered basic spreadsheet techniques. For each one, type your name in cell A1, your class in cell A2,…

Complete these projects to see how well you’ve mastered basic spreadsheet techniques. For each one, type your name in cell A1, your class in cell A2, the project number in cell F1, and the date in cell F2. Do not change column widths for these four labels, but allow the longer labels to spill over into the next cell.

Project 1 Class Grades I: Create a class report with headings and columns of proper width for STUDENT NAME, TEST 1, TEST 2, TEST 3, and AVERAGE. Type six names into the worksheet, with three test scores each. Have the spreadsheet automatically calcu- late and display in the AVERAGE column the average of the three scores for each student. Format all numbers to show no decimals. Save the workbook as SHEET1 and print it.

Project 2 Class Grades II: Open SHEET1. Add four names to the list of six and three test scores for each new name. Copy the average formula so that all students show aver- ages. Create a class average row along the bottom. Have the worksheet calculate and dis- play the average for each test column and an overall average in the average column. For- mat the average column (not row) to show one decimal. Save the workbook as SHEET2 and print it.

Project 3 Class Grades III: Open SHEET2. Delete the second student row. Create a bar chart that displays each student’s name and overall average. Add titles. Label the sheet tabs appropriately. Save the workbook as SHEET3. Print the chart only.

EXCEL 2010 E-69

FIGURE E-66 The Show Group

Click here

E

E-70

SPREADSHEET MODULE

Project 4 Expense Report I: Create a six-month expense report as follows:

EXPENSE JAN FEB MAR APR

RENT 400 400 400 420 PHONE 68 75 50 48 FOOD 125 145 175 170 CLOTHES 200 75 150 50 MISC 120 50 65 100

MAY JUN

420 420 55 65 150 120 75 100 125 60

TOTAL AVG

Add a TOTAL row beneath all columns. Format all numbers to comma, zero deci- mals. Add formulas for TOTAL (in the TOTAL column and row) and AVERAGE (in the AVG column) to calculate results. Change column widths as needed to fit the spreadsheet on one screen. Right-align the labels for month, TOTAL, and AVG to place them over their numbers. Save the workbook as SHEET4 and print it.

Project 5 Expense Report II: Open SHEET4. Between RENT and PHONE, insert a row labeled UTILITIES. Enter monthly values of 40, 56, 52, 80, 60, 85. Type (or copy) formu- las for the row’s total and average. Change the format to currency, zero decimals in the RENT and bottom TOTAL rows. (All other rows remain comma, zero decimals.) Add six column sparklines in Column J, one to the right of each expense (change colors as desired) to display month data. Save the workbook as SHEET5 and print it.

Project 6 Expense Report III: Open SHEET5. Skip a row under TOTAL and add a row labeled INCOME with 1000 entered for each month. Add a row beneath it labeled SAV- INGS. Create formulas that will appear under each month’s income to show how much money remains for savings after expenses are subtracted from income. Create formulas for row totals and averages. Save the workbook as SHEET6 and print it.

Project 7 Interest Table: Create a spreadsheet to calculate how much a deposit of $1,000 would be worth if left in a bank for ten years at 6.5 percent annual interest. Show princi- pal and interest for each year as follows:

Annual Interest Rate: .065

YEAR PRINCIPAL INTEREST

• 1  $1,000.00 (a) 

• 2  (b) 


At the bottom of the PRINCIPAL column, create a line sparkline to show its growth.

NOTE: Note that the interest in cell (a) is the principal to its left times the absolute reference to the cell that contains .065. The new principal in cell (b) is the old principal plus the interest in the row above it. Copy these formulas in the remaining cells as needed. Save the workbook as SHEET7 and print it.

Project 8 Advertising Report: Create an Advertising versus Sales report, as shown below. Set the value format to show dollar signs but no cents.

lstQtr 2nd Qtr 3rd Qtr 4th Qtr Total

Sales $5,214$6,807$7,546$12,264(e) Advertising $ 450 $ 500 $ 600 $ 1,250 (f)

Advertising as a
% of Sales: (a) (b) (c) (d)

In the cells marked (a) through (d), create formulas that will calculate the percentage of sales represented by advertising, and present the results with two decimals. In cells (e)

and (f), create formulas for total sales and advertising. Save the workbook as SHEET8 and print it.

Project 9 Inventory I: Create an inventory value sheet with proper column widths and value formats as follows:

SHOW-BIZ NOVELTY COMPANY INVENTORY AS OF 4/25/07

ITEM QUANTITY UNIT PRICE VALUE

BALLOON 1,735 $0.11 (a) CARD 26 1.25 (b)

EXCEL 2010 E-71

FUNNY FORK OOPS EGG CLOWN NOSE TOTAL VALUE:

336 .79 (c)

605 1.35 (d) 1,122 .90 (e) (f)

Create formulas in cells (a) through (e) to multiply the quantity by the unit price to calculate the value. Format the BALLOON and TOTAL VALUE cells in the VALUE col- umn to show dollars and cents, and the four remaining cells, comma and two decimals only. Create a formula to total these values in cell (f). Save the file as SHEET9 and print it.

Project 10 Inventory II: Open SHEET9. Add a fifth column titled REORDER. Use an IF statement to print the word “ORDER” if quantity is below 500 units, or a dash if 500 or above. (Hint: Use the text “Order” and “-” in the true and false locations in the IF func- tion.) Save the workbook as SHEET10 and print it.

Project 11 Retail Discount: Create a spreadsheet that will calculate all discounted prices shown below. Use absolute and relative references to the discount percentages. (NOTE: One mixed reference in cell C4 could be copied through the range.) Report all numbers to two decimal places. Show dollar signs on the top row of numbers only. Save the workbook as SHEET11 and print it.

PRICE WITH DISCOUNT OF: 10% 15% 20%

Project 12 Grade Point Average I: Create a spreadsheet that lists all the courses you’ve taken by semester. Include columns for course numbers and titles, credits, and final grades. Then have the spreadsheet calculate your college grade point average on the bot- tom. You may add columns as needed for “quality points” or other intermediate calcula- tions. Use your college handbook as a guide to the steps in calculating your GPA. Save the file as SHEET12 and print it.

Project 13 Grade Point Average II: Using SHEET12, filter the worksheet so that only courses in which you received a “B” are displayed. Print the filtered worksheet and save as SHEET13a. Remove the filter. Create a pivot table that presents all courses in GRADE ORDER. Save the file as SHEET13b and print only the pivot table.

ITEM PRICE

Belt $35.00

Jacket
Pants
Shirt
Suit 425.00

150.00 90.00 25.00

E

E-72

SPREADSHEET MODULE

Project 14 Projected vs. Actual Expenses: Create the spreadsheet below that compares projected and actual expenses by calculating a percent difference between the two. Calcu- late totals for the first two columns and an overall percent difference. Format to match the spreadsheet below. Percents should show one decimal place. Save the file as SHEET14 and print it.

PROJECTED VS. ACTUAL COMPARISON

PROJECTED ACTUAL

ITEM EXPENSE EXPENSE

RENT $4,000$4,000.00 ELECTRICITY 2,400 3,245.15 SUPPLIES 7,500 7,230.95 INSURANCE 2,000 2,150.00 CONSULTANTS 3,000 2,500.00 TOTAL:

PERCENT

DIFFERENCE

Project 15 Accounting Example: Using material from an accounting course, create a balance sheet, trial balance, or income statement. Include a copy of the original. Print and save the file as SHEET15.

 

"Looking for a Similar Assignment? Order now and Get 10% Discount! Use Code "Newclient"