Payroll Implications for Time Off - 4 MONTH TEMPLATE

	A	B	C
1	CARES ACT RESTAURANT FINANCIAL PLANNING TOOL Estimating Restaurant Burn-Rate Given Short-Term Changes in Operations & Support from CARES funding over 8 weeks with payroll & rent support with 8 additional weeks of maintaining full staffing
2	Designed by: Dr. Peter Boumgarden, Ben Dalton (MBA), and Shaun Vaid (MBA) of Washington University in St. Louis, Olin Business School. Questions or comments can go to Dr. Boumgarden at boumgardenp@wustl.edu.
3	THIS SPREADSHEET IS MERELY A TEMPLATE. PLEASE COPY TO YOUR OWN GOOGLE DRIVE OR DOWNLOAD AS AN EXCEL FILE TO EDIT ON YOUR OWN
4	OTHER RESOURCES (will expand as more become available): - DAILYBEAST ON TOOLS FOR RESTAURANTS - https://www.thedailybeast.com/a-financial-plan-of-action-for-out-of-work-bar-staff?via=newsletter&source=DDMorning - FULL TEXT OF CARES ACT - https://www.congress.gov/bill/116th-congress/senate-bill/3548/text#toc-idCCF73EAEF8454D2A8B29B9DF4626458E -
5	Yellow = Cells to Edit Based on Company Numbers
6	Green = Cells to Estimate / Project
7	Blue = Critical Calculations
8	Estimating restaurant financial impliactions (burnrate, loan size, profitability) for first 8 Weeks of CARES employee/rent/insurance support (April 1 - May 30) assuming reduced operations given COVID-19 (either shut down, or reduced revenue)
9	Note: "TRADITIONAL" implies pre-COVID week // CARES-FIRST-HALF-Week means April 1 - May 31 // CARES-SECOND-HALF means June 1 - July 30
10
11	CATEGORY	NUMBERS	NOTES FOR MODELER
12	Existing Cash on Hand	$50,000.00	Add based on existing business model
13
14	Estimated TRADITIONAL Weekly Revenue (Assuming Primarily Food & Drink)	$55,000.00	Add based on existing business model
15	Estimated TRADITIONAL Weekly Staffing Expenses (Covered by CARES)	$23,000.00	Add based on existing business model. Assumes covered in CARE bill if all staff retained on payrole through July 30
16	Estimated TRADITIONAL Weekly Utilities + Rent/Mortgage + Interest (Covered by CARES)	$10,000.00	Add based on existing business model. Assumes covered in CARE bill if all staff retained on payrole through July 30
17	Estimated TRADITIONAL Weekly Non-Staffing, Non-Food Expenses (Not covered by CARES)	$5,000.00	Add based on existing business model. This includes any remaining things NOT covered by CARES (e.g. FICA, marketing expenses, etc.)
18	Estimated Food Cost as % of Food Revenue	30%	Estimate based on existing business model. Assumes food cost as percentage stays constant in other operational models (e.g. take-out, etc.)
19	Food Cost - TRADITIONAL Week	$16,500.00	CALCULATED - Based on percentage of food revenue
20	Total Costs - TRADITIONAL Week	$54,500.00	CALCULATED - Sum of all costs identified above
21	WEEKLY CONTRIBUTION OR PROFIT - TRADITIONAL WEEK	$500.00	CALCULATED - Profit as (Revenue - Cost) from a TRADITIONAL WEEK
22
23	Percent of Staffing $ Kept On Payroll	100%	Owner Estimated (3 cells add up to 100%). Covered by CARES Bill in full assuming maintain full payrole through July 30. If this dips below 100%, you lose the covered mortgage/rent/utilites benefit
24	Percent of Staffing $ Moved to Unemployement	0%	Owner Estimated (3 cells add up to 100%). If staff goes to umemployment, they are covered by state UI ($320/wk in MO at top end) + CARES Bill supplement (proposed at 600 a week) = Equivalent to 48K a year salary
25		100.00%	CALCULATED - Make sure these add up to 100%
26	Estimated CARES-FIRST-HALF Non-Food, Non-Staff, Non-Rent/Mortgage Expenses of Reduced Operations (Assumes Rent or Mortage Covered in Bill). This applies only to things NOT covered by Cares Bill (See line 13)	90%	Owner Estimated as a Percentage of What they believe is Feasible Cost Reduction - Note that .8 means you reduced 20% of your non-food, non-staff expenses in this new operations
27	Estimated Food Revenue During CARES-FIRST-HALF Restaurant Operations (e.g. Only Take-out, Catering, etc.)	15.00%	Owner Estimated as a Percentage - For example, .25 means you are only getting 25% of your traditional revenue in this time. .75 means you are getting 75% of your normal revenue. 0% means you shut down all operations. Best if this assumption is based on what you have learned by trying this model out
28
29	UPDATED REVENUE IN REDUCED CARES-FIRST-HALF OPERATIONS	$8,250.00	CALCULATED
30	UPDATED NON-CARES-COVERED COST w CARES-FIRST-HALF OPERATIONS (eliminates all that would be reimbursed)	$6,975.00	CALCULATED
31	Actual Cash Burn Rate / Week in CARES-FIRST-HALF - Assuming Reduced Expenses through CARES COVERAGE	N/A	CALCULATED - Only has a number if Expenses > Revenue during this period, NA indicates no burn on cash during 8-week period
32	Actual Cash Runway in CARES-FIRST-HALF - How many weeks will cash last at existing burn rate - Assuming Reduced Expenses from CARES COVERAGE	N/A	CALCULATED - Only has a number if Expenses > Revenue during this period, NA indicates no burn on cash during 8-week period
33
34	Maximium Loan Size for Business allowed by CARES Act	$254,642.86	CALCULATED - Determined at 250% of monthly payroll, Capped at $10M
35	Estimated Amount of Loan Used on Rent/Mortage + Interest + Payroll + Insurance - To Be Reimbursed	$264,000.00	CALCULATED - Assuming loan/grant used on for all 8 weeks. Remainder would fall under terms of the loan (e.g. low interest small business loan)
36	Non-Payroll Benefit of Taking the Loan	$80,000.00	CALCUALTED - Sum of all non payroll benefit, or 8 * rent/mortgage/utilities per week
37
38
39	Estimating restaurant financial impliactions (burnrate, profitability) for second 8 Weeks following CARES support (June 1 - July 30) assuming increased operations but required maintenance of fully employement
40	CARES-SECOND-HALF ASSUMES YOU HAVE TO MAINTAIN ALL STAFF BECAUSE YOU WENT FOR THE CARES FUND THAT GIVES RENT/MORTGAGE/INSURANCE BENEFIT
41	CATEGORY	NUMBERS	NOTES FOR MODELER
42	Existing Cash on Hand	$60,200.00	CALCULATED - Updated to Reflect 8 Week CARES Coverage and any +/- cash flow across 8 weeks
43
44	Estimate of June 1 Business as a Percentage of Previous Business	80%	Owner-estimate based on what they think business will be like on June 1
45	Updated CARES-SECOND-HALF Weekly Revenue (Assuming Primarily Food & Drink)	$44,000.00	CALCULATED - Percentage (above) of traditional week revenue
46	Estimated TRADITIONAL WEEKLY Staffing Expenses	$23,000.00	TAKEN FROM ABOVE
47	Percent I would operate-full staff at capacity... 100% indicates full capacity	80%	Reduce from 100% if you can maintain full staff but at reduced capacity. For example 80% means you have a full staff but working 32 hours / week v 40 hours. CARES has pull back if this number dips below 75% for any employee (and by definition, all employees). Here is the exact language, "Level of Payroll: Your loan forgiveness will also be reduced if you decrease salaries and wages by more than 25% for any employee that made less than $100,000 annualized in 2019." The critical question is how long this last to be maintained.
48	Updated CARES-SECOND-HALF Weekly Staffing Expenses	$18,400.00	CALCULATED - Percentage (above) of traditional week expenses
49	Updated CARES-SECOND-HALF Weekly Utilities + Rent/Mortgage + Interest	$10,000.00	Taken from above
50	Updated CARES-SECOND-HALF Weekly Non-Staffing, Non-Food Expenses	$5,000.00	Taken from above
51	Estimated Food Cost as % of Food Revenue	30.00%	Taken from above
52	Food Cost - CARES-SECOND-HALF Week	$13,200.00	CALCULATED - Percentage of Food Revenue (above)
53	Total Costs - CARES-SECOND-HALF Week	$46,600.00	CALCULATED - Sum of all costs above
54	WEEKLY CONTRIBUTION OR PROFIT - CARES-SECOND-HALF WEEK	-$2,600.00	CALCULATED - Profit as (Revenue - Cost) from a POST-CARES SUPPORT WEEK
55
56	Actual Cash Burn Rate / Week - Assuming Reduced Expenses through CARES-SECOND-HALF	$2,600.00	CALCULATED - Only shows a number if Expenses > Cost during this period, NA indicates no burn on cash
57	Actual Cash Runway - How many weeks will cash last at existing burn rate in CARES-SECOND-HALF - Assuming Reduced Expenses from CARES COVERAGE	23.15384615	CALCULATED - Only shows a number if Expenses > Cost during this period, NA indicates no burn on cash
58
59	Ending Cash After Final 8 Weeks of CARES-FIRST-HALF + CARES-SECOND-HALF (4 months in total)	$39,400.00	CALCULATED - Based on cash flow for first 8 weeks and second 8 weeks
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100