Sales planning


Launch the demo

Overview

This application is a sales planning model for a fictional globally active bicycle manufacturing company. The system collects company's sales forecasts and costs of distribution from several hundreds of shops scattered around the globe. The manufacturing cost figures are imported from a relational database. This data is aggregated in real-time to produce a company-wide income statement. The sales figures are collected in shop's local currency for each shop and the results are converted into company's currency (USD). This allow for easy simulation of how exchange rate changes affect the company's income statement. Proposed authorization concept allows local shop owners to view and edit their own sales figures, but the computed results and contribution margins can only be seen by company's managers. "Territory managers" can view or edit data for all shops in a single region (f.e. Europe).

Login information

There is a single password for all users: pass. Following demo users are available:

  • admin - Technical administrator
  • smithe - Emma Smith is a headquarter user who is able to view/edit all available data.
  • smithj - John Smith is a manager responsible for the US region and is allow to view/edit data of all shops located in the US. (only sales figures, contribution margin is hidden)
  • dupontj - Jean Dupont is a manager responsible for the EU region and is allow to view/edit data of all shops located in the EU. (only sales figures, contribution margin is hidden)
  • mustermannm - Max Mustermann is an owner of a German bicycle shop and is allow to view/edit data of his shop. (only sales figures, contribution margin is hidden)

Metadata objects / Dimensions

Masterdata for this demo was extracted from a sample MySQL database.

  • Store (store) is a hierarchical dimension with two hierarchy levels. It contains stores grouped by their territories. The data is extracted from a relational database.
  • Product (product) is a hierarchical dimension with four hierarchy levels. It contains company's products grouped by categories, subcategories and product models. The data is extracted from a relational database.
  • Year (year) is the planning year. (2004, 2005, and so on)
  • Account (account) is a technical dimension to distinguish different company's KPIs and accounts. It contains members like: standard costs, net sales, contribution margin, and so on.
  • Currency (cur) is the currency type each planning value is associated with. It has only two members: Local for shop's local currency and US Dollar for values converted to US dollars. (US shops will have the same value for both dimension members)
  • Exchange currency (excur) is a dimension for storing various exchange rates. It contains one member for each currency used by one of the shops.

Cubes

  • Income is the main cube with dimensions: Store, Product, Year, Account, Currency.
  • Exchange Rates cube is a look up table which contains exchange rates from local currency to USD. Dimensions are: Year, Exchange currency. Exchange currency is mapped to the Store-dimension via a currency-attribute on the territory level of the Store-dimension.

Worksheets

There are two views: Exchange Rates and Sales Planning (one per cube). Exchange Rates view contains a single worksheet with an exchange rate per year and currency. The Sales Planning view contains most of the functionality. The view is organized by the Store dimension. Following are the most important worksheets:

  • Product income on the store level is the main input worksheet which contains an income sheet for each product sold in the store. After selecting a currency and a product model sales figures for each product of this model are shown. Only local currency view is editable, because USD values are computed automatically: Meta-data screen
  • Store income on the store level contains an income sheet for each store. Costs attributed to each store can be assigned via this worksheet. Only local currency view is editable. This worksheet has a cell access rule (Toggle conditional positions) which shows different positions for the two kind of shops (company owned and 3rd party): Meta-data screen
  • Products Sold by Store on the store level allow a manager to choose (via checkboxes) which products are sold in which store. A dimension dependency between Store and Product dimensions on the Product Income worksheet uses this information to show only available products: Meta-data screen
  • Company income on the * level uses the aggregated income and costs information from other worksheets to compute the Net Profit After Tax: Meta-data screen