Benji Fieri, a self-proclaimed “foodie” and MasterChef runner-up wants to open his
first restaurant, Benji’s Bistro (the “Bistro”). While his buddies tell him that he can
whip up the best lobster bisque they have ever tasted, the only numbers that he
understands are his fantasy football team’s win-loss record. That is why he has hired
your group, a prominent consulting firm, to help him build out a 5-year cash flow
model so that he can go to the bank for the capital to fund his venture. To be
considered for the loan, Benji will need to supply an income statement, balance sheet
and cash flow statement to the bank, along with a sensitivity analysis of key inputs.
The Model:
The only thing that Benji provided you is an Excel file that contains all the data he
has been able to forecast for this business. Specifically, the Tables in the workbook
are as follows:
1. The Menu. This sheet displays his projected menu, broken into categories,
and each assigned a unique menu item number.
2. The Ingredient List. This sheet gives you all the raw food materials he plans
on purchasing for the Bistro on a regular basis, its unit of measure, and how
much he anticipates each ingredient costing per unit.
3. The Recipes. This worksheet is the “secret sauce” behind Benji’s Bistro, as it
shows you how much of each ingredient (by units) goes into each menu
item.
4. Start-Up Costs & Assumptions. This worksheet contains all of the projected
capital expenditures and other operating costs he is expecting to incur.
Between Table 4 and the information on the financing below, your team
should have all the inputs needed for the business’ operating expenses.
5. Required Margins. Based on industry research, Benji has given you his
required contribution margin from each menu category. These margins
should be applied on a per-item basis, not for the category as a whole.
6. Volume Forecast. Using his crystal ball (and with some shrewd analysis by
your team) you have the projected demand for each menu item, by month,
for five years. These numbers represent items sold in units, not dollars.
Sources of Capital:
The Bank has tentatively offered a 5-year senior secured note, fully amortized,
with an annual interest rate of 7%. The loan is to be paid every month with
payments beginning when the Bistro opens. The Bank will lend up to 85% of the
© 2017 Michael E. Schostak. All Rights Reserved. Page | 2
total start-up cost. The remaining 15% of start-up costs must come from Benji’s
savings in the form of an equity capital contribution.
Requirements:
Benji has hired your team to supply the following:
1. A 5-year fully dynamic cash flow model on the worksheet titled “Model”.
This model should be displayed annually (2018-2022), and must contain an
income statement, cash flow statement, balance sheet, and any necessary
side schedules.
2. A calculation of Benji’s 5-year total return on investment, assuming he
contributes equity only at the beginning and that he sells the business after
5 years for 3x his 2022 Cash Net Income (the “Sale Price”).
3. A sensitivity analysis consisting of:
a. A 2-sided data table that tests the sensitivity of the ROI to changes in
two inputs that your team feels are the most uncertain in the analysis.
You choose the inputs, and the range to test.
b. A 2-sided data table that tests the sensitivity of the Sale Price to
changes in two different inputs than were chosen in 3(a) above.
c. A Scenario Analysis (using the Scenario Manager) that tests the
sensitivity of the ROI, Sale Price, and 2022 Ending Cash Balance, to
changes in 10 inputs that your team feels are most critical to test.
4. You will need to use as many of the Excel formulas/functions/features we
covered in class as possible. The more you use correctly, the higher your
grade will be.
NOTES:
– While the final model should be displayed annually, you may choose to build it monthly
first. That is fine, but please make sure the annual financial statements are easy to find and
well formatted.
– The sensitivity analysis should be done on the worksheet called “Sensitivity”.
– Be sure to save often and use version control. Do not link to other versions of the
workbook!
– I strongly recommend that teams get together in person to build the model and do the
sensitivities together. This will enhance everyone’s learning experience. I will ask each team
member to submit a peer review on the rest of their team after the project is turned in; so
free riders will not be tolerated!
