Unit Title : Sales Planning And Evaluation
1.Building an IT-solution to perform a plan-fact analysis of bike production company sales to shop buyers
1.1. Description
The main learning objective of this assignment is to apply knowledge of SQL and foundations of relational databases to create a simple IT-solution for plan data management.
You have been given access to a database of a company that sells bikes and related products.The database contains information about orders, products, and customers. Customers of two types place orders – individuals and shops (reseller companies).
Sales Planning And Evaluation Assignment-UK.

To evaluate the performance of sales department you need to (1) create tools to automate the planning process and (2) prepare data to calculate difference between planned and actual values of amount sold to companies.

Data manipulations should be implemented in SQL. Plan management (like creating new plan periods, data approval or changing planning status) needs to be done in form of python function calls.

This assignment includes the following activities:
• Writing queries to process and copy data
• Creating functions in python that uses one or more SQL queries
• Creating materialized views in SQL
• Setting permissions with SQL

A more detailed explanation of planning process is presented in the sections below. The next chapter covers users of a planning system you are creating and their role in planning process.

1.2. Users of planning system
There are two user groups working with plan data: administrators and managers.
Administrators prepare initial plan data which then becomes available to managers. One planning period stands for one quarter. Administrators have access to all plan data unlike managers.

Managers plan sales in several countries. Mapping between countries and managers is documented in the system’s settings. Once the administrator has prepared the initial version of sales plan, managers will be able to update and approve the calculated figures. The initial sales plan is based on actual data in two previous years.

Managers can lock their data to avoid unintended corrections by other users. Only locked data is available for correction.

At the end of the planning process managers confirm correctness of plan figures. The approved plan is then used to prepare a report on plan-fact deviations. Plan-fact comparison report is available to both managers and administrators.

1.3 Implementation of the planning process in a database

To organize planning process special tables were designed to store planning data as well as information on its status on a given planning period.

Plan data has three versions stored in a single table – N (initial calculation), P (edited) and A (confirmed and considered as the result of planning).

A plan data slice is defined by a quarter (e.g., 2013.3) and a country. The status of each data slice can be set individually. The valid status values are R (ready for work), L (locked by manager and available for editing), A (approved).