headermask image

Business Tools Blog

REPT function to create instant bar chart

REPT or repeat can save time when typing a large string of numbers. 

Example:

Rather than typing “—————————-”

use the formula =REPT(”-”,20) (formula displays “-” 20 times)

This YouTube clip shows REPT function being used to create an instant bar chart.  It’s a pretty cool idea. Since the repeat function is limited to 32,767 characters, you may need to divide by 100,000 to compare customer revenue.  To compare % you could multiple by 100.  Watch the 1:33 minute video to see how easy REPT is to use.

 

SBC and Capital Intensity Ratios

Photobucket

A question from Chris Murphy:

Could you please explain the financial formulas used to determine whether a capital project should be approved?

I love this question.  It shows that our sales team understands when spending capital is appropriate.  Cash flow is our most important metric.  Growing revenue is also important, but only in the context of growing cash flow.

Chris is asking about SBC (Success Based Capital) and Zayo is fortunate to be in a position to fund organic network growth.  In other words, we can meet customer’s needs by making investments in our network.  But how do we know which investments are good and which capital projects should fall on the cutting room floor?

The analysis that you typically think about when evaluating projects is NPV (Net Present Value).  If NPV is >0 then a project should be approved.  The problem with NPV is that it assumes unlimited investment capital and doesn’t easily factor risk of payback or opportunity for additional business based on a project.

Definition: NPV is the present value of the future cash flows minus the initial investment.   NPV is a straightforward formula in excel - explained in Microsoft Office.

Another way to look at SBC is “Capital Intensity Ratio”.  In Telecom, Capital Intensity Ratio is calculated as SBC/Gross Installs (For every dollar of Gross Installs, your company expects $X of SBC).  This ratio is also a loose approximation of cash on cash payback.  Example, if you spent $2 for every $1 of Monthly Gross Installs, your Capital Intensity Ration would be 2.0 and your payback would be 2 months.

A company that is growing their network will have a higher capital intensity ratio.  The appropriate Capital Intensity Ratio is determined by the Board of Directors.  The Capital Approval Process should evaluate projects based on maintaining the approved SBC ratio, growing revenue, and strategically expanding the network.  To compare:

  1. Project 1 has a capital intensity ratio of 3.0 to a single tenant building in a single tenant office park.
  2. Project 2 has a capital intensity ratio of 5.0 to a large multi-tenant building along a route with other large multi-tenant buildings.

Project 2 is likely the better project and more likely to be approved because the capital dollars have potential to serve a larger base of customers.

Other factors that are considered:

  • Contract Term
  • Customer’s financial strength and potential growth
  • Competition
  • Size of the project

PRWeb - a new PR Tool

Public Relations used to require a good rolodex of press contacts. You could write a press release in a word document and email your contacts hoping they would write a story.  I bought PR for dummies years ago … and that was the only discernable advice I gleaned from the book.

Today, reaching the Press is only the beginning.

PRWeb has been around since 1997.  They offer the following service:

  • Boost your Web site traffic and SEO
  • Get your news on top sites like Google News & Yahoo! News
  • Add multimedia to your release
  • Send your news to top journalists
  • Track your results

Commoncraft does a great job explaining why PRWeb is a better tool than traditional press releases in the following clip:

Note - PRWeb is not free.  A release can cost between $80 and $360 depending on the features you want to use.

MEGO

I would like to introduce a new telecom acronym to turnkey.  I’m not sure how to do this … so I will introduce it here and see if it catches on.

MEGO = My Eyes Glaze Over

This can be used in any telecom meeting where you are drawing on a white board while talking to me … usually the white board looks something like this:

 

Photobucket

 

You know who you are  …  

Novation - I learned a new word

I will caveat this blog with …. I went to public school in Hammond, Indiana

When I hear a new word, I am (1) not surprised that I don’t know the meaning, and (2) eager to add the new word to my Lexicon.  So over the past few days John Scarano has been using the word Novation.  (I’ve heard it at least 3 times)

