Embedded Files
This collection of training videos will build on the skills learned in the Basic Training and provide the knowledge to use the advanced features of Active Planner.
This video builds on what you learned in Plan Calculations Basics and focuses on retrieving values from other plan sheets. We cover all 3 types of plan sheet look-up formulas: PS-Acct.# (from a specific sheet based on Account number), PUB DATA (from all published sheets based on Account number) and PS-Row Id (from a specific sheet based on Row ID). We finish by creating an Assumptions sheet to hold our business drivers and utilize these to compute our budget values. Length 45:21
Sections allow you to manage a range of accounts in your plan sheet. Calculations, cell formulas and formatting is uniformly applied to each row in the section. Sections can be regenerated to automatically insert new account numbers that may have been added since the section was initially created, thus providing easy maintenance of the rows in your plan sheet. We cover the steps of creating sections, as well as maintaining, copying and deleting sections. Length 16:55
Templates are a powerful feature that enables you to quickly and easily derive many plan sheets from a single template, with each derivative sheet having the same look and feel as the template. It also gives you the ability to modify the template and regenerate the derivative sheets to push out the new template definition. We cover the steps of turning an existing sheet into a template, of defining the derivative sheets and generating the derivatives. Length 40:03
Dimensions are the elements that define how an organization plans and operates. Typical dimensions used in the budgeting process are: divisions, departments, accounts, employees, assets, customers, salespeople, territories, or product/service lines. This session focuses on the basics of creating a dimension with one dimension attribute and then applying the dimension to a plan sheet. Length 25:40
Dimensions Part 2- Tiered Dimensions
This video builds on what we learned about building Dimensions and takes it to the next level. Often we a need to combine 2, 3 or 4 dimensions together in a logical fashion that maintains the hierarchical relationship between them. We will be combining the Territories and Customers Dimensions to create a tiered dimension. The tier defines all the possible combinations of customers that belong to the territory. Then we apply the Tiered Dimension to a plan sheet. Length 19:17
This video builds on what we learned about building Dimensions and Tiered Dimensions. We will create a multi-segmented Dimension, which is an alternative to Tiered Dimensions. We will recreate our Territory and Customer Tier using a standard Dimension that has 2 segments. The first segment will be the territory, the second segment the customer, and then we will apply the segmented Dimension to a plan sheet. Length 10:48
This video builds on what we learned about Dimensions by examining the Dimension Attributes. We will look at both Standard Attributes and Linked Attributes. Length 6:17
Applying Dimensions & Attributes to a Salary Planning sheet
This video builds on what we learned about Dimensions and Dimension Attributes as they apply to Salary Planning. We will build a new Employee Dimension with salary and date attributes, and use this dimension to create a simple Salary Planning sheet. You will learn how to apply attributes to a plan sheet and utilize them in cell formulas to compute the period budgets. Length 25:28
In this training session, we apply the concepts learned in the previous sessions. Many customer like to plan as an annual amount and then use various spreading methods to distribute across the monthly periods. We will create some spread methodologies and apply them to the plan sheet in such a way as to allow the line manager to choose which method to apply to the various accounts. Length 33:38
OLAP Cubes and Excel Pivot Tables
One of the advanced features of Active Planner is the ability to create OLAP Cubes from the Budget and Actual data in the plan sheets. Active Planner uses the SQL Analysis Server to build Cubes of your data that you can easily slice and dice using Excel Pivot Tables or the built-in Analyzer Tool. This video shows you how build a Sales Analysis cube and then analyze the cube using Excel Pivot Tables. You will see how easy it is to analyze the sales data by product categories or by territories and the sales people within those territories. Length 13:43
The built-in Analyzer tool provides advanced data visualization allowing you to interact with the data in the form of graphs and charts, with the ability to drill down or around the data. This video shows you how to use the Analyzer to slice and dice the Sales Analysis cube by territories, sales people and product categories. Length 11:22
Using Excel Pivot Tables to Analyze Published Data
One of the advanced features of Active Planner is the ability to create OLAP Cubes from the plan sheet data using the SQL Analysis Server. Those customers who do not have an Analysis Server available are still able to use Excel Pivot Tables to analyze their Active Planner data. This video shows you how bring the published data into Excel and create a pivot table to easily slice and dice the data. Length 33:13
One of the strengths of Active Planner is its ability to use information from a variety of data sources within the enterprise. As I visit customers to help with their implementation or provide training, I find that most have existing data in SQL Server, MS Access, Excel or similar data sources that they want to use in their budgeting process. Database Query Formulas are a great way to access this data, but I find that some people are intimidated by these. So I have created a series of training videos to help you, beginning with this one covering the basics. Length 21:09
Database Query Excel as Data Source
I find that most financial people are familiar with Excel and as a result this is a typical source for database queries. However, there are a few quirks to know when using Excel, which I discuss in this training video. Length 15:31
In this video we build upon our knowledge by looking at how the column dates are used to retrieve data specific to the column, whether it is defined as a single period or 12 periods. Length 12:26
Active Planner Consolidate from Excel Subsheets
In this video we build upon our knowledge of Flow by looking at how we can easily consolidate budget values from Excel Subsheets. In Active Planner, we use the same flow setup for both plan sheets or Excel files. We will look at the modifications that are needed in the Excel file to prepare it for consolidation and the steps for defining an Excel Subsheet. Length 15:21
Salary Planning with Excel as the Data Source
I have found that many customers have a great payroll team or HR person that has already created the salary and benefit budget by person in Excel. In this video we build upon our knowledge of Database Query formulas to retrieve the Salary budgets in total for the Dept, from an Excel source. We will look at 3 examples. First we will retrieve the annual salary. Second we will retrieve the amount for each period, which accommodates raises and new hires. Finally we will look at computing the benefits/taxes in the Excel source along with the salary. Length 31:55
Data Analysis using Excel- Database Queries, Filters and Pivot Tables
I have noticed that many people throughout the world have been interested in my training videos on Using Database Queries and Pivot Tables in Microsoft Excel. So this training video, while very helpful for Active Planner customers, is applicable to any Excel user who wants to create database queries and then analyze that data using Filters and Pivot Tables. And as an added bonus, we will go into SQL Management Studio to build and debug our query and I will show you how to create a PIVOT query. Length 54:53
Page updated
Google Sites
Report abuse