Eliminating Inefficiencies in Excel Spreadsheets

excel_university_logo_100I attended an IMA meeting this evening and the guest speaker was Jeff Lenning, President of Click Consulting and author of “Excel University: Microsoft Excel Training for CPA’s and Accounting Professionals”.

The theme of the presentation was basically “eliminating inefficiencies in recurring use spreadsheets”.  I build a lot of large spreadsheets with vlookups, pivot tables, charts, if statements, conditional formatting and other relatively advanced functions.  To be honest, I did not expect to learn much, but as is often the case, the more you know, the more you realize how much you don’t know.  I quickly learned several relatively simple tricks that I can apply more consistently throughout my recurring use spreadsheets to save a lot of time for myself and my clients.  I’ll summarize some of these below:

  • Stop using SUM where SUBTOTAL is more efficient.  The SUBTOTAL function excludes other subtotals within the range, so it eliminates the need to revise the ‘grand total’ formula when new rows or subtotals are inserted into the range.
  • Add a “skinny row” before formula rows.  A “skinny row” is a blank row with a relatively small row height.  Adding a skinny row eliminates the need to adjust the formula row or the formatting when inserting a new row at the bottom of the range.
  • Hide rows and columns using Data-Outline-Group feature rather than using the hide function.
  • Use the FILTER function in conjunction with the SUBTOTAL function to create methods for other users to sort and subtotal data that are a little simpler to use than pivot tables.  I like pivot tables, but they aren’t for everyone so this is a good alternative.
  • Use a “Start Here” worksheet for input areas and highlight input cells with the “input” cell style.
  • Use an “Error Check” worksheet to ensure integrity throughout multiple worksheets.
  • Use hyperlinks to ease navigation between source data and other worksheets.
  • Use keyboard functions (rather than right click or ribbon icons) for common features such as “F2” to edit a cell, “F4” to toggle through cell references, Arrow+Shift/CTRL to scoot around your worksheet more efficiently than with your mouse, and double-clicking the lower right corner of a formula cell to push the formula down as far as there are adjacent cells.

I have used many of the tips above, but the key is to use them more consistently.  If you have any additional Excel tips or tricks you like, please share them in the comment section below.

Preventing Payment Fraud

fraud-alertMost middle market businesses have either been or will be the target of attempted payment fraud.  The good news is when you are following best practices to prevent payment fraud, it is fairly easy to detect and prevent the attempted fraud from being successful.  Best practices include the following:

1) Implement Positive Pay:  This is an early warning fraud detection system that helps you prevent fraud before it occurs.  It involves sending your bank an electronic file of payments made as you make them, which they can cross reference as checks are cashed.  The service is inexpensive and can be provided by your bank or your ERP system provider.

2) Daily bank reconciliations:  Some people think daily bank rec’s are overkill, but the truth is they prevent fraud, give you a clearer picture of your short term cash flow and they often actually save time.  Minor discrepancies can be difficult to identify when you are working with a whole week or month of data.   Reconciling daily makes it a simple task that can be easily performed by lower level accountants.

3) Use fewer paper checks and keep your check stock secured:  Most of the attempted payment fraud is the result of fraudsters getting their hands on your paper checks and either duplicating them or  using the information to make unauthorized ACH debits.  Converting regular payees to electronic ACH payments reduces the risk of fraud.

Following these 3 simple practices will greatly reduce the risk of fraud and give you some additional peace of mind.

FASB: Gift Card Accounting

No revenue is recorded when Gift Cards are sold.  Rather, a liability is created to reflect the future obligation of the seller to deliver goods or services in exchange for the payment that was made when the gift card was purchased.  When the gift card is redeemed, the sale is recorded and the liability is eliminated.

Sometimes gift cards are used to scrape ice off your windshield or they are cut into slices to keep the collars of your shirts nice and pointy.  Other times they are not used for anything at all, which means the liability on the books of the seller never gets eliminated.  FASB is working on a new revenue recognition standard that will require companies to estimate the amount that will not be redeemed, which is called breakage, and record it into revenue.

