Wanted: How YOU use Excel!

[Domski]

90% of the time I work in local government as Principal MI analyst for the shared services at Leeds City Council. This mainly involves dragging data extracts from the SAP HR system and using the wonders of Excel to crunch the data. For one off requests this will mainly be done manually, if it's going to be a regular information requirement I will automate the process using VBA.

The other 10% of the time I seem to spend helping other folk to do their jobs better (hopefully) either by teaching them how to do things with functions or putting together little Excel apps where they can just click the buttons and it does it all for them.

Dom

 

[gardnertoo]

I'm a technician at a power generation facility owned by a utility. The control system used to run the facility has a data historian to which I have access using Excel. I can pull the stored values of some 3000 instruments around the plant, at intervals as fast as 1/10 of a second for some instruments, covering any time period back to early 2004 when the plant first started. I typically use this to look at only the most recent data, say a month or less. Sometimes I am trying to compare how a given parameter acted last year at this time, or comparing it's behavior before/after a repair, or how parameter "X" acted when parameter "Y" was within a specified range, etc. It's a great troubleshooting tool. This is usually done with fairly simple workbooks, such as a straightforward graph of pH vs time. I even have that rare beast, an appropriate use of the radar plot:

You're looking at the calculated temperatures of 14 combustors arrayed in a circular pattern, at 10 second intervals. You can instantly see that combustor #4 is the one which had the problem that shut the plant down, which in turn directs our troubleshooting efforts.
A somewhat more complex example that I consult once a month or so, and tweak a little each time, is a predictor sheet which estimates the date when our turbine could be expected to reach one of two possible maintenance milestones, based on historic operating patterns. The two milestones are number of startup/heatup events, and number of running hours. The data gathered allows for other comparisons, such accumulated values of hours, watts, starts, etc over a year, which I chart for year-to-year comparisons.
The most complex one I've done, which won me an award within the company, was a homemade Maintenance Management System. This project would probably have been better suited to Access, but I know Excel. So I forced Excel to do what I needed. LOTS of macros and userforms. It had a front-end for the users to enter requests for maintenance, another for the maintenance staff to choose jobs and enter manhours, status, comments, etc, and a back-end that did the scheduling of the routine preventive maintenance. We ran with that spreadsheet for almost a year while we got the SAP Plant Maintenance Module ready to roll-out.

 

[Norie]

gardnertoo

Are you sure you aren't a game developer?

That image looks pretty much like an arcade game I used to play, or well try to play, back in the eighties.

 

[Jon von der Heyden]

Folks, yours truly is gonna start giving Excel seminars around the country. In order to get folks to perk up in their seats, I would like to create examples that strike close to home with as many people as possible. I would be much obliged for anyone that could help me generate these. If you could give information along these lines - that'd be just swell:

• Your industry - for example
  • manufacturing
  • service
  • education
  • government
  • non-profit
  • tourism
  • so on and so forth
• Just a general description of where within the industry
  • Plastics Manufacturing, or
  • Electronics Manufacturing
  • Service: Restaurants
  • Service: Health Care
  • Govt: Dept. of Motor Vehicles
  • Govt: Ministry of Silly Walks
  • Education: Elementary School
  • Education: University
  • you get the picture
• How you use Excel
  • Creating pro forma invoices for export bids
  • Reconciling Customer Returns
  • Tracking Sales of Various Mixed Drinks
  • Scheduling for Nurse's Station
  • Minimum Wait Times for License Renewals
  • Applications for Grants to Create New Silly Walks
  • Template for Science Fair Projects
  • Room Assignments in Dormitories

Again, much obliged fer any help y'all kin lend in this direction.

Industry:

Conglomerate

• Industrial (traffic, transport, buildings etc)
• Energy (fossil, wind etc...)
• Healthcare (diagnostic equipment)
• Other (IT solutions)

Business:
Division: Global Shared Services
Department: Finance & Business Administration
Customers: Operating Companies
Shared Services are EBIT neutral, focus on savings for SOCS, not profit oriented...


Typical Models:

• Costing and product pricing (costs broken down to product level, price incl cost + small margin to support investment activity)
• Revenue and cost budgets and forecasts. Revenue forecasts at customer detail.
• Monthly reporting - budget / forecast vs actuals (P&L, Balance Sheet and Cash Flow), and variance analysis.
• Debtor and Creditor Reporting (aged analysis, DSO, DPO etc).
• Customer reporting (open book policy so full detail of supporting volumes and charges, for each customer for each product). Budget / forecast vs actuals and volume and price variance analysis.
• Provisions models - e.g. employee holiday + bonus provisions.
• Compliance Models (SOX compliance) - e.g. payroll monitoring.
• Savings models, calculating and tracking savings on corporate initiatives.

Data is sourced from SAP and BI (Business Warehouse) but often stored in tables (Access) along with other data that is not collected in the ERP.

There are a variety of other activities. This is the typical management accounting stuff. But if there is anything here that you are interested in understanding in more detail let me know.

 

