Introducing PowerPivot for Excel 2010

What is PowerPivot
 
PowerPivot is a free add-in to the 2010 version of the spreadsheet application Microsoft Excel. In Excel 2013, PowerPivot is only available for certain versions of Office. It extends the capabilities of the PivotTable data summarisation and cross-tabulation feature with new features such as expanded data capacity, advanced calculations, ability to import data from multiple sources, and the ability to publish the workbooks as interactive web applications. As such, PowerPivot falls under Microsoft's Business Intelligence offering, complementing it with its self-service, in-memory capabilities.
Prior to the release of PowerPivot, Microsoft relied heavily on SQL Server Analysis Services as the engine for its Business Intelligence suite. PowerPivot complements the SQL Server core BI components under the vision of one Business Intelligence Semantic Model (BISM), which aims to integrate on-disk multidimensional analytics previously known as Unified Dimensional Model, or UDM, with a more flexible, in-memory "tabular" model.
As a self-service BI product, PowerPivot is intended to allow users with no specialised BI or analytics training to develop data models and calculations, sharing them either directly or through SharePoint document libraries.
As part of the July 8, 2013 announcement of the new "Power BI" suite of self-service tools, Microsoft renamed PowerPivot as "Power Pivot" in order to match the naming convention of other tools in the suite.

Microsoft PowerPivot for Microsoft Excel 2010 provides ground-breaking technology; fast manipulation of large data sets, streamlined integration of data, and the ability to effortlessly share your analysis through Microsoft SharePoint. 


How to download and install PowerPivot

Prerequisites:

• Requires Microsoft Office 2010.
• PowerPivot for Excel supports 32-bit or 64-bit machines.
• PowerPivot requires a minimum of 1 GB of RAM (2 GB or more recommended).

Note: The amount of memory you need depends on the PowerPivot solution that you design.

• Requires Windows XP with SP3, Windows Vista with SP1, or Windows 7

 Download PowerPivot Here

Important: If you are using the 32-bit version of Excel 2010, you must use the 32-bit version of PowerPivot. If you are using the 64-bit version of Excel 2010, you must use the 64-bit version of PowerPivot. The versions are not interchangeable. 

Install and configure PowerPivot for Excel 2010:

1. Open the folder where you downloaded PowerPivot for Excel 2010.
2. Double-click the PowerPivot_for_Excel.msi file, and then follow the steps in the wizard.
3. After the installation is complete, click Finish.
4. Click Start, click All Programs, click Microsoft Office 2010, and then click Microsoft Excel 2010.
5. When Excel 2010 starts, a message appears asking if you want to install PowerPivot. Click OK.
6. After installation completes, the PowerPivot tab appears in the Office 2010 ribbon.

In my next post we will see..oh my, you can analyze multiple data tables from Oracle, SQL, Excel, Text, Access and Much more!

Let's go.

Load or unload add-in programs

There are different types of add-in programs for Microsoft Office Excel that provide optional commands and features: Excel add-ins, such as the Analysis ToolPak and the Solver Add-in, are available when you install Microsoft Office or Excel. Additional add-ins for Excel can be downloaded from Downloads on Microsoft Office Online. Developers and solution providers usually design custom Component Object Model (COM) add-ins and automation add-ins. You must load an add-in before you can use it. After loading the add-in, the add-in program and its commands are available in one of the following locations: Data tab. The Analysis ToolPak and Solver Add-in are displayed in the Analysis group. Formulas tab. The Conditional Sum Wizard, Euro Currency Tools, Eurotool, and Lookup Wizard are displayed in the Solutions group. Add-Ins tab. The Add-Ins tab is added to the Ribbon, a component of the Microsoft Office Fluent user interface, when you load the first add-in for that tab. If you don't see the Add-Ins tab, you should exit Excel and restart it. Let us tell you how. Go to OpenCastLabs to learn more

Advanced Microsoft Excel Training at OpenCastLabs in Kenya, Uganda, Tanzania, Rwanda and Burundi

This is a unique post. It is a promo, it is an info source for you my dear reader. OpenCastLabs Consulting is a specialized training company operating in all the 5 Eastern African Countries commonly referred to as Eastern African Community. We specialize in Microsoft Excel as a productivity tool. Our classes are either monthly or a re conducted in your premise. Contact us here () for us to send you a no-obligation quotation for persons not less than 5 per group. You can also check our course listing here or a sample course on Advanced Excel for Finance Professionals () and Budgeting and Forecasting with Microsoft Excel () Hit me up anytime on info(@)opencastlabs.co.ke or call/SMS +254 722 831 259 Any time go to

