Beer Stock Accounting and Management Tool
As a brewery, instant stock knowledge is key for production and sales decisions. Furthermore, in France, alcoholic beverage support taxes. Hence monthly reports must be submitted to French Customs. We must be rigorous on data quality as an error could led to tax penalties impacting drastically our net margin.
Objective:
Seamlessly and easily collect stock variation.
Model data to required dashboard and French Customs Report.
Result :
~90% of productivity gain.
~99% precision on production costs knowledge which had huge positive impact on EBITDA margin.
Precise costs control help us land key negotiation ending up being referenced by best craft beer distributor and being best sellers on Amazon.
Overview
Data reporting
Created and validated dashboard with French Customs. Set-up xml reporting for EDI transfert.
Data Analysis & Modeling
Analyzed production through dashboard and accounting modelization to adapt strategy.
Data Acquisition
Set-up a sharable Google Form and normalized raw data for quality inspection and dashboarding.
Skills
Detailed Methodology
A end-to-end project in close collaboration with a public authority.
Data Acquisition
As we worked remotely, we needed to be able to declare stock variation online using tools that would be flexible, easy to use and share. Thus a Google Form with Google Sheets was an obvious choice.
The data must collect timestamp, description of movement, type of movement, and alcoholic title.
I designed the form and underlying data to be able to collect the right data within few seconds to cover all ~700 possible variations.
Data were stored in a Google Sheet tab to allow modification if needed.
Example of xml
Context
In France beer, wine and spirits are taxable. You pay a flat base per quantity of alcohol per hectoliter that leaves the brewery to a non “tax withheld” place. This to limit expenses on important stocks (like for breweries or distributors). Thus, if a brewery sells to a bar, taxes must be paid. If it sells to another “tax withheld” place, no taxes are paid.
To limit fraud, French customs requires breweries to keep a daily-updated manual handwritten “Stock Accounting Book” and send “design validated” official reports monthly. “Stock Accounting Book” must stay in the brewery in case of non-planned controls from French customs.
Data Normalization and reporting
Once collected, I used Google Script to handle data transformation as it is well integrated with Google Sheet and could be triggered easily from the UI.
Several scripts were developed :
for normalizing data : Pivoting raw data, taking into account movements type and splitting between taxable and non-taxable production.
for building Monthly Reports : From normalized data and using input values from Sheets as parameters, the script built the report for a given month. Then reports were mailed to the French Customs.
for building XML : XML must follow a strict format to be validated by EDI platform. Stock movement and yearly cumulative production had to be identical to Monthly Reports.
Several quality tests were conducted within the Google Sheet and EDI platform to ensure correctness and completeness.
This automation help us gain ~90% of productivity which is key for a start-up.
It was a fascinating start-up project as it covered multiple data expertise fields (development, data analysis, etc.), and an important part of company value chain (brewing, accountability, etc.). I also had to deal with start-up needs (cost efficiency and flexibility) and public authority.
Furthermore, It had concrete implications as data had two important stakes : avoid margin impact from any taxes penalties and negotiation empowerment. Indeed, having precise and updated knowledge of production costs help us during negotiation of key contracts and greatly contributed of being listed by the best craft distributors and being best sellers on Amazon and thus, contributed to Big Bang Beers success.
Monthly Report Design Definition
Monthly French Custom reports must follow really specific and strict formatting and must be validated before using it in production.
Reports must be in hectoliter with 4 decimal precision, be grouped by alcoholic title and container (bottle or keg) and consider stocks flow (taxable, not taxable production or sales)
I lead multiple meetings and follow a specific training with French Custom Monthly Report owner to have a full understanding of the fields definition and their impact on the data acquisition and modeling.
Extract of Google Form for Stock Accounting
Monthly Report for the month of February 2020
Example of share of variable costs for the IPA beer
Data Analysis
I built a Looker Studio dashboard sourced on the normalized production data to get insights and take action to maximise production costs and efficiency such as :
bottle to keg ratio (bottles have more margin but kegs more volume).
Product mix (best sellers vs best margin beers).
Production loss (part of the 20HL batch capacity is lost in draff or yeast filtering)
Data Modeling
Using other data sources from accountability I could estimate with precision production costs (and take actions) and could model our costs structure.
This allowed me to forecast revenues and margin landing with 99% precision.