computer

ASSIGNMENT: CMSPC-1513 – MODULE 5 – SPREADSHEETS

ASSIGNMENT:
This assignment will cover practicing the basics of spreadsheets with two types of sheets you might use in your
studies or personal life. Use this opportunity to get familiar with using the spreadsheet tools to enter and format
data in ways that makes the data easy to review and kept up to date using the power of formulas and
functions.

Follow the instructions included below to produce two spreadsheets:

1. Checkbook Registers (sslab1)
For this sheet, sslab1, is a simple checkbook register that should follow the detailed instructions below
including format and formulas. The goal is to keep a running total of how much money is in the account
as transactions change over time.

2. Your Choice
The second spreadsheet can be any of your choice, but you must include the basic topics and functions
as-instructed below.

Submit both sheets via Blackboard for full credit.

Note: Instructions are written for Microsoft Excel but may be completed using any spreadsheet software. The goal is to
demonstrate the ability to complete the task requested in whatever spreadsheet software you choose.

Document instructions below (↓)

Spreadsheets Part 1 (sslab1, Check Register):

This activity requires you to create a new spreadsheet and perform basic tasks such as entering labels and
values, formatting, relative replication, etc. Follow the instructions exactly for full credit.

1. Start new spreadsheet

2. In cell A1, type CHECK # and press ENTER

3. Change the width of column B to 20 characters and center PAYEE in cell B1.

Note: This is 20 characters but not all software packages use the same unit of measure so just be sure
to achieve the desired result using whatever spreadsheet software you use.

○ Move the cell pointer to cell B1
○ On the Home menubar, select Format, then Column Width
○ Enter 20 and click OK
○ Type PAYEE in cell B1 and press ENTER
○ Click on the align center icon

4. Add the labels AMOUNT in cell C1, DEPOSIT in cell D1, and BALANCE in cell E1

5. Right justify AMOUNT, DEPOSIT, and BALANCE:
○ Select cells C1:E1
○ Click on the right align icon

6. Make all of your headings bold.
○ Use your mouse to highlight all of the column headings (A1:E1)
○ Click the Bold icon on the toolbar

7. Enter the following transaction information in rows 2-9 as shown:

A B C D E

1 CHECK # PAYEE AMOUNT DEPOSIT BALANCE

2 1000.00

3 101 PEC 78.50

4 102 Wal-Mart 102.50

5 103 Sam’s Southern Eatery 12.64

6 Insurance (debit card) 50.00

7 104 AT&T 36.79

8 200.00

9 105 Cable 37.57

8. Left justify the check numbers:
○ Use your mouse to select the range A2:A9 and click the align left icon

9. In cell A10 type in TOTALS press ENTER
○ Then select cell A:10 again and click on B (near Fonts) to bold the label

10. Format columns C, D, and E to show currency to 2 decimal places
● Block-select all the cells you want to format (C2:E10)
● Then click on Number → Currency (and set decimal places to 2 if needed)

11. Put your name in cell D12

12. Put borders around the cells of the spreadsheet in range A1:E10
○ Use the mouse to highlight cells A1:E10
○ Click on the Borders button (in the Fonts section)
○ Under Line Arrangement, select “All Borders”

13. To keep the balance of the register, we will use a mathematical formula to compute the balance:
○ We will assume that this is a new account and there was not a balance in the account when the

$1000 was deposited; therefore, enter 1000 in cell E2
○ In English, the formula that we need to compute the balance for each line would read like this:

New balance = old balance – checks written + deposits
○ Translated into cell references, the formula is: E3=E2-C3+D3
○ Therefore, in cell E3, enter the formula =E2-C3+D3 and press ENTER

■ Alternatively, you can type the = in cell E3, then click cell E2, enter the -, click cell C3,
enter the +, click cell D3, and press ENTER

○ Check your work: the value $921.50 should appear in cell E3

14. Copy this formula down the E column for all cells between row 3 and 9:
○ Option 1: Click on cell E3, Select Copy, highlight cells E4:E9, select Paste
○ Option 2: Use your mouse to click on the lower left hand corner of cell E3 then click and drag

down to cell E9 to use relative copying for each cell selected.
○ Check your work: the value in cell E9 should be $882.00

15. It seems you got excited and purchased a used computer from a friend at a really great price. You then
decided to network your old and new systems together, so you purchased additional computer
equipment from the local computer shop. In addition, you stopped off at the local watering hole and
bought a couple of cases of refreshments to help you hook up and re-configure your new network.

Unfortunately, you neglected to enter these transactions. When you realized what you had done, you
had to make an immediate deposit to cover your expenses. Because all the surprise expenses were
after the check to the cable company, you will need to do the following steps to find your correct
balance:.

● Insert 4 blank rows after the check to the cable company (row 9) and before the TOTALS line:
○ Move the cell pointer to select row 10 and select Insert and/or Sheet-rows four times

16. Enter the new transaction information in rows 10 – 13 as shown below. You will compute the TOTALS
later:

10 106 Computer (Friend) 817.86

11 107 IT Land 259.19

12 108 Harry’s Market 25.00

13 225.00

14 TOTALS

17. If any of the values in columns C, D, or E do not have 2 decimal places, format them

18. Now you need to complete the balance column by copying the formula on down through cell E13:
○ Click into cell E9 and drag the formula down through cell E13
○ (Or cut and paste the formula through the cells)
○ Check your work: the balance should be $4.95 in cell E13

19. Calculate the TOTALS in cell C14 and D14:
○ Move the cell pointer to cell C14
○ Type =SUM(C2:C13) and press ENTER.
○ Copy the function from cell C14 by clicking Copy, then click into cell D14 and then click Paste
○ Check your work:

■ Cell C14 should show $1,420.05 in spending
■ Cell D14 should show $1425.00 in deposits

20. Put a thick border to separate the TOTALS line from the rest of the worksheet:
○ Select cells A13:E13
○ Click Borders (under Font), then “Thick Bottom Border”

21. Use a process similar to the one above to put a thicker border to separate the column headings:
○ Select cells A1:E1
○ Click Borders (under Font), then “Thick Bottom Border”

22. Save the worksheet file as sslab1

For reference, a completed sheet should look like this:

Spreadsheets Part 2 (your choice):

This activity requires you to apply your newly acquired/reviewed spreadsheet skills in something useful for you
or someone you know in a real-world scenario.

You are not required to include all covered topics, but you must include the following:

1. Column labels
2. Row labels
3. At least 5 rows of data
4. Sum of at least one column or row
5. Calculate with a function besides SUM.

○ (Suggestion: use AVERAGE, MAX, or MIN)
6. Resize columns wide enough to view all data and text
7. Save the file as something notable (spreadsheet2?)

Note: You only need to demonstrate use of a spreadsheet in a real-world application. Perhaps you could do a personal
budget or one for a club or team. You could use data you recorded from an experiment or a survey or data from another
course. Be sure everything is neat and easy to read.

Submit Both Files via Blackboard

The completed spreadsheet files from both parts of the activity should be attached to a single submission via
the activity link in original format (ie .xlsx, .ods, or default format for spreadsheet software used).