Entrepreneurship means rowing multiple boats all at the same time.
To make your idea a success, you should have a strong grip on the finance, operations, sales, and marketing of your business.
Microsoft Excel is one of the best tools you can use to keep a firm grip on your finances so long as you know how to use it.
Given that only 48% of office workers have ever received any formal Excel training per Acuity Training, they are here to explain the six things you must know about Excel as an Entrepreneur.
You May Read: Best Advice and Tips for First-Time Entrepreneurs
1. XIRR
XIRR is a financial function to calculate the internal rate of return. It gives you the flexibility to define the dates of exactly when each cash flow took place.
Compared to IRR, it is more accurate due to the reason that it does not assume the periods of cash flows to be equal.
=XIRR(values, dates, [guess])
Values: Refers to the actual cash flow values
Dates: Individual dates for each cash flow
Guess: Expected rate of return. It is set to 0.1 (10%) by default
Let us use the XIRR function to calculate the internal rate of return for the following cash flows.
Application For Entrepreneurs
XIRR is a handy function that keeps you informed about the rate of return that your business is generating on a project. Entrepreneurs can keep track of the actual returns to compare them with the financial projections made before starting the business.
2. XNPV
XNPV is used to calculate the net present value of the business by considering cash flows, their timing, and the cost of capital.
Unlike NPV, XNPV is more precise and more flexible because it lets you define the exact date of each cash flow and doesn’t require them to be evenly spaced.
=XNPV(rate, values, dates)
Rate: Refers to the interest rate as a percentage
Values: Refers to the cash flow values
Dates: Range of dates for each cash flow
Below is an example where a series of cash flows is evaluated to derive the net present value of the business.
Cell F3 contains the Rate, cell B3:B7 contains the Values and cell C3:C7 contains the Dates of cash flows.
Application For Entrepreneurs
XNPV is another great function for entrepreneurs that enables them to evaluate their business based on actual business activities and compare them with the projected outcomes. It can also be used to compare different business scenarios involving different cash flows. It shows whether a business activity earns a rate of return above the cost of the capital used to finance it.
3. XLOOKUP
The XLOOKUP is among the latest functions that Microsoft Excel has introduced in its Excel 365 and later versions. It is flexible enough to replace the existing lookup functions like VLOOKUP, HLOOKUP, and LOOKUP.
It is used to lookup up your desired values from single or multiple workbooks having multiple rows and columns.
XLOOKUP can be useful when you need to:
- Search the value from the left or right of the workbook
- Search the value from the horizontally or vertically managed data
- Search the value in reverse order
- Search values from multiple workbooks consolidating them into one
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Lookup_value: The value that is to be searched from the data
Lookup_array: Refers to the range where the target value will be searched
Return_array: Refers to the range for the output value if the target value is found
If_not_found: [Optional] Value to be returned if the target value is not found
Match_mode: [Optional] 0 = exact match (by default), -1 = exact or next closest match, 1 = exact or next larger match, 2 = wildcard entries
Search_mode: [Optional] 1 = look up from first to the last value (default), -1 look up from the last to the first value
For a more detailed explanation see this article: https://blog.coupler.io/excel-xlookup/
Application For Entrepreneurs
Considering the flexibility and versatility of XLOOKUP, it is a bit of a Swiss army knife for entrepreneurs. It solves many problems such as consolidating the data split across multiple workbooks.
4. PMT
PMT is another financial function in Excel. It lets you calculate the periodic installments for the repayment of a loan.
PMT calculated the periodic payment by considering the loan amount, the number of repayments, and the interest rate.
The returned value is inclusive of the principal and loan amount. PMT function considers the number of repayments and interest rate as constant throughout the loan cycle.
=PMT(Rate, Nper, Pv, [Fv], [Type])
Rate: Interest rate for the borrowed amount
Nper: Total number of repayments
Pv: Present value of the loan amount
Fv: [Optional] Remaining amount at the end of the tenure. By default, it is set to 0.
Type: [Optional] Timing when the repayment will occur. 0 indicates that the payment is made at the end of the period while 1 indicates the payment made at the beginning of the period. It is set to 0 by default.
Now, let’s use the PMT function to calculate the monthly repayments for a loan amount of £30,000 borrowed at an interest rate of 7% for 10 years.
Pay attention to the conversion of interest rate and loan duration from annual to monthly. Because we wanted to calculate the monthly repayments, we changed other parameters accordingly.
Application For Entrepreneurs
The PMT function allows entrepreneurs to calculate the cost of different borrowing options and compare them.
5. LEFT / RIGHT / MID
These functions work well when you need to process textual information. They are helpful to extract the required number of characters from a text string.
- LEFT
It is used to extract the required number of characters from the left side of the text string.
=LEFT(text, [num_chars])
Text: Text from which the characters will be extracted
Num_chars: Number of characters to be extracted
- RIGHT
It is used to extract the required number of characters from the right side of the text string.
=RIGHT(text, [num_chars])
Text: Text from which the characters will be extracted
Num_chars Number of characters to be extracted
- MID
It is used to extract the required number of characters from the middle of the text string.
= MID(text, start_num, num_chars)
Text: Text from which the characters will be extracted
Start_num: The starting point for extracting the characters
Num_chars: Number of characters to be extracted
In the above examples, we used these functions to break the “Invoice Number” into its components.
Application For Entrepreneurs
There are lots of situations where entrepreneurs have to work with text in spreadsheets. For example in e-commerce spreadsheets showing the product name, title, and meta description for thousands of web pages aren’t unusual. Similarly with stock listings.
Working with that text is quick and simple using these types of formulas.
6. CONCATENATE
CONCATENATE is used to combine values of different cells into one. The combined value would be in the form of textual data.
=CONCATENATE(text1, [text2], …)
TEXT1, TEXT2, ….: Refer to the values in individual cells that are to be joined together.
In the above example, we have joined the individual components “Invoice ID”, “Store State”, and “Store ID” to make the “Invoice number”.
Application for Entrepreneurs
As discussed above working with text is an everyday occurrence for entrepreneurs.
Some possible use cases of CONCATENATE for entrepreneurs may include:
- Combining the first and last name column to generate the column for full names
- Joining together the different components of the product database to generate a unique column for further analysis
Apart from these scenarios, it can be used in any situation which requires joining multiple values of different cells into one.
Final Thoughts
Excel is enormously flexible which means that entrepreneurs need to master the basics to ensure that they stay on top of their business’s finances.
Other areas that entrepreneurs would do well to master are shortcuts and also Excel graphs to visualise their business data.
By leveraging Excel in taking decisions, entrepreneurs can boost their efficiency exponentially.