Learning Center
Plans & pricing Sign in
Sign Out



									Spreadsheets Lab Assignment 3.1: 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 $250.00 on the 15th and the 30th of each month, starting March 15th. You have also decided to move into an apartment with your friends. Your share of the rent is $300.00, due on the first of each month. Your mom is worried that you do not have enough money in your checking account and sends you a check for $600.00 to boost your account to $897.79. 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 (Bell South), gas (Texaco Oil), 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. Year Month Day Check # Destination/ Origin Explanation Starting Balance Birthday Birthday Running shoes Supplies Utilities Phone ATM card Charleston trip for textbooks Alarm clock Withdrawal Deposit Balance $897.79 $100.00 $50.00 $90.22 $60.98 $75.63 $45.35 $43.46 $150.00 $500.00 $15.42 $200.00

2007 2007 2007 2007 2007 2007 2007 2007 2007 2007 2007 2.

2 2 2 2 2 2 2 2 2 2 2

6 7 10 15 17 19 21 22 25 27 28

406 407 408 409 ATM ATM 410 ATM

Uncle John Aunt Jane Foot Action Staples MCEC Bell South Texaco Oil Cash Dad Walmart Cash

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.

b) Making corrections: You just remembered that you did not enter an ATM withdrawal of $75.00 on 02/10/2007. Also the money that your dad gave you for the textbooks was deposited on the 02/16/2007, not 02/25/2007, 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. 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 better car now that you have a job. Use the spreadsheet to monitor the monthly minimum balance for the three months to see if you have $200 for the car payment and a cushion of $400. The IF function will be used for the planning based on the first three

months of checkbook activity. Insert IF statements near the three minimum monthly balances to check the amounts. If a monthly balance is less that $600, print a message in the IF cell that you are below the needed minimum. If the balance is over $600, print a message in the cell that you are OK for that month. Looking at these messages, you will see how your financial plan will be working to include the new car payment. e) Add an autofilter for the checkbook category Destination/Origin. Select “MCEC” as the filter criterion. Leave the data in filtered form when you submit your file. To present your spreadsheet in the final form, select format options (currency, headings, etc.) appropriate for each element. Also incorporate finer elements of formatting like different types of font, alignment, borders, and background color for some of the cells.


g) Save your file. Lab Assignment 3.2 1. The semester is almost over and you have the final exam left to take for your CSCE 101 class. You are counting on getting at least a B in CSCE 101 in order to maintain your GPA above 3.00. Given the following weights and grades for your course assignments and exams, use the Goal Seek tool to find out what you need to make on the final in order to receive an A (>=90) for the course.      2. Test #1: weight 15%, your grade 95 Test #2: weight 15%, your grade 85 Lab: weight 20%, your grade 80 Quizzes: weight 20%, your grade 90 Final exam: weight 30%

Create two charts for each of the following (bullets a and b). Use a different type of chart for each of the four charts you create. Select types of chart that will represent the data clearly. Give each chart a title, and insert axis labels so that your spreadsheet information is presented in an easy-to-understand fashion. You can put your charts on multiple sheets and name these sheets or you can place all graphs on the same sheet. a) Show the average balance for the three months b) Show the minimum and maximum balances for the three months Hint: Select the data that you want to chart. In some cases you will need to select non-adjacent cells of data. Then Choose Insert > Chart to open the dialog box that helps you in creating the graph. Incorporate the following formatting elements into any of your graphs. You can distribute these requirements over your charts as you wish.       Change the color of the chart area to something other than white Change the color of the plot area to something other than gray or white. For 3D charts, change the color of the chart walls instead. Use a texture as the fill effect for the chart area Change the type of the font used for chart labels Change the orientation of the data labels for one of the chart axes Change the color of one of the data series to something other than the default color


Your parents have asked you to e-mail a copy of your checkbook to keep on record. You don’t mind sending them the file but you don’t want anyone besides your parents to be able to open the spreadsheet file. To achieve this, you need to password-protect the file for viewing and share the password only with your parents. You also do not want your parents to modify your checkbook or to

view how you spent the money. They should only be able to see your balance history and relevant charts. In this case, you need to password-protect file modification and your checkbook’s content and structure. Complete the following three tasks to achieve the level of protection described above. a) Password-protect the file for viewing. Set this password to your username. Remember to use all lower-case letters.

b) Password-protect the file for modification. Set this password to your username followed by the number 2. Again use all lower-case letters. c) On the main worksheet of your spreadsheet file (the worksheet containing the balance table and charts), hide the columns for check #, destination/origin, explanation, withdrawal, and deposit. Move the charts to the right hand side of the sheet as necessary so that they are not hidden. Highlight the cells containing the balance formulas, right-click and select Format Cells. Click on the Protection tab and check the box next to Hidden. Password-protect the worksheet’s structure and objects using the Protection option under the Tools menu. Set the password to your username followed by the number 3. Remember to use all lower-case letters.

To top