Business plans involving anything from bank loans to venture capital often forecast cash position through a pro forma cash flow statement. Starting with an initial cash position, subsequent positions are calculated by adding expected sources and subtracting expected cash outlay for each month. Needless to say, the actual sources and outlays of cash are not always forecast with great accuracy especially for a start-up.

It is the month of May. You would like to start a company but are not sure how much cash you need to be successful. You have $200,000 but are very uncertain if that is enough cash to start a company. You estimate that your idea will generate on average $500,000 cash each month. You also estimate that your cash out (expenses, employee salaries, rent, food, travel, etc.) is on average $450,000 per month.

Although your idea has huge potential, you know that if in any month cash (sales) does not come in the way you want and/or if there are any unplanned expenses (cash out)....you won’t have enough cash to conduct business and will "go broke" (negative cash implies bankruptcy). Simply stated, if you run out of cash in any month, your company ceases to exist (and lose the $200,000 you started with). Your goal is to understand how much cash is needed so you don't start the company "too soon"....but....at the same time...you want to start the company as soon as possible before someone else with the same idea goes to market before you do.

Given your risk attitude, you need more guidance and consult a friend. This friend has launched a company previously. Your friend advises that you should assume that both ‘Cash In’ (sales) and ‘Cash out’ (expenses) are normally distributed with a Coefficient of Variation (COV) of 25% each. Your friend states that with these assumptions, you should have a good understanding of how much cash you will need to avoid ‘going broke’.

Instructions:

Enhance this going_broke.xlsx to a spreadsheet model that will answer the question of the probability of “going broke” (running out of cash for any given month).

1. Enhance the spreadsheet using the following business logic:
   • Start Month is June.
   • ‘June Net Cash’ equals ‘Initial Cash’ + ‘June Cash In’ – ‘June Cash Out’.
   • 'July Net Cash’ equals ‘June Net Cash’ + ‘July Cash in’ – ‘July Cash Out’. Remaining months use same logic.
   • Lowest Cash – min/minimum of ‘Net Cash’ for any of the months. Lowest Cash will be your formula to estimate the probability that if in any month you have negative cash (aka running out of cash, hence, "broke")
2. Identify the Uncertain Inputs by shading the cells with a color of your choosing.
3. Using previous assumptions(your assumptions and your friends assumptions), assign Distribution/Shape to each Uncertain Input.
4. Identify the model output by shading the cell with a color of your choosing (a color other than what was used for your uncertain inputs).
5. Run Simulation (10s of thousands of trials) and create both a histogram and cumulative plot graph (make sure chart is readable/labeled).
6. Answer the following questions/'complete task' (provide answers and analysis in Presentation file):
   • “What is the probability that you will ‘go broke’ (probability of negative net cash) based on your current initial cash"?
   • Complete the following table (preferably present with a column/bar plot)

     Initial Cash	Probability of "Going Broke"
     $200,000
     $300,000
     $400,000
     $500,000
     $600,000
     $700,000
     $800,000
     $900,000

   • Given your risk attitude and the goal of starting the company asap, how much initial cash do you need that gives you ~5% probability of going broke? Present decision using cumulative plot.

	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Cash In
Cash Out
Net Cash
Initial Cash	$200,000
Lowest Cash

Answer rating: 100% (QA)

To solve this problem effectively you can use a spreadsheet software like Microsoft Excel or Google Sheets Heres a stepbystep guide to enhancing the p... View the full answer