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).
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)
Masterdata for this demo was extracted from a sample MySQL database.
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) 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) is the planning year. (2004, 2005, and so on)
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.
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)
excur) is a dimension for storing various exchange rates. It contains one member for each currency used by one of the shops.
currency-attribute on the territory level of the Store-dimension.
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: