CSCE 101 Introduction to Computer Concepts
Lab 5
Spreadsheets
1. Open a new spreadsheet. We will be using this spreadsheet for subsequent assignments. First, we will
add some data to it. Assume you have just been given a part-time job at USC. You will receive
$300.00 on the 1st and the 15th of each month, starting February 1st. You have also decided to move into
an apartment with your friends. Your share of the rent is $200.00, due on the first day of each month.
Create a mock checkbook registry starting with the one given below. Set up your spreadsheet, and
enter the data below. Include the money from your mom and the rest of your expenses for the month.
Remember to include your regular expenses including your share of the electric bill (MCEC), phone
bill (AT&T), gas (Sonoco), food, books, clothes, entertainment, etc. Make up data to continue the
spreadsheet for two more months in addition to what is given below. Include at least nine entries for
each of the additional months. (30 Pts.)
Year Month Day Check
#
Destination/
Origin
Explanation Withdrawal Deposit Balanc
e
Starting
Balance
$700.50
2008 1 6 Uncle James Birthday $100.00
2008 1 7 Aunt Sally Birthday $50.00
2008 1 10 806 Foot Locker Running shoes $90.22
2008 1 15 807 Office
Depot
Supplies $60.98
2008 1 17 808 MCEC Utilities $75.63
2008 1 19 809 AT&T Phone $45.35
2008 1 21 ATM Sonoco ATM card $43.46
2008 1 22 ATM Cash Charleston trip $150.00
2008 1 25 Dad For textbooks $500.00
2008 1 27 810 Target Alarm clock $15.42
2008 1 28 ATM Cash $200.00
2. Modify your spreadsheet for the checkbook register using the specifications given below.
a) Calculate the balance for each entry. Enter the formula in the cell under the initial balance to
calculate the balance of the first entry. Since empty cells will be treated as zero, your balance
formula can simply add the deposit and subtract the withdrawal from the previous balance. Use
the automatic fill handle technique to apply the formula to all the rest of the cells in the balance
column. It is easy to enter a formula incorrectly, so always spot check your answers. (10 Pts)
b) Making corrections: You just remembered that you did not enter an ATM withdrawal of $75.00 on
01/14/2008. Also, the money that your dad gave you for the textbooks was deposited on the
01/18/2008, not 01/25/2008, so that entry needs to be moved up and the date changed. Make these
corrections. Do you need to make any adjustments to the balance column? The balances should
have automatically changed, but you inserted a row, so make sure that the balance was calculated
for the newly inserted row. (10 Pts)
c) For each month calculate the maximum balance, the minimum balance, the average balance, total
expenses, and the total amount deposited. Use the MAX, MIN, AVERAGE, and SUM functions.
(10 Pts)