So I looked up Novation on InvestorWords.com

The substitution of a new contract for an old one; or the substitution of one party in a contract with another party.

Thanks John, for adding a new word to my personal dictionary. 

Calculate the Quarter for any given Date

For financial and operational reporting, we sometimes show quarters.  To convert a date to a quarter, use the following 3 steps:

Step 1 - Find the quarter

  • The dates are shown in row 1
  • The formula is in row 4  =ROUNDUP(MONTH(C1)/3,0) 
  • This formula takes the month in C1, divides it by 3, then rounds it up to the nearest number, which equals the Quarter

Photobucket 

Step 2 - Find the year

  • The formula is in row 5  =YEAR(C1)
  • This formula converts a date to a year

Photobucket

Step 3 - Concatonate a formula for Quarter and Year

  • The formula in row 6 =C4&”Q”&C5
  • This formula puts the letter “Q” between the quarter and the year

Photobucket 

That’s all there is to it.

Valuable Business Lesson from Ron Beaumont

A few days ago I reminisced about my first Telecom job.

What I didn’t mention is that I worked in the Business Unit MFS Telecom, who’s President at that time was Ron Beaumont.  Ron taught me one of the most valuable lessons I’ve learned in business. It applies to all businesses … and it really applies when the finance organization is centralized and doesn’t have a good handle on operations.

For those of you who don’t know Mr. Beaumont, he used to be a little bit intimidating. When perplexed by a problem, he would look at you over the brim of his glasses. He probably didn’t know it, but the result was that we would quake in our boots and wait for the question …

 

Photobucket

One day, I sat in a meeting with some of the more senior finance folks and operations leads … I think I snuck into the meeting, or turned left when I should have turned right trying to find the break room. Anyway, the EBITDA forecast had changed drastically from the previous month. Everyone was fidgeting as the numbers were presented.  Ron sat calmly, then looked over the brim of his glasses and said,

I only have one question. What makes you smarter today than you were 30 days ago?

I can’t remember the answer, other than I am sure that like Chinese food, it was strangely unsatisfying.

But that short, simple question changed the way I approach forecasting. With every assumption, I think … in 30 days will I wish that I had been smarter about this?

In Telecom, our business is largely recurring revenue and expense. Even usage should be predictable. If there is a fluctuation, it’s either because we were surprised by an unpredictable event or because we didn’t fully understand or sales, installs, network expense or SG&A. 30-90 days out, a forecast should be +/- 1%.

The financial close usually happens mid-month. For example, the June financials will be final sometime around July 15th.  By July 15th, we should have a pretty good idea of where July sales, installs and even revenue, NetEx and SG&A will come out. Finding these prelim numbers is a lot of work. It’s much easier just to make an assumption, than it is to research and use prelim numbers as your basis.

However easier today comes with consequences tomorrow. Think of that sick feeling you will experience deep in your gut when you get asked, “What makes you smarter today than you were 30 days ago?” If the answer is that information was available and you chose not to use it, you will have a hard time coming up with a satisfactory answer.

So my advice to newbies and anyone in Zayo’s finance group is … when you work on a project, ask yourself, can I answer Ron Beaumont’s question? In 30 days, will my forecast be spot on? And if it’s not, will I be able to point to an event that I couldn’t have reasonably predicted? 

Hide the field buttons in a Pivot Chart

Once you’ve created a pivot table, you can right click on the pivot table to quickly create a pivot chart.

Photobucket

The problem is that a pivot chart has those ugly field buttons.  Microsoft put them in the charts, so that the user can easily re-arrange data, but when the chart is finished, the field buttons make your chart look sloppy. 

Photobucket

I  only recently and quite by accident discovered that you can hide these buttons.  Right click on any filed button and then select Hide Pivot Chart Field Buttons.

Photobucket 

Your chart now looks like this:

