As one of the more common and easy mistakes to make, hardcoding values in a financial model not only demonstrates a lack of experience but also alludes to a fundamental misunderstanding of the model’s purpose.
The rule is that only revenue and costs should be inputted manually; the rest of the model should then adjust according to these inputs. If additional values are hardcoded, you increase the complexity of the model, make it more difficult to go back and change inputs, and risk hiding certain assumptions. Perhaps, worst of all, hardcoding values showcases a lack of discipline. Other individuals or investors might be skeptical of your capabilities and doubt the reliability and transparency of your company.
To help alleviate some of these issues, double-check that only two values are hardcoded: revenue and cost (sometimes called expenses assumptions). Remember that the majority of your model should come from interlinked cells and formulas that update dynamically given different inputs.
If adding additional hardcoded values is absolutely necessary, make sure to group the fields together in the same tab titled “inputs” and apply a different formatting to them, they usually are blue.
Typically the “inputs” is "assumptions" tab should be first in the Excel file since it determines the results of all subsequent projections. Good organization reduces the complexity of your model, making it more accessible to different viewers. This is also important since models can be shared, presented, and printed.
Remember organization and formatting are not technical issues that require additional skills or expertise. Simply put, they require a little more time, discipline, and empathy for others viewing your work. Similar to how a SaaS product must be clear and understandable to potential users, make the same effort in your financial model if you’d like to reduce any possible friction.
Sometimes some of the easiest mistakes to make are also the simplest. Pointing to the wrong cell or confusing two formulas can produce unfortunate errors. For example, confusing COUNT() for COUNTIF() would result in counting all numeric values in a given range instead of only counting the values that match a certain criteria.
Some other common syntax errors include:
One possible solution is to use checks throughout the spreadsheet to verify calculations. Similar to the inputs tab, still keep all checks in a single tab to stay organized. Also remember that the Balance Sheet should always balance, so the sum of all assets should equal the sum of all liabilities and equity.
Try to avoid long formulas as much as possible if you want to both reduce the complexity of your financial model and spend less time double checking calculations. Don't fall for another common mistake by using nested if conditions. While perhaps necessary at times, nested IF’s are more likely to contain errors and reduce the readability of your model.
Keep your model simple. One of the best fixes is to break the problem down into smaller pieces. You can do this by using multiple columns to create a formula that will either return a single or multiple values.
A daisy chain is when data is drawn from multiple sources despite being available from a single input tab. In other other words, there's a series of links that do not directly link to the original source. Not only does this force the viewer to search through the spreadsheet to follow the logic, but it also increases the size of the file.
Similar to formatting, the naming convention you use to save your files can be the difference between wasting time in file explorer or already presenting your model.
Always try to reduce the possibility for human error. Of course this does require some discipline. It’s much too easy to name a model under “model_4” and forget its purpose entirely. Instead try to be rather specific as you name your files. Keep in mind the extra time spent naming your files decreases the chance of presenting an incorrect version in the future.
One last common mistake involves calculating incorrect sums by “double counting” values. For example, a column might contain the values of different assets with subtotals in the same column. If you were to simply calculate the sum of the values, =SUM(C1:C7) the subtotals would be included and double the sum.
To avoid this, use the SUBTOTAL function to add together all values that are not already subtotals. =SUBTOTAL(C1:C7) would calculate the correct sum even though the same values are specified as in the last example.
Creating a financial model is crucial but it’s easy to make mistakes. Without accuracy, transparency, and reliability, a financial model can be deceiving. If you’d rather not worry about making these calculations or errors, check out Sturppy’s financial modeling tool. Using AI, you’ll never need to worry about daisy chains, syntax errors, or organizing your model. Sturppy even lets you export your data directly to investors.