Lab Four

Excel Spreadsheets

Due March 15th

Create a Yearly Budget, Linking sheets, and using Goal Seeker.

Sheet One, Budget:

• Include at least six items in your budget. i.e. Rent, food, etc. This can be real or fictional data.

• Your budget needs to be for 12 months, with the name of the month centered at the top of each column.

• Use a formula to show a total for the costs for each month.

• Use a formula to show a total for the amount spent on each of the items for the year.

• Show income for each month, and then subtract the cost to give you the money left over at the end of each month.

• Format the entire first sheet as money as currency with negative amounts in red (don’t just make the color red). Make sure some months have a positive balance and others with negative. Make sure you do this for the entire first sheet, so that anytime something is changed it will be correct.

• Use at least three different fonts and three different sizes of fonts.

• Use the border feature to make the spreadsheet more readable. Example, lines above totals and under headings.

• Create a Header with a title and a Footer with the page number centered and date in the lower right corner.

Sheet Two, Summary:

• Create a new sheet that summarizes the yearly budget, name it Summary.

• Create a link for the yearly totals for all six of the items, to the data on the first sheet. Meaning, it will look to the first sheet to get the values and will then display the values on the second sheet, if something is changed on the first page it will show on the second page.

• Create a chart on the “Summary” sheet that shows how the money is being spent. Use a pie chart and show the percentages for each item for the year.

Sheet Three, Payment Options

• Create a third sheet that will allow you to determine loan payments.

• Show the amount of the car, down payment, the interest rate, and the length of the loan. Calculate the amount of the loan.

• Use the PMT function to get the monthly payment. Here is an example =-PMT(.05/12,60,20000) i.e. 5% a year, for 60 months and a total amount of $20,000. Here are a couple of things to watch out for, I put a ‘-‘ before the formula, otherwise you will get a negative number. Also, make sure you divide the interest rate by 12, because the percentage is based on a year. You will need to use the cell references not the numbers like shown here in my example. By using the cell references, you can make multiple copies for the needed five options.

• Use the ‘Goal Seeker’ feature to create help to create different options. In the Windows Version the Goal Seeker is it is under the Data tab and then under the “What-If-Analysis” button.

• Show five different payment options on your third sheet.

Save this file as an Excel file and name it yourlastname4.xls, and e-mail to dgomez@csustan.edu. Make sure you include Lab 4 CS4000 in your subject line. Come into the lab and show me your Spreadsheet on either March 18^th or 20^th.