Finance tools for Engineering Team Leaders, Lab Managers, and Analysts
To manage portfolios worth millions of dollars and maintain a positive cash flow sounds like a role of financial analyst. They are often employed by pension funds, banks, securities firms to help companies or clients by making financial investment decisions for them. To analyze financial data, developing forecasts and spot trends, they rely on financial software tools such as excel, power point, word. Other tools called Capital IQ, SAS etc are also utilized for data and analytic platforms. Keep in mind to not mix financial analyst with “quants”, they deal with mathematically intensive elements. They focus on mathematical modeling and concerned with derivatives, fixed income analysis and financial risk management.
Chartered Financial Analyst (CFA)
So, if you have worked as a financial analyst for bank or company for 3 years or minimum of 4,000 hours and taken three individual level exams, adhere to the CFA Institute Code of Ethics and Standards and a member of the CFA institute, then you have earned a designation of chartered financial analyst charter. The CFA institute was established in 1947 and many CFA charter holders with years of experience were grandfathered in through work without taking any kind of exam. The first CFA exam was administered in 1963 and has become global across Europe, Asia. They are qualified to work in senior and executive positions in investment management, risk management, asset management, and more.
10-K report
Public companies have to file a comprehensive report about financial performance annually; it is called 10-K. They are available as public information through a number of sources. These reports include information such as corporate history, financial statements, and earnings per share. It is considered a useful tool for investors and required by the U.S. Securities and Exchange Commission (SEC). Along with these reports, you have to file 10-Q and 8-K. The form 8-K includes information such as company announcement of major events, elections of executives, bankruptcies, assets and other relevant news while 10-Q on the other hand contains comprehensive report of a company’s performance and company’s financial position.
If the company float between $75 million and $700 million must file 10-K within 75 days while companies with a public float of $700 million or more have 60 days to file 10-K at the end of their fiscal year.
Excel VB Macro and Functions
To create custom user generated functions and speed up manual tasks, you can use VB Macros. The Microsoft event driven programming language claimed its legacy in 2008. After 12 years, it was considered the most dreaded language for developers. It interacts with the host application which provides a library and application programming interface (API) documentation. It can establish reference to word and outlook libraries.
Now, let’s take a look at few examples to understand the concept of finance.
Loan Schedule
Imagine a person wants to buy a house that costs three hundred thousand dollars. He wants to assess whether this is feasible. After talking to some friends and seeing several mortgage loan advertisements he concludes the current market rates are between 2 percent and 4 percent. So he makes his preliminary calculations by using a 3 percent rate fine. He wants to repay the loan in 10 years by making equal monthly payments throughout this period. Here is the million dollar question how much will his fixed monthly payment be for him to extinguish the loan in 10 years. Microsoft Excel VBMacro can fully answer this question. Let’s type the inputs on this sheet. The number of periods during which the loan will be repaid is one hundred twenty given that the person will make equal monthly payments for 10 years. The interest rate he will have to pay for borrowing the money is 3 percent annually to obtain the monthly interest rate. As usual one should divide the annual interest rate by 12. And finally the amount borrowed is three hundred thousand dollars.
Loan Schedule Practical Example
Internal Rate of Return (IRR)
Internal rate of return or most commonly referred to as IRR is a discount rate used for measuring the profitability of potential investments. The effective rate of return makes the net present value of all cash flows from an investment equal to zero. Pay close attention to the fact that all cash flows must be included in the calculation of IRR positive and negative ones. Let’s use the example to illustrate IRR and how it can be applied. I will directly use excel IRR function and minor VB Macro to accomplish this task. I will select the values for all cash flows in our investment and will click Calculate button to obtain the result. But what does it mean precisely.
Well the present values are calculated at 7 percent IRR calculates the rate I would have had if the NPV was equal to zero. So, if the sum of the present values for the periods from 1 to 5 was equal to the initial investment. Then, in this case 500 instead the sum of the present value of the five cash flows which equals to 433 which is less than five hundred.
Discounting a cash flow value at one plus the internal rate of return to the power of the number of the period. So what would be the financial interpretation of this calculation? Would I consider this a worthy investment if my cost of financing is 7 percent? No because 4 percent is smaller than 7 percent. Therefore, if we carry on with this project, we will obtain returns that cannot cover its cost of financing.
Therefore as long as the internal rate of return is lower than an alternative investment, you should not invest. If the internal rate is higher than the interest rate then investment sounds like a good idea.
Please bear in mind both NPV and IRR are used in the world of finance for assessing the potential profitability of projects.
IRR Practical Example
Revenues and COGS Practical Example
We are given a business case study data model. As financial analyst, we are given the following tasks below. In this example, we use excel built in functions.
Financial Data Model of the client
Task 1: Calculate the company’s annual Revenues and Cogs. Then, provide the company’s Gross Profit which is equal to Revenues minus Cogs. Following below shows the functions to calculate the total revenues for fiscal year 2015.
Solution for Task 1:
SUM(Data!E4:E195)
To calculate the cost of goods (COGS), use the following excel function below.
-SUM(Data!F4:F195)
The Gross profit will be the sum of revenues and cogs,
SUM(C6:C7)
Task 2: Provide a breakdown that shows monthly Revenues and Cogs and calculate monthly Gross Profit. Create an area chart that shows the development of Revenues and Cogs.
Solution for Task 2:
To calculate the monthly revenues, use the following excel function.
SUMIF(Data!$B$4:$B$195,’Task 2'!C$6,Data!$E$4:$E$195) and then copy and paste this function across the rest of the cells for revenues. For Cogs, use the following excel function and repeat the same copy and paste process as you did for monthly revenues.
-SUMIF(Data!$B$4:$B$195,’Task 2'!C$6,Data!$F$4:$F$195)
Afterwards, to calculate the Gross Profit, it will be SUM (C7:C8), copy and paste this function for the rest of the cells of Gross Profit.
What does SUMIF function do?
It returns the sum of cells that meet a single condition. Criteria can be applied to dates, numbers, and text. The function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.
To create the area chart that shows the development of Revenues and Cogs, we will use INSERT and select he area graph and then use the following excel function Chart Data Range =SERIES(‘Task 2’!$B$8,’Task 2'!$C$6:$N$6,’Task 2'!$C$8:$N$8,2)to plot the area graph as shown below.
Task 3: Provide a monthly breakdown of Revenues by type of client and calculate the percentage incidence that each client type has on the company’s Revenues. Create a chart that shows the incidence on Revenues that different type of clients had throughout the year.
Solution for Task 3:
We will use +SUMIFS(Data!$E$4:$E$195,Data!$B$4:$B$195,’Task 3'!C$8,Data!$C$4:$C$195,’Task 3'!$B9) for Supermarkets to calculate the monthly breakdown of revenues by type of client, the same excel function will be used for FastFood and Other as listed below.
FastFood: SUMIFS(Data!$E$4:$E$195,Data!$B$4:$B$195,’Task 3'!C$8,Data!$C$4:$C$195,’Task 3'!$B10)
And Other: SUMIFS(Data!$E$4:$E$195,Data!$B$4:$B$195,’Task 3'!C$8,Data!$C$4:$C$195,’Task 3'!$B11)
Copy and paste these functions for the rest of the desired cells. To calculate the Total revenues, we will use the following excel function: SUM(C9:C11)
What is the difference between relative and absolute cell reference?
Relative Cell Reference: When you are copying and pasting a formula across multiple cells, it will change based on the relative position of rows and columns. So, if you want to repeat the same calculation across multiple rows or columns, the relative references are especially convenient.
Absolute Cell Reference: If you don’t want a cell reference to change then you can use absolute reference. It is designated in a formula by the addition of a dollar sign ($) before the column and row. So, if you don’t want the column and row to change, use $A$3, but if you don’t want the row changed when copied then use A$3 and for the column not to be changed when copied, use $A3.
To calculate the percentage incidence that each client type has on the company’s Revenues is shown below:
Supermarkets%: C$9/C12 (Supermarkets divided by Total Revenues)
Fast Food%: C$10/12 (Fast Food divided by Total Revenues)
Other%: C$11/12 (Other divided by Total Revenues)
Copy and paste these functions for the rest of cells.
Now, we will create a chart that shows the incidence on Revenues that different types of clients had throughout the year by using the following excel function.
Chart Data Range =’Task 3'!$B$8:$N$8,’Task 3'!$B$16:$N$18
3 Financial Statements Practical Example
When you are trying to summarize the revenues, costs, and expenses incurred during a specific period, you create a Profit and loss (P&L) statement. It is one of the three financial statements issued by company quarterly and annually, along with balance sheet and the cash flow statement. It also provides depth in look at a company’s financial performance when you have all three financial statements in front of you: P&L, balance sheet and cash flow statement. So, let’s build a model at a total company level. All we need is mappings that shows P&L categories — Revenues, Costs of Goods Sold, Operating Expenses, D&A, Interest expenses, and Taxes and understand some financial formulas along the way.
Gross Profit: Revenue — COGS
Operating Income: Gross Income — operating expenses
(Earnings before Interest, Taxes, Depreciation, and Amortization) EBITDA: Gross Profit + Operating expenses
Earnings before Income and Taxes (EBIT): EBITDA + D&A
Earnings Before Tax (EBT): EBIT + interest expenses
Net Income: EBT + taxes
Step 1: Mapping
Add a mapping to your sheet. It will allow you to create categories assigned by you. You are not stuck with the original categories you found in the source data. This is a valuable tool.
Step 2: Building an Output P&L Sheet
Paste the data and remove duplicates to obtain a unique list. Forma the P&L sheet professionally and use subtotals and totals.
Step 3: Fill in the output P&L sheet with historical financial data
Use paste special values to paste the data in the P&L sheet. Remove Duplicates to obtain a list with unique values. Copy the unique values and use Paste Special Transpose to paste them. Apply the formatting of columns B to columns C, D and E. Then, use SUMIFs to populate the table. Copy the formula for all financials that are not subtotals or totals, then add a minus sign for costs.
Excel functions used for P&L sheet
Revenue: +SUMIFS(Workings!$E:$E,Workings!$F:$F,’P&L’!$B4,Workings!$C:$C,’P&L’!C$3)
Cost of good sold: -1*SUMIFS(Workings!$E:$E,Workings!$F:$F,’P&L’!$B5,Workings!$C:$C,’P&L’!C$3)
Gross Profit: SUM(C4:C5)
Operating Expenses: 1*SUMIFS(Workings!$E:$E,Workings!$F:$F,’P&L’!$B7,Workings!$C:$C,’P&L’!C$3)
EBITDA: SUM(C6:C7)
D&A: -1*SUMIFS(Workings!$E:$E,Workings!$F:$F,’P&L’!$B9,Workings!$C:$C,’P&L’!C$3)
EBIT: SUM(C8:C9)
Interest Expenses: -1*SUMIFS(Workings!$E:$E,Workings!$F:$F,’P&L’!$B11,Workings!$C:$C,’P&L’!C$3)
EBT: SUM(C10:C11)
Taxes: -1*SUMIFS(Workings!$E:$E,Workings!$F:$F,’P&L’!$B13,Workings!$C:$C,’P&L’!C$3)
Net Income: SUM(C12:C13)
Step 4: Calculation of percentage variances
Calculate percentage variances between 2014–2015 and 2015–2016. Use Paste Special Formats and Paste Special Formulas. Add Conditional Formatting Traffic Lights.
Excel functions used for Variance
Var% 14–15 = D4/C4–1
Var% 15–16 = E4/D4–1
Step 5: Output Balance Sheet
Create a new sheet. Change its name to BS. Open the BS 2016 sheet. Paste the copied cells and improve the sheet’s formatting. Apply total formatting for “Assets” and “Liabilities & Equity”. Add periods “31-Dec-14”, “31-Dec-15”,”31-Dec-16". Adjust the header of the table. Insert a SUM function for Assets and Liabilities & Equity. Add a check below the table. Asset must be equal to Liabilities & Equity.
Step 6: Use Index, Match to fill the Balance Sheet
Delete empty rows and columns to the left and above. Do that in all three input sheets. Open the “BS” sheet. Use Index, Match to fill in the table with Balance Sheet values from the input sheets. Start with 2014 and then copy the formula for both 2015 and 2016. Use Find and Replace to replace “BS 2014” with “BS 2015” and “BS 2016” in all formulas.
Excel functions used for Balance Sheet
Trade Receivables = INDEX(‘BS 2014’!$1:$1048576,MATCH(BS!$B4,’BS 2014'!$A:$A,0),MATCH(BS!C$3,’BS 2014'!$1:$1,0))
Inventory=INDEX(‘BS2014’!$1:$1048576,MATCH(BS!$B5,’BS2014'!$A:$A,0),MATCH(BS!C$3,’BS 2014'!$1:$1,0))
PP&E=INDEX(‘BS2014’!$1:$1048576,MATCH(BS!$B6,’BS2014'!$A:$A,0),MATCH(BS!C$3,’BS 2014'!$1:$1,0))
Cash=INDEX(‘BS2014’!$1:$1048576,MATCH(BS!$B7,’BS2014'!$A:$A,0),MATCH(BS!C$3,’BS 2014'!$1:$1,0))
Other assets=INDEX(‘BS2014’!$1:$1048576,MATCH(BS!$B8,’BS2014'!$A:$A,0),MATCH(BS!C$3,’BS 2014'!$1:$1,0))
Assets= SUM(C4:C8)
Trade Payables=INDEX(‘BS2014’!$1:$1048576,MATCH(BS!$B11,’BS2014'!$A:$A,0),MATCH(BS!C$3,’BS 2014'!$1:$1,0))
Provisions=INDEX(‘BS2014’!$1:$1048576,MATCH(BS!$B12,’BS2014'!$A:$A,0),MATCH(BS!C$3,’BS 2014'!$1:$1,0))
Financial Liabilities=INDEX(‘BS2014’!$1:$1048576,MATCH(BS!$B13,’BS2014'!$A:$A,0),MATCH(BS!C$3,’BS 2014'!$1:$1,0))
Other liabilities=INDEX(‘BS 2014’!$1:$1048576,MATCH(BS!$B14,’BS 2014'!$A:$A,0),MATCH(BS!C$3,’BS 2014'!$1:$1,0))
Equity=INDEX(‘BS2014’!$1:$1048576,MATCH(BS!$B15,’BS2014'!$A:$A,0),MATCH(BS!C$3,’BS 2014'!$1:$1,0))
Liabilities & Equity= SUM(C11:C15)
What does INDEX and MATCH function do?
If you want to return the element in table or an array selected by the row and column number indexes, then this function. This function can be used two ways,
· Returning a reference to specified cells
· Returning a value of a specified array of cells.
While MATCH function returns the position of a cell in row or column.
Step 7: Adding forecast period
Group columns G and H to hide them. Label columns I-M as “Forecast”. Copy the formatting of the columns to the left and paste it to the right (using Paste Special Formatting). Add a forecasting grey area below the table. Fill-in grey box with Base, Best and Worst case scenarios. Add a few rows above the P&L and create a scenario cell. Use Data Validation to create a drop-down list.
Step 7: OFFSET and MATCH and Calculation of Historical Percentage Ratios
Use OFFSET and MATCH for scenarios such as worst, base and worst case. Then, use percentages that were observed historically as a guidance to forecast the future.
I20 = OFFSET(I20,MATCH($C$3,$B21:$B23,0),0)
I26 = OFFSET(I26,MATCH($C$3,$B27:$B29,0),0)
What does OFFSET function does?
Returns a reference to a range and considered very handy for formulas that require a dynamic range.
Step 8: Forecasting DSO, DPO, DIO, Other Assets and Other liabilities
Forecast Trade Receivables, Inventory, Other assets, Trade Payables, and Other liabilities. Calculate AVERAGE historical days and ratios. Then, apply average figures to the forecast period.
Days Inventory Outstanding (DIO) = average inventory/cost of goods sold x number of days
Days Payable Outstanding (DPO) = accounts payable x number of days/cost of goods sold
Days Sales Outstanding (DSO) = accounts receivable x number of days/total credit sales
DIO = -1*C6/’P&L’!C$7*360
DPO = -1*C12/’P&L’!C$7*360
DSO = C5/’P&L’!C6*360
Other Assets% = C9/’P&L’!C$6
Other liabilities% = C15/’P&L’!C$6
Step 9: Build Fixed Roll Asset Forward
Create a new sheet. Name the new sheet “Fixed Asset Roll Forward”. Format the sheet nicely. Use the usual formatting macro and pre-saved Cell styles. Calculate historical D&A as a % of beginning PP&E, and Capex as a % of beginning PP&E. Apply the average of these numbers for 2017–2021. Apply forecasted D&A % of PP&E, and Capex as % of PP&E to obtain Beginning and Ending PP&E for the forecast period. Link the forecasted PP&E values to the Balance Sheet.
Beginning PP&E = BS!C7
D&A =’P&L’!D11
Capex = D8-D6-D5
Ending PP&E = BS!D7
D&A as a % of beginning PP&E =D6/D5
Capex as a % of beginning PP&E =D7/D5
Step 10: Build Financial Liabilities Schedule
Create a new sheet. Name the new sheet ‘Financial Liabilities’. Format the sheet nicely. Use the usual formatting macro and pre-saved Cell styles. Calculate the amount to be paid to extinguish the firm’s financial liabilities over the next ten years. Build a detailed breakdown showing the portion paid for interest expenses and the portion paid for debt repayment. Link the forecasted values to the P&L and the Balance Sheet.
Beginning Debt = C8
New Debt = 0.0
Principal Repayments = D8-D5
Ending Debt = BS!C14
Debt to be repaid in ten years = 10
Interest rate = 9%
Annual Payment = PMT(C11,C10,E8)
Payment = $C$12
Interest expense = -E8*C11
Debt repayment = C16-C17
Residual debt = E8+C18
Step 11: Build Equity Schedule
Create a new sheet. Name the new sheet “Equity Schedule”. Change the line items in the table accordingly. Format the sheet nicely. Taking into account is the amount of dividends that will be paid to the shareholders. Build an IF function for the payment of Dividends (the company pays dividends only if its Net Income is positive). Assume no increases of capital in the forecast period. Link Net Income results to the “P&L” sheet and calculate Ending equity. Finally, link Ending equity to the “BS” sheet.
Beginning equity = E9
Increase of capital = 0.0
Net income (loss) = ‘P&L’!I16
Dividends = IF(F7>0,-F11*F7,0)
Ending equity = BS!E16
Dividends as a % of Net Income = 40.0%
Step 11: Build and Calculate Cash Flow
Create a new sheet. Name the new sheet “Cash Flow”. Format the sheet in a professional way. Build the structure of a Cash Flow statement.
EBITDA = ‘P&L’!I10
Interest Expenses =’P&L’!I13
Taxes = ‘P&L’!I15
Change in Trade Receivables = -(BS!F5-BS!E5)
Change in Inventory =-(BS!F6-BS!E6)
Change in Trade payables = BS!F12-BS!E12
Change in Other assets = -(BS!F9-BS!E9)
Change in Other liabilities = BS!F15-BS!E15
Capex = -’Fixed Assets Roll Forward’!F7
Operating Cash Flow = SUM(C5:C13)
Dividends = ‘Equity schedule’!F8
Change in Financial liabilities = ‘Financial Liabilities’!F8-’Financial Liabilities’!E8
Change in Provisions = 0.0
Change in Equity = 0.0
Net Cash Flow = SUM(C14:C18)
Add Net Cash Flow to Beginning Cash and this gives Ending Cash. Assets minus Liabilities equals zero.
Conclusion
So, these tools are not only useful for financial analysts but they can also be learning tools for engineering team leaders, operations managers working for corporations or small companies. The financial side of your business can quickly turn into a big mess if you are not creating your P&L statements frequently and don’t know where your business stands financially.