The COVID-19 Pandemic has caused a frightening drop in consumer demand and forced shutdowns for many small businesses in the service industry. Any company that depends on foot traffic to survive is suddenly finding itself scrambling to conserve cash. To help, I created a simple financial model for a small business to estimate the impact of a shutdown (and reduced demand) on its cash position over 12 weeks. You can download the model here:
Note: the blue numbers are the variables meant to be changed.
This financial model is a cash basis, not an accrual basis model. The point of the model is to show actual cash inflows and outflows during the week they occur, allowing a small business to determine:
- If and when a business will run out of cash.
- If the business will run out of cash, how large of a loan it will need to remain solvent.
- How reduced demand may affect the business when it reopens.
The model outputs three 12-week scenarios. In the first scenario, the business remains open with a large reduction in demand for five weeks (I realize this is not an option now in many areas), the second is a three-week shutdown, and the last is a six-week shutdown. The model assumes that you continue to pay both your bills and your salaried employees on time. Each of the scenarios has demand gradually ramp to normal after reopening. There are many assumptions in this model, and you should feel free to make adjustments based on your opinion on how the market will recover. You can also modify the model to go beyond twelve weeks.
Using the default numbers in the model, the hypothetical business in question runs out of cash during the 5th week of a six-week shutdown (highlighted in red in the model). The business remains solvent in the other two scenarios:
Here is a quick rundown of each variable in the model:
- [Business Basics] No. of days open per week: adjusting this will change the weekly variable staffing cost of hourly employees.
- [Monthly Costs] Rent: self-explanatory, your monthly rent cost.
- [Monthly Costs] Additional expenses: other monthly fixed payments like insurance and internet.
- [Monthly Costs] Salaries: the monthly cost of your salaried employees.
- [Monthly Costs] Monthly debt payments: the monthly cost of your long-term debt. Do not include vendor payments.
- [Monthly Costs] Monthly hour staff (full) and (skeleton): these two numbers are pre-calculated based on your daily hour staff cost. Don’t change these numbers.
- [Variable Operating Costs] Variable Operating Costs: these are variable costs like electricity, water, and gas as a percentage of sales. You may need to calculate this by dividing the total cost of our variable expenses by your monthly sales to get an idea of the percentage. I realize that these bills are typically paid monthly; however, for simplicity, we are turning it into a portion of sales.
- [Variable Operating Costs] Cost of Goods Sold (COGS): this is the cost of the product you sell as a percentage of total sales. In the default example, the product cost is 30% of the total weekly sales.
- [Daily cost of hourly staff] The daily cost of hourly staff (full staff and skeleton crew): how much it costs to staff your business with hourly workers per day. The full crew is your typical high-demand day crew, and a skeleton crew is your minimum viable crew size.
- [Financial Position] Cash on hand: the current bank account balance(s) of your business.
- [Financial Position] Accrued hourly staffing cost: hourly wages that have been earned by employees but not yet been paid.
- [Financial Position] Outstanding accounts receivable (AR): money owed to your business within the next 30 days, but not yet paid. In this model, the default is that you receive your AR in full during the third week, but keep in mind that during a crisis you might not get paid!
- [Financial Position] Outstanding accounts payable (AP): money that you owe to vendors and other service providers, ordinarily due in 30 days or less. In this model, the default is that you pay your AP in full during the third week.
- [Loan Details] Small business loan injection: the dollar value of a loan for your small business; $0 by default, so you can easily see the impact of a loan by filling in this number.
- [Loan Details] Loan term: the duration of the loan, generally at least one year.
- [Loan Details] Loan interest rate: the interest rate of the loan.
- [Loan Details] Loan payments: how many payments you need to make on the loan per year. Twelve annual payments (one per month), are typical.
- [Loan Details] Calculated loan payment amount: this number is auto calculated; the monthly payment amount for your loan.
- [Sales data] Average weekly sales (historical): your businesses average weekly sales, based on historical data.
- [Sales data] Low week (historical): a low sales week for your business, historically.
- [Sales data] 50% reduction in demand: this number is auto calculated, a 50% reduction on your low week.
- [Sales data] 75% reduction in demand: this number is auto calculated, a 75% reduction on your low week. Some businesses have seen a 95% reduction in demand, so feel free to adjust this formula based on your needs.
If you are a small business owner, at the very least, I hope this model provides a starting point to navigate this crisis. You can also make adjustments to the model, for example, moving or delaying your rent payment to see how it impacts your cash position. If you are a small business that’s in trouble, I recommend reaching out to the landlord and other vendors to request payment extensions and/or contacting your financial institutions to try and get a loan. The Small Business administration also has an info page here with resources to help navigate the crisis.
My heart goes out to anyone that is struggling during this strange time. Please reach out to me at [email protected] if I can be of any assistance.