When competition gets tough in the business world, even the smallest advantage proves critical, and this is where your financial data becomes extremely valuable. Excel remains as a powerful instrument that you can use to monitor your data, so mastering it should be one of your top priorities. Here are some important Excel tips that can get you started on the road to success.
Templates
There are many useful templates out there. We shared a few Excel templates for project management and tracking. Financial or fiscal calendars are necessary for tracking corporation tax and government financial statements. Additional information that can be includes payment dates, due date, employee form deadlines, and much more. SourceForge has actually crafted some nifty calendars that you can refer to when making your own. Aside from tracking your financial due dates and obligations, you can also take cues from economic calendars. The extensive economic calendar featured on FXCM actually tracks important economic events such as policy changes and financial reviews. If your business or supply chain is dependent on seasonal fluctuations and other macroeconomic factors, indicating these dates in your financial calendar can help you make important decisions and time them accordingly. Another type of template that you can integrate within your plan is an issue-tracking calendar, preferably one in a Gantt chart-like format. Issue tracking templates allow you to isolate particular issues and monitor how you resolve them, including the resources you dedicate to solving it.
Formulas
Formulas are crucial for monitoring financials, as they allow you to number crunch and get valuable insights. Here are some of the more important formulas:
1. XNPV (Net Present Value)
Net Present Value allows you to determine what the company is worth. You can determine the Net Present Value of a series of cash flows with the =NPV() formula, but =XNPV() allows you to be more precise because it takes into account specific cash flow dates.
2. XIRR (Rate of Return)
The Internal Rate of Return (IRR) is the discount rate that sets the Net Present Value of all future cash flow to zero. You can get the IRR with the =IRR() formula, but that formula assumes equal time periods between each cell. To accommodate more flexible time periods, use the =XIRR() formula using two sets of data, the first being the series of cash flows, and second being the corresponding dates of each of the cash flows.
You can also use =MIRR() in which M stands for modified. You can use this when the cash from one investment is put into a different investment.
3. FV (Future Value)
If you want to know how much money you will have after a set amount of time, given a starting balance, payments, and a compounding interest rate, you can use =FV(). FV captures rate, number of periods, payments, starting value, and type.
4. EFFECT (Effective annual interest rate)
Use the formula =EFFECT(interest rate, # of periods per year) to determine the annual effective or 'real rate' of interest of your investments or loans. The formula uses the original rate of interest and the number of times that interest has been compounded. Tech on the Net has a full list of formulas and functions here.
Data validation
When building a financial model, you need to ensure data integrity. Fortunately, Excel has this feature. In Excel, data validation can be found in the Data Ribbon. You can use Input Message to alert users of your own message when there is invalid data. Criteria can be set by selecting the input that needs to be validated and then setting the valid ranges in Settings.
Conditional Formatting
Like Data Validation, Conditional formatting is a powerful tool in organizing and visualizing huge amounts of data for analysis. Formatting in Excel can be done with icons, colors, and data bars.
To format your data, first of all you need to set a conditional formatting rule. First, select the cells that your want to format. Click the Home tab and select Conditional Formatting. Select your desired formatting type, then select the rule. A dialog box will appear. This is where you will set the desired values. You can also use formatting presets or styles from the Conditional Formatting drop-down menu. You can manage multiple rules in the Manage Rules tab.