To read the Journal of Accountancy article, click here.

The Ins and Outs of Financial Covenants

Loans generally come with covenants that serve as protections for the lender to ensure that the borrower meets certain levels of financial strength and performance.  There are a variety of ratios or calculations that can be used, but in general they are all designed to predict your ability to repay your loan. The most common types are asset-to-liability ratios such as the current ratio, or income-to-financing costs such as the Fixed Charge Coverage Ratio.  The types of ratios used are pretty standard, but the minimum or maximum restrictions vary and may be open to negotiation.

They are generally reported quarterly to the bank. For internal purposes, they should be reported more frequently and should be forecasted at least two quarters in advance. This will allow time to arrange alternatives in the event that your forecast falls out of compliance with the covenant restrictions.

Covenants are generally designed to change over the life of the loan, and can be adjusted later as well if your situation changes. The loan documents generally contain language that specifically spells out events of default as well as how each covenant is to be calculated. Take the time to study the language so you completely understand it.

In summary:

  • Be aware of the covenants before closing the loan.  That’s the best time to negotiate as much flexibility as possible for your company.
  • Create an easy to use template to calculate the covenants and report them to the bank as required.  Be aware the covenants change over the life of the loan, so build those dates and changes into the template.
  • Build covenant calculations into your financial forecasts.  Predict and internally report covenant compliance at least two quarters in advance.  Graphics and conditional formatting help to highlight potential non-compliance issues.
  • If your business is going through significant changes that may affect your ability to stay in compliance with covenants, consider going back to the bank to attempt to have them adjusted.
  • Understand the events of default, including any events that can occur even if you are in compliance with the covenants, and have a plan in place to deal with it long before you need it.

Those are the ins and outs of financial covenants.  If I left anything out, please add your comments below.

 

Hedging with Derivatives; Part 3 of 3: Commodity or Product Input Risk

Companies that depend heavily on raw-material inputs or commodities are sensitive, sometimes significantly, to the price change of the inputs.  For example, energy intensive manufacturers and transportation companies can be sensitive to the price of gas.  Food product companies can be sensitive to fluctuations in the price of certain agricultural commodities.  Futures contracts on commodities can mitigate a portion of this type of risk.

Futures contracts are sometimes confused with forward contracts.  While similar, they are not at all the same.  A forward contract is an agreement between two parties (such as a wheat farmer and a cereal manufacturer) in which the seller (the farmer) agrees to deliver to the buyer (cereal manufacturer) a specified quantity and quality of wheat at a specified future date at an agreed-upon price.  It is a privately negotiated contract that is not conducted in an organized         marketplace or exchange.

Futures contracts, while similar to forward contracts, have certain features that make them more useful for risk management.  Futures contracts have standardized terms established by the exchange. These include the volume of the commodity, delivery months, delivery location and accepted qualities and grades. The contract specifications differ depending on the commodity in question, but this is the general idea:

A flour miller is concerned about the risk of wheat price increases for wheat to be purchased in November. Wheat futures for December delivery are currently trading at $4.20/Bu, and the typical basis at the miller’s location is $0.15 over futures. The miller hedges this risk by taking a long position (buying) the December wheat future at $4.20. In November, the futures price has increased to $4.40, and wheat is selling locally for $4.55. The miller lifts the hedge by selling back the futures position at $4.40, resulting in a profit of $0.20/Bu. This profit is then applied to the cash purchase cost of $4.55/Bu, resulting in a net cost of $4.35, which is the price expected when the hedge was placed.

For companies sensitive to raw material prices, raw material price variances should be isolated and included in your KPI reporting.  Implementing an effective commodity hedge strategy can minimize these price variances, and the effectiveness of your hedge strategy should be measured and included in the reporting

This concludes the 3 part series explaining how middle market companies can hedge their risks with derivatives.  Happy hedging!

.

Hedging with Derivatives; Part 2 of 3: Interest Rate Risk

