Financial Planning and Analysis Associate

Excel Modelling Demonstration

The purpose of this exercise is to demonstrate your ability to work with data and develop analyses in excel. This exercise should take you no more than 4 hours. The scenario and all assumptions are provided below, but if you would like to add an additional assumption, feel free to do so, while noting it clearly. No additional research is required. Excel is required for this activity, while a very brief Word explanation of your findings would be helpful as well. Please focus more on the excel model and do not worry about formatting or visuals of any Word summary of your findings.

Scenario:

A global pandemic has come to the United States, and restaurants everywhere are affected: people are staying in their homes, and many are afraid even to order take out. Three weeks into the shelter in place orders in New York and New Jersey, we think we may have seen a sales bottom, but sales are significantly down from pre-pandemic levels. It is time to start making some fast decisions. As the FP&A Associate, you have an important seat at the table, helping the team analyze different options at their disposal.

Question to Analyze:

Does it make sense to temporarily close any restaurants? Analyze the impact on sales and costs from consolidating restaurants. If you recommend to close any restaurants, at what point would you reopen them?

Assumptions:

• Utilize the attached sales data as a baseline for recent historical sales figures. While we have only tracked volume of orders for in-store orders to date, assume that the average pickup order is twice as large as the average in-store order, and the average delivery is three times as large as the average in-store order. Also, only in the last week did we start to break out direct delivery vs direct pickup, but you can assume going forward the same split between the two.
• Chicken tender prices have plummeted to $0.95/lb from a historic average of $2/lb. We buy directly from our supplier based on prevailing chicken prices at the time, with no opportunity to store chicken or hedge prices. We have heard that this drop is likely temporary due to a sudden decrease in demand nationwide from restaurants, and can reasonably assume prices will rise again in the near future. Chicken today is about 1/3 of total food costs, which are at 23% of net sales. Paper and packaging makes up another 3.5% (so total COGS today of 26.5% of net sales).
• Each restaurant needs a minimum of 2 team members during the slowest shifts. The average wage per hour for hourly team members is $16 in NY and $14.50 in NJ, and the highest performing restaurants post-pandemic are averaging about $70 in sales per labor hour for hourly labor. In addition to hourly team members, each restaurant has a GM who averages $60,000 in annual base salary. Payroll taxes and benefits add an additional 12% to total gross labor (hourly + salaried).
• For the Third Party and Proprietary Digital Sales, assume the following commission rates:
  • Doordash, Caviar - 25%. Postmates - 20%. Uber 30%. These are all delivery orders, and this rate is inclusive of the cost of delivery.
  • Grubhub, Sharebite and Delivery.com 20%. Assume these are 75% delivery orders and 25% pickup orders. The deliveries require Stickys to pay for outsourced delivery at $5 per delivery.
  • Ritual 15%. These are 100% pick up orders.
  • Direct Delivery: No commission, but these deliveries require Stickys to pay for outsourced delivery at $5 per delivery.
  • Direct Pick up: No commission.
  • Any catering 3^rd party sales require a 20% sales commission. Any WebCater orders do not require a commission.
• Assume that if we close any restaurant in Manhattan, we could reallocate 100% of the delivery sales from that restaurant to other Manhattan restaurants, but no sales from Brooklyn or New Jersey could be allocated to other restaurants and no sales other than delivery sales could be reallocated from closed Manhattan restaurants. Also, assume that if a restaurant is closed for more than 2 weeks, its sales upon reopening would be 50% of what they would have been had the restaurant stayed open, increasing 2% per week up to full volume.
• The remaining P&L cost buckets include the following:
  • Direct Operating Expenses: 1.2% of sales and $70 per month of fixed costs
  • Restaurant Supplies: 1.5% of sales.
  • Utilities: $3,000 per month.
  • Credit card fees: 1.5% of net sales.
  • Repairs and Maintenance: $400 per restaurant per month in preventative maintenance, plus 1% of net sales in unscheduled repairs.
  • Other restaurant G&A: 0.5% of sales
  • Monthly occupancy costs are as follows:
    • NYC1: $14,000
    • NYC2: $32,000
    • NYC3: $38,000
    • NYC4: $8,200
    • NYC5: $45,000
    • NYC6: $39,000
    • NYC7: $19,200
    • NYC8: $27,000
    • BK1: $9,500
    • NJ1: $9,500
    • NJ2: $12,000
    • NJ3: $11,000