Brewery financial model and Budget Control

PURPOSE OF MODEL

This Excel model is an advanced, user-friendly financial planning tool designed explicitly for startups or existing breweries. It creates a comprehensive 10-year rolling financial projection, including Income Statements, Balance Sheets, and Cash Flow Statements. The model reflects the unique aspects of a brewery's revenue generation, cost structures, and investment needs.

In line with the best practices in financial modeling, the model includes:

• Instructions and explanations for each line item (Input Sheet)
• Checks and input validations
• A dedicated sheet for historical data entry, allowing for direct comparison of the brewery's actual performance against forecasted numbers
• An extensive suite of charts for visual depiction of crucial metrics and performance indicators

KEY OUTPUTS

The primary outputs encompass:

• Detailed financial statements presented monthly over a decade and condensed annually
• Valuation sheet
• Over 30 financial and business ratios for an in-depth performance analysis of the brewery
• Informative charts for visual understanding of business dynamics and key drivers
• Breakeven analysis
• A dashboard featuring summaries of forecasted financial statements, key financial ratios, and visualizations of financial projections, including revenue, gross profit margins, sources and uses of funds, and cash balance trajectory

MODEL ORGANIZATION

• Assumptions: Central to the model, this sheet hosts essential assumptions regarding revenue, CapEx, OpEx, and startup costs, all customized for brewery operations.
• Operations: Outlines calculations for revenue, COGS, and OpEx, informed by the Assumptions sheet.
• Working Capital: Assesses necessary working capital for ongoing operations, a vital component of the brewery's financial stability.
• Debt Structure: Supports debt schedule aligned with the brewery's funding needs
• Capital Expenditures (CapEx): Targets investments required for acquiring and upkeeping brewery equipment and infrastructure.
• Monthly Financial Statements (Mo-FS): This section displays income, balance, and cash flow statements enriched with Operations and CapEx insights and considers dividend distribution possibilities.
• Actual Performance: Tracks the brewery's real-world performance for continual comparison with projected figures.
• Actual + Forecast: Combines actual data with forecasts for enriched valuation analysis.
• Annual Financial Statements: Summarizes financials annually, collating monthly data.
• Summary: Provides a swift executive overview of key business metrics.
• Breakeven Analysis: Calculates the sales volume necessary to offset operational expenses.
• Ratios: Delivers over 20 financial ratios for a thorough review of the brewery's health.
• Valuation: Employs discounted cash flow methodology for business valuation.
• Budget vs. Actual: Evaluate financial progress by juxtaposing budgeted and actual figures.
• Charts: Visual tools for emphasizing financial variations and supporting strategic planning.

PRODUCT LINE & PRODUCTION FORECAST

The model accommodates various product lines (Keg, Canned, Bottled) as per industry norms, with pricing and costs projected in BBL equivalents. It invites users to schedule production volumes by month/season, mirroring the brewery's operational capacity and resources.

FORECAST REVENUE & COGS

It allows for nuanced revenue forecasting by barrel output, factoring in losses and dispersing production across different lines. The setup for individual COGS per product line acknowledges each line's distinct economic profiles and margins, ensuring precise financial projections and COGS estimation.