Photobucket

 

 

Undo, the unsung hero!

I was working on a pretty large spreadsheet and made a huge copy paste mistake, virtually  wiping out 4 hours of work.  Then I clicked UNDO and then everything was OK again.

I use that undo button a dozen or more times a day.  This may be the biggest productivity saver of all time.  So I was going to blog my thanks to the inventor of UNDO.  This seemed like a good idea at the time, now 20 minutes later, I still don’t know who that unsung hero might be.

If you are out there, let me know and I will send a tasty treat!  After all, you’ve earned it.

Here’s what I found … The inventor was probably working with Steve Jobs on the Apple Lisa project between 1978 and 1982.

The Edit menu was laid out in a standardized fashion with Undo, Cut, Copy, Paste and Clear (Delete) the command key equivalents were assigned to be the bottom left four keys on the keyboard (Z, X, C, V) and the backspace for delete.

Microsoft’s Ballmer on the future of media

Microsoft’s Steve Ballmer was quoted in the Washington Post,

What is your outlook for the future of media?

In the next 10 years, the whole world of media, communications and advertising are going to be turned upside down — my opinion.

Here are the premises I have. Number one, there will be no media consumption left in 10 years that is not delivered over an IP network. There will be no newspapers, no magazines that are delivered in paper form. Everything gets delivered in an electronic form.

10 years?

Yeah. If it’s 14 or if it’s 8, it’s immaterial to my fundamental point. . . . If we want TV to be more interactive, you’ll deliver it over an IP network. I mean, it’s sort of funny today. My son will stay up all night basically playing Xbox Live with friends that are in various parts of the world, and yet I can’t sit there in front of the TV and have the same kind of a social interaction around my favorite basketball game or golf match. It’s just because one of these things is delivered over an IP network and the other is not. . . .
 

In late March, BearonBusiness wrote that bandwidth is growing and that’s an understatement.  I think we picked the right industry at the right time.  Content might be king … but if you read Ballmer’s comments closely, the content isn’t what’s changing, it’s our ability to interact with the content that will change.   The interaction described above requires lots of bandwidth. 

Does the Bear have a crystal ball? Read his The Supply Side of the Equation post:

Zayo’s Investment Thesis:

  • Bandwidth is rapidly growing and this will persist
  • Fiber will be the workhorse for bandwidth transport for as far as the eye can see
  • New generations of fiber are unlikely to make current generations of fiber obsolete

A fair response at this point is a heavily sarcastic ”haven’t we heard this story before?” That is, the three points above led to lots of dollars being spent on the construction of fiber networks. My time horizon begins in the late 1980s, when competition with the baby Bells began. (MCI, Sprint and the break-up of AT&T had already taken place—I’ll leave it to another blogger to cover the roots of LD competition.) Between the late 1980s and early 2000s, many billions of dollars were spent by competitors to the former AT&T monopoly. By my estimate, these companies invested $85B. That is a big number. (Later, I will show my derivation.) $85 bill is a lot of dough.

Granted this money was spent on more than just fiber construction and associated electronics. Switches, colo facilities and opex burn chewed up a big portion. Regardless, $85B or thereabouts was invested with lots of it pumped into ground. In the meltdown, it was not demand that was the problem—it was supply. There were simply too many companies with too much bandwidth (and other stuff) to sell. It didn’t matter that demand was growing—it was not growing fast enough to justify the ample supply.

So why does Zayo believe 2008 is any different than 2002? After all, the fiber is still in the ground. This is a fair question—to address it properly requires a bit of a history of competitive telecom. I will skip past the MCI and Sprint era. My story will begin in the late 1980’s, when competition with the baby bells began. It will take many posts to tell this story. I will begin this in about 2 or 3 weeks. In the meantime, I will cover some other topics. Stay tuned.

If you haven’t tuned in to Bearonbusiness and Dan’s Telecom Texas Holdem Tournament, start at the beginning and read through.