Embedded Files
For management reporting such as variance analysis (like Budget vs Actual etc) to make sense of results, one may consider utilizing the waterfall chart ( Which is a very useful way to present the results in an orderly manner highlighting the material deviations.)
For Budget vs Actual PL Reporing
For Drilling Down into Operating Expenses
Another area etc would be for reviewing the effectiveness of promotions where there is a comparison between actual sales figures achieved during promotion vs Baseline Sales Figures
Main analysis would be the below diagram which segregate the effects of promotions into the respective categories
Baseline Sales figures ~ Would be sales figures if no promotion. An estimate.
Sales Price Variance: The reduced margin due to the promotional markdown
Sales Volume Variance: The increase in sales due to increase in quantity sold (for pdts with positive price elasticity of demand)
Cannibalization: impact on other products with negative cross elasticity of demand. E.g Promoted item decreasing the sale of another item in the same category
Halo Effect: Opposite of Cannibalization. Impact on other products with positive cross elasticity of demand. E.g Promoted item increasing the sale of another item.
Pull-Forward: Impact on future sales ~ Increasing current sales at expense of future sales ~ applicable for items with long shelf life
To perform the above, there is a need to review historical records and perform the necessary filtering, averaging, and adjusting for seasonal fluctuation factors for the respective product types. Thus there is a need for a compiling tool for creating a central database ; and a reporting tool for analysis.
Prerequisite to perform the above breakdown analysis:
1) Compiling input tool ~ To create a Database of actual past sales transactions from the retail POS machines (PS: Invoices to distributors do not count - sales to middleman does not equate to sales to end customers ~ to prevent overstock situation of distributors) The manual process of compiling data could be automated as per below website where data feeds in CSV or excel format could be uploaded to Access in one click.
Website sample of compiling tool: https://sites.google.com/site/excel4routine/excel/exceltoaccess
2) A reporting tool ~ Excel Spreadsheet as a fronted for extracting and summarizing the data from the database.
For Excel 2016 version, this waterfall chart is available as a default selection and can be easily implemented through a few clicks.
1~Prepare and highlight the Data for charting (From the top left cell containing word "Budget" till the bottom right cell which is boxed up in red borders)
2~Select INSERT from the menu buttons and click on the icon below under the CHARTS section
3~Select the Waterfall Chart . Done!
For earlier versions of Excel, this waterfall chart is not available as default selection. A template which does exactly the same Waterfall Charts is as attached below for those with earlier Excel versions. (PS: A workaround using Stacked Columns)
DOWNLOAD FILE
Page updated
Google Sites
Report abuse