Spreadsheets Lab 4 1. Open a new Excel spreadsheet document. First, we will add some data to it. Copy and paste the data in the table below into your spreadsheet. Save the file as lab4 on your H drive in lab. Assume that you have just been given a part-time job at USC. You will receive $405.00 on the 1st and the 15th of each month, starting February 1. You have also decided to move into an apartment with your friends. Your share of the rent is $300.00 due on the first day of each month. Make up data to continue the spreadsheet for three more months. Remember to include your paycheck and regular expenses such as your share of the rent, electric bill (SCEG), cell phone bill (AT&T), gas (Shell), food, books, clothes, entertainment, etc. Include at least eight entries for each of the additional months. Remember to save the file frequently. Year Month Day Check # Destination/ Explanation Withdrawal Deposit Balance Origin Starting $837.50 Balance 2010 2 4 Uncle David Graduation $200.00 2010 2 6 Aunt Jane Graduation $25.00 2010 2 9 406 Foot Locker Tennis shoes $72.33 2010 2 12 407 Office Dpt Supplies $48.98 2010 2 15 408 SCEG Utilities $146.52 2010 2 20 409 AT&T Phone $74.29 2010 2 21 ATM Shell Gas $35.37 2010 2 22 ATM Cash Atlanta trip $150.00 2010 2 24 Mom For textbooks $300.00 2010 2 25 410 Target Alarm clock $15.42 2010 2 30 ATM Cash $100.00 2. Modify your spreadsheet as follows. 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. b) Making corrections: You just remembered that you did not enter an ATM withdrawal of $50.00 on 02/14/2010. Also, the money that your mom gave you for the textbooks was deposited on the 02/18/2010, not 02/24/2010, so that entry needs to be moved up and the date changed. Make these corrections. Update the balance column as necessary. 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. d) You are thinking of buying a netbook to use for schoolwork now that you have a job. Use the IF function to determine whether you can afford the netbook based on the first three months of your checkbook activity. If your minimum balance over the first three months does not drop below $400, output a message which indicates that you can afford the netbook. If not, output a message which indicates that you cannot afford the netbook now. e) You want to monitor your cell phone expenses. Add a filter for the category Destination/Origin. Select “AT&T” as the filter criterion to view only your cell phone expenses over the three months. Leave the data in filtered form when you submit your file. f) To present your spreadsheet in the final form, select format options (currency, headings, etc.) appropriate for each element. Also incorporate elements of formatting like different types of font, alignment, borders, and background color for some of the cells.