Friday, December 13, 2013
Efficiency Tracker
This Excel application serves as a management tool for analyzing the performance of a company engaged in retail sales activity.
There are 4 territories and within each territory several Sales Representatives. Sheet named 'Data' contains the data for a quarter. Sheet 'Dashboard' contains a set of performance parameters and their charts of a Sales representative side by side the same parameters of the Territory the Sales Representative belongs to.
There are 2 ComboBoxes. One for selecting Territory. Once a territory is selected, the other combobox will be populated with the Sales Representatives (SR) of the selected Territory. By default the first SR of a territory will be selected and the performance parameters of the selected SR will be fetched.
The SR can be selected from the second combobox.
Download xlsm file
There are 4 territories and within each territory several Sales Representatives. Sheet named 'Data' contains the data for a quarter. Sheet 'Dashboard' contains a set of performance parameters and their charts of a Sales representative side by side the same parameters of the Territory the Sales Representative belongs to.
There are 2 ComboBoxes. One for selecting Territory. Once a territory is selected, the other combobox will be populated with the Sales Representatives (SR) of the selected Territory. By default the first SR of a territory will be selected and the performance parameters of the selected SR will be fetched.
The SR can be selected from the second combobox.
Download xlsm file
Friday, November 29, 2013
Sales Rep Efficiency Analysis
Download xlsm file
The data sheet contains hypothetical data of a FMCG distributor.
Column C to Column K contains data for a specific measure for the months of February to October. In this XL application, this is called 'Periods'
Column L to Column T contains data for another measure for the months of February to October.
Number of periods must be same for all the measures.
One measure is called one round. You can see that there are 2 rounds in the 'Data' Sheet.
Left most colum contains the Sales Representative (SR) ID which is unique. It has to be unique.
Column 'B' contains the Territory name. One Territory contains many SRs.
This application will do the following things:
-Create one Excel file for each Territory.
-Within each Territory, one sheet will be created for one SR.
-All the files will be saved in the location: D:\efficiency
-Some formatting will be done. For example, one blank row will be inserted before every round to
accommodate for the titles "Periodic" and "Periodic Average" to compare a specific period's performance
with his/her average performance.
-2 Charts will be created for every SR. 2 charts for 2 rounds.
-You don't have to do any data processing. Just activate the Data sheet and run the 'MotherMacro'.
-A Summary sheet will be created which will contain the names of the Territories for which XL files were created.
When the application prompts you for the number of periods, input the appropriate figure. For example if you are
analyzing data for 4 months, input 4. Naturally, your data should contain 4 columns for each round. There is no limit for number of periods.
When the application prompts you for number of periods, input 2, 3 or 5 whatever number of rounds you have. There is no limit.
When the applications ask you to show the data source for Territory, select B1 and any number of Territories you want. The selection must be contiguous.
Don't worr if there are duplications; only unique Territory names will be used. Just remember to start your selection from cell B1 and select as many
cells as you need below. [Refer to screenshot]
If you have any query email me at dilir.khan@gmail.com. I shall try to help if time permits.
The data sheet contains hypothetical data of a FMCG distributor.
Column C to Column K contains data for a specific measure for the months of February to October. In this XL application, this is called 'Periods'
Column L to Column T contains data for another measure for the months of February to October.
Number of periods must be same for all the measures.
One measure is called one round. You can see that there are 2 rounds in the 'Data' Sheet.
Left most colum contains the Sales Representative (SR) ID which is unique. It has to be unique.
Column 'B' contains the Territory name. One Territory contains many SRs.
This application will do the following things:
-Create one Excel file for each Territory.
-Within each Territory, one sheet will be created for one SR.
-All the files will be saved in the location: D:\efficiency
-Some formatting will be done. For example, one blank row will be inserted before every round to
accommodate for the titles "Periodic" and "Periodic Average" to compare a specific period's performance
with his/her average performance.
-2 Charts will be created for every SR. 2 charts for 2 rounds.
-You don't have to do any data processing. Just activate the Data sheet and run the 'MotherMacro'.
-A Summary sheet will be created which will contain the names of the Territories for which XL files were created.
When the application prompts you for the number of periods, input the appropriate figure. For example if you are
analyzing data for 4 months, input 4. Naturally, your data should contain 4 columns for each round. There is no limit for number of periods.
When the application prompts you for number of periods, input 2, 3 or 5 whatever number of rounds you have. There is no limit.
When the applications ask you to show the data source for Territory, select B1 and any number of Territories you want. The selection must be contiguous.
Don't worr if there are duplications; only unique Territory names will be used. Just remember to start your selection from cell B1 and select as many
cells as you need below. [Refer to screenshot]
If you have any query email me at dilir.khan@gmail.com. I shall try to help if time permits.
Saturday, November 16, 2013
Sales Record Update
Macro will take data from 'New Data' sheet and update column B.
It will match SKU column in both sheets, copy sales data from 'New Data' sheet and add the same to column B of corresponding SKU in 'Result' sheet.
You are free to change data in 'New Data'.
Download https://drive.google.com/file/d/0B5Ry95YO7wVARWpkMmRHVmh0cVk/edit?usp=sharing
It will match SKU column in both sheets, copy sales data from 'New Data' sheet and add the same to column B of corresponding SKU in 'Result' sheet.
You are free to change data in 'New Data'.
Download https://drive.google.com/file/d/0B5Ry95YO7wVARWpkMmRHVmh0cVk/edit?usp=sharing
Tuesday, November 5, 2013
Copy Values from one sheet to another but skip cells with conditionality
Copy values from column A of 'new' worksheet to column A of old worksheet.
Skip the cells that contain N/A or '0' in 'new' worksheet.
Change the color (make yello) of those cells in 'old' worksheet whose values were replaced.
Skip the cells that contain N/A or '0' in 'new' worksheet.
Change the color (make yello) of those cells in 'old' worksheet whose values were replaced.
Friday, October 25, 2013
Subscribe to:
Posts (Atom)