Goal Seeking

If we know the result that we want a formula to produce then we can use goal seeking to determine the value of a cell on which that formula depends.

Problem 1

You’ve just realised that it’s your mother’s birthday in five weeks time. You have found the ideal gift for $100. You don’t have a penny on you at the moment, but you work at the local grocery store for 12 hours a week and make $4.50 an hour. Since you have been keeping track of your accounts for the last six months you know that your expenses (food, rent, etc.) generally use up 80% of your wages.

Create an Excel worksheet to work out:

- The total amount of money you will make in 6 weeks

- Your total expenses for six weeks

- The amount of money that you will have left over for the gift.

Unfortunately, the amount of money that you have left over will not be enough for that perfect gift.

Use the (Goal Seek) command from the (Tools) menu to figure out:

- The number of extra hours a week you will need to work to reach your goal

- If you choose not to work any extra hours then you will need to lower the percentage of your salary that you use on expenses. What will this percentage now be?

NPER Financial Function

Another useful financial function is NPER, which calculates the number of periods for an investment based on periodic, constant payment and a constant interest rate.

Problem 2

Based on your budget and salary, you have worked out that you will have $1,000,000 (yes one million dollars) when you retire. You estimate that you will draw out $50,000 a year to live on and the remainder will go into a bank account with a 4% interest rate compounded yearly (yearly interest rate).

Part I: Use the NPER function to work out how many years it will be before you run out of money.

Part II: Create a payment or amortization schedule for this period and verify that the result of the NPER function is correct. Your table should contain columns for beginning balance, amount withdrawn, interest added and final balance for every payment period.

Question: Is the payment period in months or years?

Part III: Add a chart to your worksheet showing the decline in your balance till it reaches zero.

More Formatting

We talked a little bit about formatting during our first class meeting. We know how to format numbers in cells.

There is much more to formatting than just this. You can format the alignment, fonts, borders, and background colours of cells.

Problem 3 [SK1]

Consider the following table of data representing sales for various branches of a bookshop in England.

Ye Oldie Book Shoppe

2004 Sales

	Colchester	Oxford	Cambridge	Liverpool
Fiction	3230	4530	2150	230
Biography	5300	6390	6299	549
Reference	3599	7395	3053	912
First Edition	9232	11003	2351	1052
Children	5234	5372	2692	384

Part I: Grab the above information and place it in an Excel document.

Part II: To center a title, follow the steps below:

- Select the cell with the text

- Modify the text's font, size, color

- To center, select the range of cells and go to the cells option in the format menu. In the alignment tab, check the merge cells box, and change the horizontal alignment to center.

Part III: You need to format the table data. You can either format it yourself by changing the fonts and background colour (which is what I did), or you could use the Excel AutoFormat feature.

AutoFormat uses predefined Excel formatting features as follows:

- Select the range of cells to be AutoFormatted

- Go to Format on the menu bar and select AutoFormat

- Select the AutoFormat option of choice

Part IV: Insert a picture. You can insert a picture anywhere in your spreadsheet. To do this, go to the insert menu and select picture then either (picture from file) if you have your own picture that you want to use, or (clipart). I used a clipart picture. Don’t worry if you can’t find exactly the same picture. Just pick one that you think looks good.

Part V: Insert a chart to visually depict the data.

The goal is to produce the following worksheet:

Printing

Let’s have a look at what the spreadsheet would look like if we printed it out. Click on the (Print Preview) toolbar menu.

You will probably find that you will need to make some adjustments before you can print the page. You might need to change the page orientation from portrait to landscape. You also might need to adjust the margins. You can do this by going into the Page Setup menu item in the file menu.

One of the useful features under the margins tab is the centre horizontally and centre vertically option. This centres your spreadsheet in the middle of the page.

After your adjustments, the bookshop spreadsheet should look like the following in (Print Preview):