In Part I of this series, we’ll walk through the process of translating customer requirements into a Kepion Application. In this session, we’ll review some basic Kepion concepts, identify the Forms we need to build, and use Filters to consolidate the total number of Forms we need.
In Part I of this series, we’ll walk through the process of translating customer requirements into a Kepion Application.
In this session, we’ll begin by reviewing some basic Kepion concepts. Then we’ll tackle the first step of the Architecture Design process by identifying the Forms we need to build, and lastly, we’ll see how we can use Filters to consolidate the total number of Forms we need.
Let’s start by looking at the fundamental concepts that drive Kepion: We use Dimensions to define Models. Models are then used to store relational data and calculations in a database. Then we create Forms as a window to view that data.
Let’s see how we can identify the Dimensions, Models, and Forms we need, from spreadsheets provided by the customer.
In our example, the customer is a Consumer-Packaged Goods company, which sells four brands, each with multiple products, across several countries. We currently have their revenue planning spreadsheets.
The spreadsheet starts with a group of Profit & Loss, or P&L reports. The reports are all structured in the same way. From the tab names we can see that they’re separated by the four brands: Adair, Aviva, Harper, and Sander. The row headers are different accounts of the P&L.
The Deflator % Form shows some percentage accounts by brands-- and then there’s a group of Trade Spend Forms, also divided by brands.
At the end we have some Base Assumption tabs. Each of them contains a Form for Volume, Cost, and Price. A link to download this spreadsheet is provided below the video. It’s encouraged that you spend some time familiarizing yourself with it, before moving on.
First, we’ll need to clean up the customer spreadsheets and translate them into Kepion Forms, which are defined by four regions: Rows, Columns, and Filters, with Data existing at the intersection between.
We should also determine which Forms will be read-only reports and which will be input Forms. By default, in Kepion, read-only cells are white, while input cells are yellow. The default color for all cells, including Annotations and Drop-Downs, can be configured however you’d like.
The P&L Forms are reports, because the data is calculated through formulas. The rest of our Forms will be used to gather input.
Notice how all of the Forms in the Base Assumption tab have exactly the same structure. To create a more efficient application, we should consider combining them together using a Filter. Filters allows us to switch between different contexts in a Form.
For example, if we combine these three spreadsheets together, we could create a Filter with options such as Volume, Price, and Cost, and use it to flip between the different sets of data. By stacking more Filters together, we could combine the data from hundreds of spreadsheets into a single Form.
Adding Filters that allow us to switch between different Fiscal Years; Scenarios; and Entities; would all be helpful.
In addition, we could use a Brand Filter to switch between the four brands.
This means we only need a single Form to represent the Volume, Price, and Cost, of our Base Assumptions, so let’s hide the extra ones. Since we’re also adding a Filter to switch between the different Brands, we can hide all the other Base Assumption tabs as well.
Using the same logic, we can remove the extra tabs for the Trade Spend Forms, the Brand information in the Deflator % Form, and the extra P&L reports.
By adding Filters to all of the Forms, we’ve now condensed the customer requirements spreadsheet from 22 Forms to 4. I’ve added mockups of each of these Forms to our Architecture Document.
In this session, we reviewed the basic concepts that drive Kepion, analyzed the structure of the customer’s spreadsheets, and used Filters to transform and consolidate them into Kepion Forms.
In our next session, we’ll begin step 2 of the Architecture Design process and determine the Dimensions and Hierarchies we’ll need to build those Forms.
Continue to Session 2 - Identifying Dimensions.