[DonkeyOte]

Interesting thread - and some interesting uses !

I've often used Excel as the basis for ETL solutions - ie taking data from one supposedly "better" system, analysing/translating said data prior to pushing the data into another supposedly "better" system... and normally on an automated basis (ie firing overnight)

The above probably sounds completely mental but in a classic case a no. of "feeder" systems actually stored less data than the output system required (think OLAP).

Now as I'm sure is the case in most firms - the executive board (being the board) decided that rather than spending money investing in input systems that actually captured the info they needed it would in fact be far better spent on a nice new and expensive boardroom fit-out ...
(presumably so they could be comfortable whilst discussing what to tell the auditors when they asked why all their proprietary systems were from the dark ages and no longer really "fit for purpose").

End result: "someone" (read: muggins aka "System Red Adair" to my colleagues - hey, they weren't technical!) had to somehow intervene within the existing workflow and add an intermediate step wherein a whole host of as yet "unseen" data could be created from scratch ... the rules behind the creation of this data was I would say relatively complex (code permutations across variety of fields etc).

To me XL was the most obvious application of choice given the data storage element of the process was minimal (& more importantly temporary) and it was in essence just calculation intensive. Furthermore the use of XL brought with it other benefits, namely:

a) the system was easily adjusted
(via a few straightforward (well designed?) reference tables)

b) audit-able
(well reasonably so if you know my code / formula sledge hammer approach!)

c) familiar tool to colleagues (& lest we forget - the auditors!)
meaning should I ever have had the audacity to say go on holiday others could in theory have implemented the process on an ad hoc basis without having to phone me at unmentionable o'clock or whilst I was holidaying say on the other side of the planet! (it didn't work as you can tell)

I have to say that as time wore on I used XL less and less as a primary tool and more and more along the lines of the above - web apps and OLAP systems became the main point of reference for the majority of end-users and XL for me became something of a tool in between but no less valuable given XL was far better at performing ad hoc calculations than either of the above whose strengths lay elsewhere (security & storage respectively - IMO).

 

[Cbrine]

Let's see....
Industry: Health Care/Service

Business
Private Retirement and Long Term Care of the Elderly
Corporate Human Resources

Usage
Annual Merit, Bonus, LTIP, Vacation Analysis,Service Awards,etc... I use excel to do a lot of process automation, from programatically producing distribution workbooks, to consolidating the completed workbook together agian, intelligent mail merges with word, Parseing data from the crappy Crystal Reprots Server that this company seems to enjoy so much(They make them pretty with breakpoints and summaries, which makes them pretty usless for data analysis until I parse them, and it's easier to build a VBA program than to get the frigging report writer guy to understand what I want ). I also do a large amount of salary analysis using excel as well. I've integrated excel with outlook using redemption to bypass security restrictions, to make it a mass mailer. I've also done this with STMP bypass outlook all together. I'm sure I've done some other weird stuff as well

 

[arkusM]

Your industry - for example

• Energy

Just a general description of where within the industry

• Natural Gas producer/marketer

How you use Excel

• We use excel to forecast the companies Netback bi-weekly
• I use Excel to model our hedging program via Bloomberg real time data feeds
• I calculate and verify sales revenue monthly.
• Various reports, tracking sheets
• We use it to store data (oh the horrors LOL) Excel is simple for my co-workers as most are familiar with it.
• Track and calculate pricing spreads for different delivery points
• I have used/abused excel to save attachments from our email system (groupwise) and even verify and rename files. Thanks to the help of people online.
• Automating web queries where I can.
• Automating website logins

On the side I automated a spreadsheet for the lunch time poker point tracker. The guy that was running the sheet did all the calculations by hand which took an hour. Now he enters the chip count for each person and the sheet shows the rankings of the players and the deltas for the day ie total points and net points. They have developed a scoring system because we don't play for money at the office, don't want to push our luck.

 

[Annalyzer]

I work for a private non-profit community action agency. We use Excel for many things. Of course, we use it for budgeting which involves a lot of "what-if" type scenarios.

Another interesting way we use Excel is to transfer information between databases. We operate on many different funding sources, many of whom require use of their proprietary database. Then, when we need to compile information for reporting, we are usually able to export data from one database into Excel, save it as type .csv and import it into another. With macros, we are often able to do this with little or no user intervention.

We also use Excel to allocate expenses across a multitude of funding sources. We can set the spreadsheet up at the beginning of the fiscal year according to the budget and then use it all year long to allocate various expenses.

 

[Gerald Higgins]

That image looks pretty much like an arcade game I used to play, or well try to play, back in the eighties.

Tempest ?
http://en.wikipedia.org/wiki/Tempest_(arcade_game)

Man, that was an awesome game. Sadly I wasn't awesome at playing it !

 

[Colin Legg]

Your industry

• Finance

Just a general description of where within the industry

• Investment Banking

How you use Excel

• Primary: playing battleships
• Secondary: answering questions on MrExcel
• Tertiary: financial modelling