A Second Data Table

<div dir="ltr" style="text-align: left;" trbidi="on"> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> We'll do one more Data Table, just so that you get the hang of things. This time, we'll use a more simple formula than PMT, and we'll use Rows instead of Columns. This is the scenario:</div> <div class="TextIndent3" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif; margin-left: 5em;"> You have 250 items that you want to sell on EBay. Your unique selling point is this - All items are only £5 each! Except, you feel £5 may be a bit expensive for the goods you're selling! What you want to know is how much profit you'll make if you reduce your prices to £4.50, how much if you reduce to £4.00, and how much for a reduction to £3.50. Assume that everything gets sold.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> To start creating your Table, construct a spreadsheet like the one below. Make sure that you start on a new sheet.</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Data Tables in Excel 2007" height="120" src="http://www.homeandlearn.co.uk/excel2007/images/2TableTwo.gif" width="408" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> In cell B1 is the number of items we want to sell (250). Cell B2 has the original price (£5.00). And the <b>Reductions</b> Row has our new values. Cell B3 has a 0 because there's no reduction for £5.00. Row 4 is where our Profits will go.</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <ins style="background-color: transparent; border: none; display: inline-table; height: 60px; margin: 0px; padding: 0px; position: relative; visibility: visible; width: 468px;"><ins id="aswift_0_anchor" style="background-color: transparent; border: none; display: block; height: 60px; margin: 0px; padding: 0px; position: relative; visibility: visible; width: 468px;"><iframe allowtransparency="true" frameborder="0" height="60" hspace="0" id="aswift_0" marginheight="0" marginwidth="0" name="aswift_0" scrolling="no" style="left: 0px; position: absolute; top: 0px;" vspace="0" width="468"></iframe></ins></ins></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The formula to work out the profits is simply the <b>Number of Items</b> multiplied by the<b>Price Per Item</b>. So click inside cell B4 and enter the following formula:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>= B1 * B2</b></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Your spreadsheet will then look like this:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Profits in cell B4" height="120" src="http://www.homeandlearn.co.uk/excel2007/images/2TableTwo2.gif" width="419" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> So if we manage to sell all our items at £5, we'll make £1,250. We're a bit dubious, though. Realistically, all our items won't sell at this price! Let's use an Excel Data Table to work out how much profit we'd make at the other prices.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Again, we put the answer in cell B4 for a reason. This is because when you want Excel to calculate a Data 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.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Next, click inside cell B3 and highlight to cell E4. Your spreadsheet should now look like this one:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Highlight the cells B3 to E4" border="1" height="119" src="http://www.homeandlearn.co.uk/excel2007/images/2TableTwo3.gif" width="426" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Excel is going to use our 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 for us. To create a Data Table then, do the following:</div> <ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <li>From the Excel menu bar, click on <b>Data</b></li> <li>Locate the <b>Data Tools</b> panel</li> <li>Click on the "What if Analysis" item</li> <li>Select <b>Data Table</b> from the menu</li> </ul> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="The What-if Analysis menu" height="136" src="http://www.homeandlearn.co.uk/excel2007/images/2DataToolsWhatIf2.gif" width="161" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Just like last time, you'll get the Data Table dialogue box. The one we want now, though, is Row Input Cell. But what is the Input Cell this time?</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Ask yourself what you are trying to work out, and what you want Excel to recalculate. You want to work out the new prices. The formula you entered was:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>= B1 * B2</b></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Excel is going to be changing this formula. You only need to decide if you want Excel to alter the B1 or the B2. B1 contains the number of items; B2 contains the price of each item. Since we're trying to work out the profits we'd get if we change the price, we need Excel to change B2. So enter B2 for the Row Input Cell:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="The Data Table dialogue box in Excel 2007" height="123" src="http://www.homeandlearn.co.uk/excel2007/images/2TableTwo4.gif" width="213" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> When you click OK, Excel will work out the new profits:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="The new profits are on row 4" border="1" height="119" src="http://www.homeandlearn.co.uk/excel2007/images/2TableTwo5.gif" width="440" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> So setting a price of £3.50 per item, you'd make £875 profit. You'd make £1,000 at £4.00 per item, and £1,125 if you sell for £4.50.</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <br /></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Hopefully, Data Tables weren't too difficult! But they are a useful tool when you want to analyse values that can change. In the next section, we'll take a look at scenarios.</div> </div>

A Second Data Table

We'll do one more Data Table, just so that you get the hang of things. This time, we'll use a more simple formula than PMT, and we...

Read more »