Now, we will analyse the Data Marts with Excel Power Pivot.

In Excel, under PowerPivot → Manage → From Database, connect to SQL Server

• Server name: NO RDB
• Use SQL Server identification
• User name: NO SID
• Password: NO PAS
• Save your password
• Database: select the data mart from the drop-down menu: pick NO SID_DATAMART
• in the ADVANCED tab, check, if the parameter Persistent Security Info is set to false.
• Click Next

In the Choose How to Import the Data windows, check if select from a list of tables and views to choose the data to import ist selected. Click Next.

• select F_Monthly_Sales, F_Monthly_Costs, F_Monthly_Marketing and F_Monthly_Stock and Select Related Tables.
• Click Finish.

Your data model should look quite complex now.

Create a new query and answer the following question:

PowerPivot: CompanyProfit

PowerPivot: MarketShare

PowerPivot: Product Mix

Check, if your product mix in the store fits to the product mix sales amount.

You started marketing campaigns in different months. Check, if you selected the months, where the highest sales activities are.

PowerPivot: Marketing Mix

• You should plan your marketing campaign in months with a high sales amount

Check, if your marketing campaigns were successful. A marketing campaign is successful, when the gross profit is higher than the marketing costs.

Analyse your Marketing Campaign

The following report shows the performance of your salespersons. Does the revenue, the sales amount and the gross profit correlate? With other words, does the salesman with the highest sales amount make the highest gross profit?

Analyse GrossProfit, Revenue, SalesAmount and GrossProfit-to-Revenue ratio by salesmen.

HowTo PowerPivot - How does your Salespersons perform?

Did you employ enough salespersons?

Make a quick calculation:

• Your store is open 300 days a year, because shops are closed on Sunday.
• Your store is open from 10 am to 8 pm (10 hours a day). Opening hours a year: ?
• A salesperson works 40 hours a week. He/she gets 6 weeks paid holidays. Also, calculate 3 weeks off for education, trainings and exhibition visits. Also plan 1 week absence because of illness and 1 week for public holidays. Working hours a year: ?
• 3 salesman should be always in store.

How many salesman should be employed?

• If you have employed not enough salesperson, you will get a negative service factor which influences your sales amount

• Also, play with the commission factor. The more commission a salesmen gets, the better he/she will sell. Be careful: a high commission will melt your profit

f5014