The Startup Station Insights - Excel Tricks. How to Model Various Business Scenarios

A financial model is a quantitative representation of the company’s business plan. In a way, it is the company’s brain, demonstrating how its strategic decisions affect different functions and translate into financial results.

For that brain to work properly, we need to correctly create the step by step instructions of what happens with the company as a result of its strategic decisions, or financial model assumptions. The process of creating such assumptions and logic is the expertise of The Startup Station and the crux of our unique methodology.

The final step is the implementation of this logic, and this requires knowledge of Excel.

Here are four most useful Excel tricks you can use for modeling a variety of business scenarios.

• # 1: HLOOKUP/ VLOOKUP

These two functions are extremely useful when you create assumptions that change with time or based on some condition. They are interchangeable – which ones you use depends on how you organize your data. HLOOKUP is used to look up a column in a single row, and VLOOKUP is used to lookup a row in a single column. 

HLOOKUP EXAMPLE: Customer acquisition cost decreases with time.

VLOOKUP EXAMPLE: Cloud computing cost varies with the number of users

• # 2: SUMIF, AVERAGEIF, SUMIFS, AVERAGEIFS

These functions are extensively used in data aggregation. They will produce a sum or average, based on one or several conditions.

EXAMPLES:

SUMIF can be used to find out how many new subscriptions you estimate to convert each year, where the IF condition is the year and you are summing new subscriptions.

SUMIFS can be used to find out how many orders exceeded $150 per year. In this case, the first IF condition is the year, the second IF is for the order value to be greater than $150, and you are summing out new orders.

AVERAGEIF can be used to find out the average revenue you make per customer per year, where the IF condition is the year and you are averaging out revenue per customer.

AVERAGEIFS can be used to find the average revenue you make per customer per year, excluding those months when there was no revenue so that the data is not distorted. In this case, the first IF condition is the year, the second IF is for the revenue per customer to be positive, and you are averaging out revenue per customer.

• # 3: IF ELSE

One of the most useful Excel functions in financial modeling is the “IF-ELSE” statement. It is used when you want to specify a specific outcome depending on one or more conditions, often dynamically determined by the model. You can use as many conditions as needed.

EXAMPLES:

If the revenue in any month exceeds $10,000, there is an additional cloud computing cost of $5,000.

If the revenues for the last 12M months exceed $5M, start the data monetization revenue stream. 

Note that to implement the condition of the last 12M, you will anchor the first month and copy the SUMIF formula, until it covers 12M in a range, and then remove the anchor to copy for the remainder of the time covered by the model.

If the funding comes in, hire the CTO. Here you can set the month and year when the funding is supposed to come in as an assumption which can be changed. You can then dynamically determine in the model when that happens.

• # 4: ISNUMBER, ISTEXT, ISERROR, ISNA

These functions are incredibly useful to make the formulas work in all cases, regardless of the input and output.

EXAMPLES:

If you are looking up a value in the table which does not exist, the result will be NA. You can use the ISNA function to catch it and describe what to do in this situation. If the result of the HLOOKUP in TABLE 1 is NA, use HLOOKUP in TABLE 2.

If you are calculating profit margins, the result will be an error for cases when there is no revenue. You can catch that result and describe what to do in this situation. If the result of the division is ERROR, then NA.

These are just some of the many scenarios which can be modeled using the 4 sets of functions above.

If you would like us to review your financial model and see if you correctly translated your vision into a financial plan, book a 30 min consultation now and we will be happy to do it!

About Author

Victoria Yampolsky, CFA, is the President and Founder of The Startup Station, a comprehensive resource for modeling and valuing early-stage startups. She evaluates the financial feasibility of business models and specializes in the financial modeling and valuation of pre-revenue companies. She also created a finance curriculum for early-stage founders and launched The Startup Station’s educational program in 2015. Since then, more than 1,000 founders have attended her online and in-person finance classes and learned the basics of financial modeling, valuation, and startup financing.

Previously, Victoria worked for the Deutsche Bank Research Department and performed IT consulting for CapGemini’s Financial Services Division. Victoria holds a Bachelor’s Degree, Cum Laude, in Computer Science, with a minor in Mathematics, from Cornell University and an MBA, with honors, from Columbia Business School. Victoria is also on the Advisory Board of the Computing and Information Science (CIS) Department of Cornell University.