If variable interest rates leave your company exposed, you should consider an interest rate hedging strategy.  The most common method is a ‘vanilla’ interest rate swap which can be used to effectively convert all or part of a variable (LIBOR + X) rate loan into a fixed rate loan, thereby mitigating the risk of fluctuating exchange rates.  Simple example:  You have a $2 million loan that pays a variable interest rate.  You can enter into a swap agreement whereby you will borrow $1 million at a fixed rate and invest it in a security that earns a variable rate.  This will reduce your exposure to fluctuations in the variable LIBOR rate.

Another situation that may necessitate an interest rate hedging strategy is when you are anticipating a significant inflow or outflow of cash to take place in the future, perhaps one year from now.  The viability of the strategy may depend to some degree on the interest rate at that time, and you do not know what that interest rate will be.  By purchasing a Treasury futures contract, you could effectively ‘lock-in’ the interest rate now.  This will take one important variable out of the equation and make it easier to focus on the other aspects of the deal.

As with other types of hedging, remember the purpose of a hedge program is to mitigate risk.

Next up:  Hedging with Derivatives; Part 3 of 3:  Commodity or Product Input Risk

Hedging with derivatives Part 1 of 3; Foreign Exchange Risk

When a middle market company engages in exporting its products or importing raw materials, it may want to consider hedging the risks related to fluctuations in the currency.  For example, let’s assume your company takes a sales order from a French customer that will be filled in 6 months and you agree on a price in Euro’s.  You know exactly how many Dollars those Euro’s will convert into at today’s exchange rate.  The problem is you don’t know what the exchange rate will be 6 months from now when you get paid.  If the Dollar appreciates versus the Euro, you will end up with fewer dollars than you anticipated when you booked the sale.  To mitigate this risk, you can purchase foreign-exchange futures contracts.  I will not get into the details of how those work, but I will offer these additional tips:

  • If you agree to terms with a customer or vendor involving foreign currency, define the currency and stick with it.  Do not allow the customer/vendor to have their choice between a price in Dollars or the foreign currency.  Doing so would allow them to complete the transaction in whichever currency is favorable to them at the time of payment, which means your company is shouldering all the risk and will likely lose either way.  Make sure your salespeople, AR and AP staff’s understand this and monitor it.
  • As an alternative to hedging, you can structure the sale or purchase contract in a way that shares the risk between the customer and the vendor by locking the current exchange rate on a portion of the total contract.
  • Some companies have a natural hedge, meaning they purchase raw materials in a foreign currency and then sell finished goods in the same currency.  The idea is fluctuations in the currency that drive raw material prices up will also result in higher sales and vice-versa so there is less need for a hedging strategy.  However, due to timing differences, you might think you have a natural hedge but what you really have is twice the exposure and you could actually lose on both ends!
  • Isolate your gains/losses on foreign currency exchange from your other revenue and material costs.  Make it one of the KPI’s you include in your reporting package.
  • Remember, you are not a speculator.  You are a risk mitigator.  If your “hedging” strategy actually makes a lot of money for the company, you are not doing it right.

Next up:  Hedging with Derivatives Part 2 of 3; Interest Rate Risk

Hedging risk with derivatives

Warren Buffet called derivatives “financial weapons of mass destruction”.  Warren is right, as always, but he also understands their purpose.  Derivatives can be extremely risky investments, but when used properly, they are tools to reduce risk.  It is very important when implementing a hedging strategy to build in sufficient oversight & accountability and follow the policy closely.  This not only protects the interests of the company, for the CFO it is about CYA – covering your ass-ets.  When done right, a hedge strategy is like insurance.  It costs the company money and you hope you never need to use it.  It’s about cutting your losses, not making money. The idea is the cost of the hedge is far less than the potential loss had you not hedged.  CEO’s and owners will sometimes forget this point and question why you spent money on derivatives that expired with no value.  If the strategy was clearly presented to them, they participated in the decision and signed off on it, it will help them remember the blurry uncertainty you were hedging against at the time the strategy was implemented, which can be difficult to see when hindsight is 20-20.

To help you evaluate a company’s use of derivatives for hedging risk, in this 3 part series, we’ll look at the three most common ways to use derivatives for hedging.