Keep a list of results regularly updated in Excel

It can be very encouraging to see a continuously updated display of latest results.

It might be reports of races won, properties sold, lives saved or pets rescued. It might be shown on intranet monitors or projected on walls in clubs, shops, offices or staff rooms.

The one common denominator is that the best software to use is a spreadsheet application. In this example, it’s Excel.

Charitable contributions are being collected by Red, Yellow, Blue, Green and Purple teams.

The graphics are provided by Smartart, which doesn’t normally allow for collection of data from cells, but this column shows how to get around that.

The automatic updating can be daily or minute by minute, activated by some short macros.

Start with a clean worksheet and, if you are using Excel 2007, choose Smartart on the Insert ribbon.

In the left-hand panel click Hierarchy and, using the hover tips, find and choose, Table Hierarchy.

We need the top row for a title, not the second, and the third level should have five boxes instead of three.

Smartart has a language of its own that is not intuitive, but follow these instructions and you will be all right.

Click on the left-hand box in the lowest tier and click Promote in the Create Graphic section of the Smartart Tools ribbon.

Repeat that for the second box on the lowest level, and then the third. You’ll now have one long box for a title with five small boxes underneath.

Where it says ‘[Text]’ in each of the boxes, type in text of an equivalent length to the final result. You could also click the tiny arrows to the left of the Smartart diagram to display a text box to use.

If you do that don’t press Enter or it will change the diagram.

It’s usually easier to type the text in the boxes. Any time the Smartart diagram is active, a Smartart Tools tab will appear above the other ribbons.

Click on the Page Layout tab, then click the Themes icon on the far left of the ribbon and pick a theme. This will change the font, the point size, and the background colour.

The theme used in the illustration here is Urban.

Select a style
Click Smarttools, Design and select a Smartart Style with a curved shadow border.

Click on the last box in the bottom row and press Ctrl & A to select all the boxes.

You could also press Ctrl and click on all the boxes individually to select them all.

Right-click and choose Copy.

Below the Smartart diagram, right-click and choose Paste.

This converts the boxes in the Smartart diagram to individual Shapes.

The advantage of a Shape is that text, or a formula, from another source can be displayed in it. And pre-Excel 2007 users can use Shapes and the updating macros.

You can still continue changing the formatting if you wish.

Right-click on the top box and choose Format Shape, Gradient Fill, and the first of the pre-set colours, Early Sunset.

The lower boxes will give the results of the different colour teams.

There is no need to label them Red team, Yellow team and so on. Click on the first box.

Under the Home tab use the regular Fill and Font Colour tools to change the box’s colour to red and the font colour to black.

Repeat this action to appropriately colour the other boxes.

If you wish to follow the illustration, you can also click on the first box in the lower row and choose Format Shape, 3-D Rotation, and change the Z: axis to 5° to tilt it to the left.

Repeat this with the other small boxes to tilt them to the left or right.

There is no linkage between the initial Smartart diagram and the copy of it in Shapes, so you can remove the original from the worksheet if you wish.

Click within the diagram between the boxes and press Delete.

Pick a range of six cells that will supply the up-to-date data for the boxes. In the first cell enter the formula for the top box.

If you want to update the results daily enter:

=”Team results at “&TEXT(TODAY(), “mmm d yy”)

If you want the results to be updated every minute enter:

=”Team results at “&TEXT(NOW(), “h:mm am/pm”)

Click within the text of the top box, but don’t enter the link there. Instead, if the formula is in cell A45, in the Formula Bar enter:

=A45

The contents of cell A45 will now appear in the top box.

If you need to adjust the point size, just click on the top box and change the point size tool on the Home ribbon.

In cells A46:A50 enter the initial results to go in the red through purple boxes.

Later, if you wish, these cells can contain links to a changing database. Click on the red box and enter:

=A46

in the Formula Bar. Click on the yellow box and enter:

=A47

in the Formula Bar and so on for the rest of the coloured boxes.

If you want to update the time or date manually, just press the F9 function key.

Press Alt & F11, then double-click on ThisWorkbook in the left-hand panel and in the right-hand panel enter:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime mTime, “Refresh”, , False
End Sub
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue(“00:01.00”), “Refresh”
End Sub

Click on the Insert menu and chose, Module. In the right-hand panel enter:

Public mTime As Date
Sub Refresh()
mTime = Now + TimeValue(“00:01:00”)
Application.OnTime mTime, “Refresh”
Calculate
End Sub

Press Alt & F11 to close the VBA editor and from then on the display will update by itself.

Save the file. If using Excel 2007, use an.xlsm extension.

The default Excel 2007 format (.xlsx) does not allow macros.

Taking stock
A major influence on the price of a stock is often the state of the market, then the sector it is in (such as retail, media or banking), and finally the performance of the company.

The FTSE 100 (Financial Times stock exchange index of 100 top stocks) has declined 28.4 per cent in the past three years, 9.2 per cent over the past five years, and 30.9 per cent over the past 10 years.

The sectors mentioned have all been hit badly in recent times.

These historical results can be plotted on a spreadsheet.

More optimistically, a spreadsheet can help you examine individual companies carefully.

Recently we looked at interest cover and the debt to equity ratio.

That’s because many of the companies that have failed of late suffered from a leveraged buyout (LBO). This is where a company is shackled with the debt undertaken to buy it out.

This month we’ll look at asset Turnover. From its Balance Sheets, take the company’s five year reports of Total Assets. Multiply each by 100, and divide by Net Sales.

This ratio should remain fairly constant over the years, trending neither up nor down and staying in line with the industry.

Net Sales means Turnover or Gross Revenue (depending on the industry) less the value of returned merchandise, allowances to customers for damaged goods or poor services, and discounts for prompt payment.

Discounts for quantity purchases are usually subtracted from revenues before being recorded under Turnover in most industries.

The definition of Assets can be a little more tricky as they include depreciation.

This is a measure of consumption, not a measure of change of value.

The useful life of a car might be four to eight years, but the problem is when a company changes its view of the life of an asset.

In its annual report for the year to 31 March 1988, BAA said its runways have lives of 23 and a half years. In 1989 it said runways lasted for 40 years.

Then, in 1990 it reckoned runways should last 100 years.

As BAA owned a lot of expensive runways at the time, this makes a big difference.

It’s simpler in the US where things last as long as the Internal Revenue Service says they do.

You can also compare Fixed Assets to Net Worth.

This is Net Plant and Equipment times 100, divided by Net Worth. Compared with its industry, a lower ratio is desirable as it frees funds for working capital.

This is why many companies prefer to rent properties rather than buy them and lease machinery or equipment.

When you invest in the stock of a company you become a part-owner.

So it makes sense to keep track of these things on a spreadsheet.