Here are three core formatting best practices you can apply for financial models and data analyses in Excel and Google Sheets.

[Formatting cheat sheet with examples (free download)](https://docs.google.com/spreadsheets/d/1lPK9rY2XF0C5kbo1EtUGiCEvozOfaUPxt9zilfthTZc/edit#gid=0)

## 1. Color Coding Cells
- <span style="color:black;border:solid 1px;border-width:1px;padding:0px 2px">Black</span> = formula, calculations and links to other cells
- <span style="color:blue;border:solid 1px;border-width:1px;padding: 0px 2px">Blue</span> = hard coded input cells and historical data
- <span style="color:blue;border:solid 1px;border-width:1px;padding: 0px 2px;background:yellow">Blue with yellow background</span> = key input assumptions
- <span style="color:green;border:solid 1px;border-width:1px;padding: 0px 2px">Green</span> = link to other spreadsheets in the same file

__Example - Big Tech Company Metrics__
![COLORCODE](//images.ctfassets.net/vwq10xzbe6iz/1B5GvkyNo7NBDedwEllnDM/1a7fa72588abd089f5d470992a41baef/COLORCODE.png)

## 2. Financial Tables
- __FY20XX__ = Use financial year end for your column headers, include the year end and metric in the table header
- __Revenue__ = bold important headline metrics (Revenue, EBITDA, EBIT, NPAT)
- Cost of Revenue = indent sub metrics used to calculate headline metrics (e.g. COGS)
- Financial formatting = use brackets for negative values and avoid more than 1 dp
- Left to right = older to newer FY dates ordered left to right

__Example - Alphabet Income Statement__
![TABLE](//images.ctfassets.net/vwq10xzbe6iz/1HaM9TUGNg0UkczOVAIDgl/98d7bb87c0cda476c90562f9a85056f5/TABLE.png)

### 3. Key Metrics
- __FY20XX__ = Align your key metrics under the same columns as the financial table
- *Revenue growth yoy* = italic text for key metrics
- *23%* = italic text including the unit metric (e.g. %, x)
- <span style="color:gray;border:solid 1px;border-width:1px;padding: 0px 2px;background:gray">Grey</span> = grey out cells if computed metric not able to be calculated

__Example - Alphabet Income Statement Key Metrics__
![METRIC](//images.ctfassets.net/vwq10xzbe6iz/jKJYZgcCeyTjKK6kjKaR8/e98f783e10d0b4fb8fc2326d4de988ce/METRIC.png)

[Formatting cheat sheet with examples (free download)](https://docs.google.com/spreadsheets/d/1lPK9rY2XF0C5kbo1EtUGiCEvozOfaUPxt9zilfthTZc/edit#gid=0)

Investment Banking Excel and Google Sheet formatting best practices for building spreadsheets and financial models: 1) Color Coding Cells; 2) Financial Tables; 3) Key Metrics

Financial Model Formatting - 3 Core Best Practices

![fininte state machine](//images.ctfassets.net/vwq10xzbe6iz/6JNh1Rpi6a1VUnTux03TzW/c69b8b54a1be48ec820cec8e721163af/fininte_state_machine.png)

Finite State Machines allow cross-functional teams to visually design complex logic before committing expensive resources to design and code implementation.

It is a powerful tool for product teams and business owners to communicate on customer requirements in order to identify rabbit holes and poor UX early in the process.

They have been widely used in electrical engineering and in the logic design of electronic devices for decades. 

To illustrate the power of Finite State Machines, imagine we have been briefed by the business to build a customer feedback form widget.

## Phase 1 - Simple Feedback Widget
The first phase of our brief is for a simple feedback widget that will do the following:
- Ask for positive or negative feedback
- If positive display a happy success message
- If negative display an apology
- Allow user to cancel the feedback form

We can visualise this simple application using Finite State Machines below:
![phase1](//images.ctfassets.net/vwq10xzbe6iz/3aRVidpZK6c6qevtvEWLfB/9a09666917aeeddc3170c9a4ef80cfcf/phase1.png)

Our application has 4 states:
1. __START__ - our initial state where a user can click to begin the survey
2. __RESPONSE__ - where a user is asked for a positive or negative response and where they can cancel to go back to START
3. __POSITIVE CONFIRM__: an end state upon a user clicking POSITIVE
4. __NEGATIVE CONFIRM__: an end state upon a user clicking NEGATIVE

Our 4 states are also characterised by 4 events and transitions which are conditional upon the current state the application is in.

Our application also triggers actions such as saving the rating and updating the message upon certain events and entering different states.

Once the logic and user flow has been agreed we can easily convert this to design and code stage. In the examples below we use the open source library [XState](https://xstate.js.org/docs/) by [David Khourshid](https://github.com/davidkpiano) to codify our Finite State Machine and use ReactJS to render our UI.

<iframe src="https://codesandbox.io/embed/feedbackphase1-stkhm?fontsize=14&hidenavigation=1&theme=dark&view=preview"
     style="width:100%; height:500px; border:0; border-radius: 4px; overflow:hidden;"
     title="feedbackPhase1"
     allow="accelerometer; ambient-light-sensor; camera; encrypted-media; geolocation; gyroscope; hid; microphone; midi; payment; usb; vr; xr-spatial-tracking"
     sandbox="allow-forms allow-modals allow-popups allow-presentation allow-same-origin allow-scripts"
   ></iframe>

## Phase 2 - Negative Feedback Reason
The business has now come back asking for us to capture the main reasons for negative feedback.

Our application now must:
- Ask the user to select the main reason for negative feedback (Service, Product, Price)
- Also allow a user to select POSITIVE again if they accidentally selected NEGATIVE

With Finite State Machines we can easily modify and expand our logic without breaking any previous logic:
![phase2](//images.ctfassets.net/vwq10xzbe6iz/38VI5AsXioynO833DlaAzM/9f8c7edb547374d6044b6bff26400c85/phase2.png)

Our application has a new __NEGATIVE FEEDBACK__ state which allows a user to select from three of the main reasons for the negative response (SERVICE, PRODUCT or PRICE). Upon selecting an event the reason is captured and the state transitions to the NEGATIVE CONFIRM end state and displays an apology message.

We have now also added a new POSITIVE event which a user can click on to transition directly to the POSITIVE CONFIRM end state.

Our team can now confidently update our application with the new business logic:

<iframe src="https://codesandbox.io/embed/feedbackphase2-lg6sy?fontsize=14&hidenavigation=1&theme=dark&view=preview"
     style="width:100%; height:500px; border:0; border-radius: 4px; overflow:hidden;"
     title="feedbackPhase2"
     allow="accelerometer; ambient-light-sensor; camera; encrypted-media; geolocation; gyroscope; hid; microphone; midi; payment; usb; vr; xr-spatial-tracking"
     sandbox="allow-forms allow-modals allow-popups allow-presentation allow-same-origin allow-scripts"
   ></iframe>

## Phase 3 - Advanced Feedback
The business comes back wanting to direct users to contact sales for pricing discussions if they select PRICE as a negative response. In addition they want to capture any other reasons for negative feedback.

Our application must now grow in complexity to handle the following:
- If user selects PRICE as a negative response, send to different end state with pricing discussion CTA
- New OTHER option for a user to select with ability to leave comment

Once again we modify our Finite State Machines to expand our logic
![phase3](//images.ctfassets.net/vwq10xzbe6iz/6wb9cvXQflR8QIBGul2SOU/b8e775fd2c86f028a3c4652602219969/phase3.png)

Our application has now added two new states:
1. __PRICE CONFIRM__: end state when a user selects PRICE as a negative response. We will display a contact number for pricing discussions. Creating a new end state provides us with flexibility if the business may want to display discount offers or ask the user for more contact information in the future
2. __COMMENT__: when a user selects OTHER as a new reason for their negative response. This state includes three events where a user can UPDATE COMMENT to capture the reason, SUBMIT COMMENT to transition to end state and CANCEL to go back and select a different reason

Once again, our Finite State Machine is able to handle increasing complexity without breaking previous logic. Our designer and developer team can now confidently work on implementing the new logic:

<iframe src="https://codesandbox.io/embed/feedbackphase3-8y5dx?fontsize=14&hidenavigation=1&theme=dark&view=preview"
     style="width:100%; height:500px; border:0; border-radius: 4px; overflow:hidden;"
     title="feedbackPhase3"
     allow="accelerometer; ambient-light-sensor; camera; encrypted-media; geolocation; gyroscope; hid; microphone; midi; payment; usb; vr; xr-spatial-tracking"
     sandbox="allow-forms allow-modals allow-popups allow-presentation allow-same-origin allow-scripts"
   ></iframe>

## Future Complexity
Our application will naturally grow in complexity as business and user requirements evolve. 

Examples may include:
- Ask for feedback for positive response
- Invite user to add users after a positive response
- Ask user to provide the name of the poor service representative
- Introduce a passive response in addition to positive and negative
- etc.

With Finite State Machines we can confidently continue modelling this advanced complexity into our design and visually see how it interacts with previous functionality.


How Finite State Machines allow cross-functional teams to visually design complex logic before committing expensive resources to design and code implementation.

Finite State Machines - A Common Tool for Product Owners, Designers & Developers

Why do most startups fail to find product market fit? 
![beware_solutions_seeking_problems](//images.ctfassets.net/vwq10xzbe6iz/2rGuWsynYvglJNeJeWcQoz/b11da185061ef24f957efe1f9683d4f8/beware_solutions_seeking_problems.png)

Because our [confirmation bias](https://mannhowie.com/cognitive-bias) creates tunnel vision towards our existing solutions. Instead we should frame our focus on solving the user’s struggle.

Here are 5 questions you can ask in user interviews to validate if your startup has found product market fit.

Imagine we are a startup building solutions for mortgage brokers.

## 1. What progress are you trying to make in X?
__*Example:*__
__Q.__ What progress are you trying to make in finalising loan applications?

__A.__ I’m looking for my team to close more loans and shorten the bank approval times.

## 2. What is your biggest struggle when trying to make progress in X?
__*Example:*__
__Q.__ What is your biggest struggle in getting bank approvals completed on time?

__A.__ Document gathering with our customers. A single important lost or incomplete document can slow the whole process down and cause frustration amongst both lenders and borrowers, both of whom are our customers.

## 3. Tell me about the last time you encountered this struggle?
*__Example__*
__Q.__ Tell me about the last time you encountered this struggle? (create a timeline of the event as if you were in their shoes)

__A.__ Last month one of our new starters had a backlog of 10 applications with one of our newer lenders. An applicant customer did not submit a key piece of information that the new lender required but this only came to light a couple of weeks after the applicant had already submitted all the documents we asked. The lender was frustrated that we missed the information (as they said they had asked us for this before) and the applicant was livid as they had already terminated their rental lease.

## 4. What solutions have you previously tried?
__Example:__
__Q.__ What solutions have you previously tried to avoid or manage this struggle?

__A.__ We’ve used shared cloud storage solutions like Google Drive and Dropbox, onboarding checklists to make sure we don’t miss things and our CRM tool to keep track of emails.

## 5. What didn’t you love about those solutions?
__Example:__
__Q.__ What don’t you love about those solutions you’ve previously tried?

__A.__ Some of the tools create more work for our team in managing the document workflow and version control, we still have to review all the documents to make sure they are complete. As a result we still rely heavily on email to follow-up and confirm next steps with both lenders and customers but then it’s easy for us to miss things and drown in emails.

## Bad User Interview Questions
User interviews to find product market fit should be framed around the user’s struggle and not solution ideas. 

Bad user interview questions include:
- What ideal solution would you like to solve your struggle? ❌
- Do you have any feedback on our solution? ❌
- Would you use this solution if we offered it? ❌
- What do you think we could do to improve your process? ❌
- Do you know anyone else in your company who would use our solution? ❌

## User Interview Insights
Following the insights from our 5 user interview questions we are now better able to frame the struggle for our mortgage broker manager as:

> “Communicating between lenders and customers the missing information needed to complete a loan application”

Solutions we can now explore to solve the problem could include:
- Assignees for onboarding docs (whose court is it in now) 💡
- Automatic email reminders of incomplete docs 💡
- Auto-logging emails to capture a digital chat history 💡
- Identify which docs have not been updated in a while 💡

Framing the challenge around the user struggle allows us to continue iterating on solutions to help users make progress instead of being fully invested into one solution. 

⚠️ Beware solutions seeking problems.


5 user interview questions to validate product market fit: 1) What progress are you trying to make?; 2) What is the hardest thing about making progress?; 3) Tell me about the last time you encountered the struggle; 4) What solutions have you previously tried? 5) What didn’t you love about those solutions?

Howie Mann
Startup & Investing Advice
About Articles Stocks

About me

Recent Articles

Financial Model Formatting - 3 Core Best Practices

Finite State Machines - A Common Tool for Product Owners, Designers & Developers

5 Questions to Ask to Find Product Market Fit - User Interviews

Howie MannStartup & Investing AdviceAboutArticlesStocks

About me

Recent Articles

Financial Model Formatting - 3 Core Best Practices

Finite State Machines - A Common Tool for Product Owners, Designers & Developers

5 Questions to Ask to Find Product Market Fit - User Interviews

Howie Mann
Startup & Investing Advice
About Articles Stocks