Bar Financial Model: 5-Year Monthly Forecast with SBA 504 + 7(a) Loan Financing, DSCR, DCF & 3 Statements

Description
Overview
This Excel model forecasts the launch and first five years of a full-service bar (January 2027 – December 2031, in USD), with opening set for July 2027. It covers pre-opening build-out, staffing and launch costs, the ramp-up to full trade, and steady-state operations. Start-up funding combines founder equity with SBA financing.
The core revenue drivers sit on the Dashboard and the detailed inputs on one Assumptions sheet. Change guest counts, the average check or rent, and every statement, chart, DSCR test and valuation updates automatically.
The model comes pre-filled with realistic 2026/27 US base-case assumptions, so you can use it as a working example or replace the inputs with your own lease, payroll and supplier figures.
Revenue drivers

• Average guests per day split by Monday–Thursday, Friday–Saturday and Sunday, with annual growth
• Average check per guest and an annual menu price increase
• Sales mix of food vs beverage and liquor, plus an optional service charge (set to 0% because US tips go to staff)
• Separate cost of sales % for food and for beverage and liquor
• Opening ramp-up: first-month trade level and months to full trade

Costs

• 19 staff roles (GM, bar manager, head chef, bartenders, barback, servers, cooks, host, security), each with a hire date, end date, monthly wage and annual raise
• Payroll taxes and workers' comp (FICA, FUTA/SUTA, WC)
• Monthly G&A with start and end dates: NNN rent incl. CAM, utilities, liquor liability and property insurance, card processing (% of sales), licences (liquor, ASCAP/BMI), DJ and live music, repairs, consumables and admin
• One-off pre-opening costs: organizational and licensing costs, the launch marketing campaign, training and the soft opening
• Costs are entered in today's dollars and inflate at your chosen annual rates

SBA financing module

• An SBA 504 structure, split into a bank first lien, a CDC/SBA debenture and the borrower injection (minimum 15% for start-ups)
• An SBA 7(a) working-capital loan with guaranteed share and upfront guaranty fee
• Interest rates built from WSJ Prime plus spreads, upfront fees financed into principal, term, interest-only months and drawdown dates
• Monthly amortization schedules for each loan tranche
• Annual DSCR (EBITDA / debt service) with an automatic covenant check against a 1.25x minimum
• Sources and uses of funds, with the funding gap or surplus calculated automatically

Assets, working capital and tax

• CAPEX for FF&E (bar and kitchen equipment), leasehold improvements and POS, sound, lighting and security systems, each with its own purchase date and depreciation period
• Receivable, payable and inventory days
• Blended federal and state corporate tax, with loss carry-forward and payment timing
• Dividend policy (declaration period and payout ratio)

Outputs

• Monthly and annual Income Statement, Balance Sheet and Cash Flow Statement (direct and indirect methods)
• Dashboard with key financials, use of funds and charts
• A Charts sheet covering sales, gross profit margin and EBITDA margin
• A Ratios sheet covering growth, margins, ROE/ROA, the current ratio and asset efficiency
• Free cash flow, break-even, payback period and IRR
• DCF valuation: FCFF, terminal value, enterprise value and equity value

Built to best-practice standards

• One assumptions area, separate calculations and clear input/formula colour coding
• 13 integrity checks (balance sheet balance, direct vs indirect cash flow reconciliation, schedule balancing) plus alerts for negative cash and limited dividends, all summarised on one Checks sheet
• Hyperlinked table of contents and a step-by-step user guide inside the file
• Fully unlocked, with no macros: every formula is open and editable

Who it's for

• Entrepreneurs opening a bar, pub, cocktail lounge or gastropub
• Owners preparing an SBA 504 or 7(a) loan application or a bank credit package
• Consultants, SBA lenders, CDCs and accountants who assess hospitality projects
• Investors evaluating a bar acquisition or a new location

Sheets included
Cover, Contents, Guide, Dashboard, Charts, Ratios, DCF Valuation, Assumptions, Calculations, SBA Financing, Financial Statements (Monthly), Income Statement (Annual), Balance Sheet (Annual), Cash Flow (Annual), Checks, LookUp