Part Two of Excel and Tables

 - If you missed the first part of this tutorial, it can be found here: Excel Tables, Part One.


In the previous part, we saw how to use an Excel Table to work out interest rates. The Table was used to fill out several values at once. We'll now see how to use a Table to examine profir margins. Here's the scenario:
You take 250 items to a Car Boot sale. Your Unique Selling point is this: All items only a pound each! Except you feel that one pound might be a bit expensive, especially for the goods you're selling! What you want to know is how much profit will you make if you reduce your prices to ninety pence each, how much if you reduce to 80 pence each, and how much if you reduce to 70 pence each. Assume that everything gets sold.
To start creating your Table, construct a spreadsheet like the one below. Make sure that you start on a new sheet.

Number is the number of items we're taking to the Car Boot sale. Price will be used in our formula. Reductions is the amount we want to reduce by. Sales is the amount of profit we expect to make. Our formula will go in cell B4. So click inside cell B4 and enter the formula:

= B1 * B2

The answer is, of course, 250. But the answer is in cell B4 for a reason. This is because when you want Excel to recalculate a Table in Rows, the formula must be inserted one Column to the Left of your first new value, and then one Row down. Our first new value is going in cell C3. So one column to the left takes us to the B column. One row down is Row 4. So the formula goes in cell B4.

Next, click inside cell B3 and highlight to cell E4. Your spreadsheet should now look like the one below:

Excel is going to use your formula in cell B4. It will then look at the new values on Row 3 (not counting the zero), and then insert the new totals starting in cell C4.

So with the data highlighted, click on Data from the menu bar
From the drop down list, click on Tables
The Table dialogue box appears

We want Excel to fill our new values in Row 4, so we need the Row input cell. But what is the Input Cell this time?

Ask yourself what you are trying to work out, and what you want Excel to recalculate. You want to work out the new prices. So you need the old price. The old price of each item was one pound, and you used this in the formula.

• So on the Table dialogue box, click inside the "Row input cell" text box
• Enter your cell reference
• Click OK when you are done
• If you got it right, your spreadsheet will look like the one below:

So, if you don't reduce your prices, you will make two hundred and fifty pounds. If you reduce the price of each item to seventy pence, you will make one hundred and seventy five pounds.
Tables are a useful tool when you want to analyze values that can change. Before moving on, try this exercise.

Exercise

Amend the table above. This time, instead of reducing your prices, you are going to increase the price to £1.25 per item, and increase the number of items you sell. You currently sell 250 items. Work out the new profits if you sell 300 items, 350 items, and 400 items.

In the next part, we'll move on to Scenarios.