Data
Tables
|
We also have Free
training videos on this site ( visit the
Home page
or the
Training Page)
Excel data tables
Data tables are defined as a range of cells that are used
for testing and analyzing outcomes on a large scale. It is
a way to see how altering the values in a formula affect the
results. Data tables can store the results of multiple scenarios
in your spreadsheet, and saves you time in calculating multiple
formulas. These tables are written in array formulas; this
allows the user to perform multiple calculations in one location.
|
|
| How to set up data tables
There are two different data tables that you can use depending
on what you would like to calculate. Either one-variable data
tables or two-variable data tables both are equally easy to
set up and complete. This step by step guide will assist you
in setting up your own data tables and cutting the time you
spend on calculating scenarios by more than half. |
|
For the purpose of this exercise we will use the example of DVD
rentals. A DVD rental is R20.00, costs for the company is R2000.00.
We want to work out the net profit.
- Open up a new Excel spreadsheet
- Set up a Base Model for the function to work from:
- Cell 1 (C1): will be the rental price: R20.00
- Cell 2 (C2): will be the number of rentals: 100 (any number)
- Cell 3 (C3): total income, add the formula =C1*C2, cell now
reads R2000.00
- Cell 4 (C4): costs: R2000.00
- Cell 5 (C5): net profit: is the formula =C3-C4, cell now reads
0
To determine how many rentals the company requires to show a positive
net profit, therefore you will be using one-variable option of the
data tables:
- Along side the base model set up a column that contains various
amounts that are predicted rentals, example: 100; 150; 200; 250;
300; 325 (from Cell E2 – E7)
- Take your cursor and select the cell from E1 down to E7 and
the cells adjoining to the right.
- From the Data menu in the Excel menu at the top of the window
select Table from the drop down menu.
- A dialogue box will appear with two blocks that require values,
for the purpose of a one-variable data table you will only fill
in one of the fields.
- The two field are: Row input cell and Column input cell; as
you have put your values in a column you will use the Column input
cell field and you will put the cell that corresponds to your
values, ie C2 the number of rentals.
- The other field can be left open as we are only working with
one variable.
- Click OK, Excel will automatically add in the values alongside
- You can also use two-variable data tables, for these tables
you will put the one set of variables horizontally and the corresponding
cell name will go in the Row input cell field in the dialogue
box. The other set of variables will be put in vertically and
the corresponding cell name will be put in the Column input cell
field.
Excel data tables training
For the in depth look at data tables and how it can help your company
with auditing purposes contact the experts in the field, Miricle
Solutions. The staff of Miricle Solutions are experienced in Excel,
Accounting and Auditing and have designed courses to help you get
the most use out of your Excel package and save you time and money.
Miricle Solutions can provide training on data tables, goalseek
and numerous other functions.
|
