Task: Build a pro forma financial statement model using the information below and then filling in the provided Excel spreadsheet. The objective of the assignment is for you to compute and estimate free cash flows for Cracker Barrell Express Project for year 2019-2023.

Scenario Description

Cracker Barrels revenues have increased by an average of 3% per year in the past 5 years and continue to grow at a projected average rate of 2.5% per year, having grown their revenue to over 3 Billion dollars in the 2018 FY. While the companys outlook is strong, core business revenue growth is strongly driven by an expansion of stores, rather than by an increase in guest traffic. In fact, the company saw a 1.9% decrease in guest traffic in 2018 (2018 annual report). As the generations that represent the bulk of Cracker Barrels customers age, we anticipate a continued decline in guest traffic.

While the company has introduced the Holler & Dash Brand for a fast-casual service marketed towards younger generations, we believe that the core business can still be expanded to younger generations in the same way and therefore build the Cracker Barrel Old Country Store Brand through express-style home cooking on college campuses.

Pricing

The express stores have been modeled with schools with around 20k or more students, where meal equivalency dining services program average $7 for students. We plan that the menu would be scaled down for faster service, allowing for this $7 meal average. Old Country Store meals currently averaged $10.48 in 2018.

Revenue

We anticipate $980k per store revenues grown at the pro-forma 5 year average of 2.5%. In the first year, this totals $2.45M for a total of 5 stores opening halfway through the year. After initial negotiations, we expect that it will take less time to open new stores with contracted universities, and anticipate 5 more opening in 2021, 5 more in 2022, and finally 5 more 2023 (none thereafter).

COGS, SG&A, Labor, Fees

In all scenarios, COGS (i.e. food costs), SG&A, and Labor are computed as % of sales, using Cracker Barrels 2018 annual report figures.

For COGS, these costs are 25.7% of sales, which has remained quite stable over the last 5 years.

For SG&A, these costs have been assumed to be 4.7% of sales from the 2018 annual report.

For Labor, the % of sales is 34.8% (2018 annual report).

Commission fees paid to school = 12.5% of revenues.

Startup costs

Each store is estimated to have startup costs of $215k including capital expenditure for kitchen and cooking equipment of $150k. Thus $65k ($215k - $150k) is for items such as opening inventory and supplies, training expense, and insurance.

CAPEX

Each year that five (5) Express stores open, the projected capital expenditure totals $750k (kitchen and cooking equipment at $150k*5). We opted for a 10 year straight line depreciation method for simplicity. This results in $15k annual depreciation for each store.

Express Store EBIT

Year 0 (2019) is a time for planning, so there are no operational profits or capital expenditures.

Year 1 (2020) represents the first year of express store operations. This year reflects profits for operating only half of the year, as opening is not expected to be possible before summer 2020.

Year 2 represents a full year of operations with 10 stores.

Free Cash Flow Estimation

Estimate Cracker Barrells Free Cash Flows from years 2019 2023 using the FCF equation and the information provided in the Spreadsheet.

CBE Project Pro Forma Statement Student Template (1) Q Search Sheet Home Insert Page Layout Formulas Data Review View + Share A Out Arial 8 A A A- A+ to Wrap Text General 2 Autosum Fill Clear. 49 Copy + Format Paste B I U. - A = EE Mergo & Cantor $. % 2 0 .30 00 D Call Styles Conditonal Format Formatting as Table Insert Format Delete Sort & Filter Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Chack for Updates, M48 XVfx T D . . 1 L A 1 . Cracker Barrell Express Project Pro Forma Statement TAM 1 Assumptions Extimata Expiration border: TRACE 11 Oturm Best CUL S. H 1: lis.sic.lnk CW nep EXT Nina BRASS 21 ISS Far. Przu mukaanak kad han RELSE CONEX Pride SUSTERS 11 > Yar! Trar Yar 2020 Rama 201 23 2013 15 2125 16 Eckarerraltar Fodul M.2 Menu (euFLIaar Teu RIM TIES lead 41 u 40 44 4 4. Chiro Opintapete SURA N.CLAPEN DEN Taal ERIT 0 41 + 11 . Kaka & Cockt BERI Ndihisi NUU Abris de Caux MAC NCE - MIT IT) + DUCATX - CNC 1 site - Best case IRR & PP Sheet1 + Ready S: 5796 CBE Project Pro Forma Statement Student Template (1) Q Search Sheet Home Insert Page Layout Formulas Data Review View + Share A Out Arial 8 A A A- A+ to Wrap Text General 2 Autosum Fill Clear. 49 Copy + Format Paste B I U. - A = EE Mergo & Cantor $. % 2 0 .30 00 D Call Styles Conditonal Format Formatting as Table Insert Format Delete Sort & Filter Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Chack for Updates, M48 XVfx T D . . 1 L A 1 . Cracker Barrell Express Project Pro Forma Statement TAM 1 Assumptions Extimata Expiration border: TRACE 11 Oturm Best CUL S. H 1: lis.sic.lnk CW nep EXT Nina BRASS 21 ISS Far. Przu mukaanak kad han RELSE CONEX Pride SUSTERS 11 > Yar! Trar Yar 2020 Rama 201 23 2013 15 2125 16 Eckarerraltar Fodul M.2 Menu (euFLIaar Teu RIM TIES lead 41 u 40 44 4 4. Chiro Opintapete SURA N.CLAPEN DEN Taal ERIT 0 41 + 11 . Kaka & Cockt BERI Ndihisi NUU Abris de Caux MAC NCE - MIT IT) + DUCATX - CNC 1 site - Best case IRR & PP Sheet1 + Ready S: 5796