Use Excel 2010 & SharePoint 2010 To Build An Interactive What-If Analysis Tool

Products:  Excel 2010, SharePoint 2010

The Challenge
A client requested assistance in providing an interactive what-if analysis tool for their business users.   The tool consisted of a few variable fields and a final estimated cost field.

The Environment
The users had an existing SharePoint site where they managed various types of content during the course of their daily collaborating sessions.   It would be nice if the solution fit nicely within their existing SharePoint Site.

The Solution
Excel has always provided some really great features for entering numbers and formulas for calculating all types of results; and now, Excel 2010 has extended additional capabilities to the browser with SharePoint 2010.

Step #1 – Define The Fields In Excel
* include field descriptions as needed

    • Variables (set default values as needed)
      – Loan Amount
      – Loan Interest Rate
      – Loan Years
    • Calculation
      – Monthly Payment

Step #2 – Name The Areas In The Form

    • Form Area Name (will be used later in SharePoint)
      – Select the cells that comprise the Form Area
      – Select Formulas tab > Name Manager > New
      + Enter a Name
      + Select Scope of Workbook
      + Select OK
    • Variable Field Names (will be used later when publishing the Excel File)
      – Select a variable field
      – Select Formulas tab > Name Manager > New
      + Enter a Name
      + Select Scope of Workbook
      + Select OK
      ** repeat above steps for each Variable Field Name

Step #3 – Save The Form Locally

The Implementation

Step #4 – Publish The Form To SharePoint

    1. Select File > Save & Send > Save to SharePoint
    2. Select the SharePoint Site Document Library destination
    3. Select Publish Options
      1. within the Show tab, select the appropriate option from the drop down
      2. select Parameters tab
        1. add the named areas to be available to the business users
        2. select OK to save
    4. Select Save

Step #5 – Page Configuration

    1. Select Site Actions > New Page
      1. configure as needed
    2. Within Editing Tools tab, select Insert > Web Part
      1. filter Categories to Business Data
      2. within Web Parts, select Excel Web Access
      3. select Add
    3. Select ” … Open the Tool Pane …” link
    4. Web Part Properties
      1. configure Workbook field
        1. navigate to location of Excel File in local Site Shared Documents library
      2. configure Named Item field
        1. refer to the name of the Form Area Name used in Step #2 above
      3. scroll down and select OK to save the properties

The Verification

Step #6 – Test The Solution

    1. Enter values into the parameters
    2. Select Apply button
    3. The parameters will show in the Form Area and the new calculated value(s) will display

Publish Excel Workbook To SharePoint (Read More)
Some Restrictions May Apply (Read More)
Mileage May Vary Based On Driver Experience and Road Conditions (Read More)

How has SharePoint affected you?

This entry was posted in Business Intelligence, Excel, SharePoint. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s