How to develop a Simple Time Tracking Sheet in Microsoft Excel

The process of tracking time is unique to every employee or position, so there’s no one-size fits-all sheet that will do the job. Still, certain principles and features will generally play into most timesheet models. For instance, before you start formatting cells and entering formulas, you need to decide why you’re tracking time, who’s going to use that information, and how:

1. Decide on a time period. (Management will probably make this decision for you.) Most of us track hours weekly, biweekly, or monthly.
2. Tracking just time isn’t always enough. Many companies bill your hours back to clients. Therefore, you must allocate your time to specific projects. This article will show you how to create a weekly timesheet you can easily adjust to track biweekly and monthly times and projects. As is, this sheet doesn’t document breaks or allow for flexibility in scheduling. However, the basic structure and formulas are present, so you can easily customize the sheet to fit your needs.

This article will show you how to create a weekly timesheet you can easily adjust to track biweekly and monthly times and projects. As is, this sheet doesn’t document breaks or allow for flexibility in scheduling. However, the basic structure and formulas are present, so you can easily customize the sheet to fit your needs.
Read more: http://opencast-labs.com/downloads/How_to_Build_a_simple_timesheet_in_Excel.pdf and the sample files here http://opencast-labs.com/downloads/TimeSheetExcel.xls

Adopted from TechRepublic

How to use the awesome FORECAST Function in Excel

The FORECAST function is a rare function. In my few years of teaching Excel, none - even the most seasoned Excel users have never asked me how to use this rather mysterious function. I have always included a sub-topic on forecasting in my classes but end up showing professionals how to insert a graph, then a linear trend line and finally how to show the R coefficient and the Linear equation which is commonly used to build Forecasts.
Though FORECAST is a powerful tool, it cannot independently be used to generate forecasts. A good forecast system involves dealing with seasonality, noise, trends and events. So what does this mean?
Linear regression is a form of regression analysis and can be used to calculate a mathematical relationship between two (or more) sets of data. In forecasting, you would use this if you thought one set of data could be used to predict another set of data. For example, if you sold building supplies, you may find that changes in interest rates can be used to predict sales of your products. This is a classic example of using regression to calculate a relationship between an external variable (interest rates) and an internal variable (your sales). However, as we will see later, you can also use regression to calculate a relationship within the same set of data.
The FORECAST(x, known_y's,known_x's) function returns the predicted value of the dependent variable (represented in the data by known_y's) for the specific value, x, of the independent variable (represented in the data by known_x's) by using a best fit (least squares) linear regression to predict y values from x values.
If you assume that data pairs are plotted in a scatter plot with x values that are measured on the horizontal axis and with y values that are measured on the vertical axis, FORECAST returns the height of the best fit regression line at the specific value x on the horizontal axis. FORECAST is the value of y that would be predicted based on both the value of x and the regression line (characterized by its slope and intercept that can be found by using Excel's SLOPE and INTERCEPT functions).
In the figure below, we have a spreadsheet that includes the average interest rate over the previous 6 years and unit sales during that same 6-year period. We also show a predicted interest rate for the 7th year. We can see in the example that our unit sales go up as interest rates come down, and go down as interest rates go up. Just looking at the example, we can probably guess that our sales for year 5 would be somewhere between 4,000 and 5,000 based on the observed relationship between interest rates and sales during the previous periods. We can use the Forecast Function to more precisely quantify this relationship and apply it to the 7th year.

In the figure below, you can see the Forecast Function being applied. In this case, the formula in cell H3 is =FORECAST(H2,B3:G3,B2:G2) . What we have within the parenthesis is known as an “argument”. An argument is really just a means of passing parameters on to the function being used (in this case, the Forecast function ). Each parameter is separated by a comma. In order for the Forecast Function to work, it needs to know the value we are using to predict our output (our year 6 sales). In our case, the parameter (our Year 6 interest rate) is in cell H2, so the first element of our argument is H2. Next, it needs to know where it can find the existing values it will use to determine the relationship to apply to H2. First we need to enter the cells that represent the values of our dependent variable. In our case, this would be our units sold during the previous 6 years, therefore we enter B3:H3. Then we need to enter the cells that represent the values of our predictor variable. In our case, this would be the interest rates during the previous 6 years, therefore we enter B2:H2) .
The Forecast Function can now compare the units sold during years 1 through 6 to the interest rates in those same years, and then apply that relationship to our predicted Year 6 interest rate to get our forecasted sales for Year 7 of 4,523 units.
Download Excel Workbook