Spreadsheets as we all know are indeed a very good way of presenting data,
either for analysis or visual presentations of facts and figures and many
similar tasks. However, there is one limitation that they do so in a very
primitive manner. The best way of visually presenting data in a given
spreadsheet is charts. However, the limitation with Excel charts is that they
are not interactive. You can't drill down or query particular fields.
Businesses and information workers regularly need tools that can aid them in
a variety of tasks including analytics, score-carding or even simpler task of
interactive visual representations.
We show you a sample what-if analysis dashboard using Crystal Xcelsius that
allows you do all of the previously mentioned tasks and more. The software comes
in two versions for standalone usage, Professional and Standard. The former
offers additional features such as exporting
to PDF and animated charts with drill-down capabilities.
|
The software also comes in the 'Workgroup' version the details of which
can be obtained from their website www.xcelsius.com.
Step 1: Importing data
The software not only imports data values from your Excel workbook, but also
imports all the formulae and references between sheets of the same workbook. You
can even export new data models created through actions on your dashboard to an
Excel workbook. The import/export of data is a simple process that only requires
selecting the file for your model.
Practically, you need to worry about nothing at all once you have selected
the workbook on which you want to work on. To import data, click on
Data>Import Model, browse to the Excel file you want to import and click on
OK. The status bar shows 'Model Import Done' on the right hand side.
Drag Column chart to the canvas after you have imported an Excel model and link to the model by selecting appropriate series in the Properties box |
Step 2: Creating charts
Expand the Charts node in the component window that appears to the left of the
drawing canvas. Drag the 'Column Chart' to the canvas. Double click on the
chart to open Chart Properties and click on Series. Click on the '+' button
to add a new series. The button beside X-values input box opens up the imported
Excel sheet. Select the range of values that you want to include. Similarly, you
need to select relevant values from your Excel sheet for labels of X and Y-axes.
Edit the chart title manually or select a cell from the model similarly. Once
all your values and titles have been selected or edited, close the Property box
to return to the canvas.
Step 3: Adding components
Expand the 'Single Value' node in the component window and drag the 'Horizontal
Slider' component to the canvas. Open properties box by double clicking on the
component and choose appropriate cells for title and value. Remember a slider is
representing a single value field so you need to select that cell which contains
a single value depicting a parameter whose value directly affects the values of
a column in the chart.
To add dynamic analysis of column values, add a slider and link it to the cell containing the parameter whose effect needs analysis |
Change the maximum value for slider according to your data and close the
Property box to finish. You can add multiple sliders by simply copying this
slider through Edit>Copy and then pasting it back on to canvas through
Edit>Paste or through contextual menu that opens on right clicking the
canvas. You can then open each of the sliders' property boxes and link them to
different cells representing each of the columns in your chart.
Step 4: Exporting to other formats
With all the sliders done, all you have to do for exporting this to any of the
applications-Acrobat Reader, PowerPoint, Word, and Outlook-is to click on
the respective buttons on the toolbar. The software generates a flash file and
imports it to the format or application of your choice. For example, if you
wanted to give a presentation on the data you just modeled, you can export it to
PowerPoint where your canvas will retain its dynamic value generations based on
the formulae it imported from your Excel model.
The exported model onto a PowerPoint slide retains all the dynamics and interactivity allowing you to show all the scenarios of your analysis |
This was a rather simple demo to get you started with this tool. With
Xcelsius, you can even create (say) pie charts and link each of the regions in a
pie chart to a different column or bar charts with gauges and sliders to even
view the changes that would occur if a parameter is modified. Such a dynamic
panel can be useful for forward tracking, ie analyzing about figures in facts in
the near future-based on the variables and their present values. With the
workgroup edition, you can even connect to a database and view changes in your
dynamic charts in real time. For example, you can set up a scorecard that will
keep updating in real time the number of successful calls a tele-executive has
made in a day, or in some other measured time period. The workgroup addition
adds to the Professional version this particular feature, useful particularly in
an enterprise due to its ability to connect and get values from a database. For
learning more about the projects, check out the online demos. There is even a
Retirement calculator that you can play with.