Author: Admin

Using Excel to Control Project Cost of Delay

Evaluating the Cost of Delay for a project is an important management tool, particularly when there are multiple projects which all use the same finite resources. The projects cannot all start at the same time if they all need the same resources, so which one is chosen to go first?

Evaluating Cost of Delay will show the impact on the profit of the organisation if a project is delayed, and this helps managers to evaluate distribution of resources in order to mitigate the financial impact.

Also, most projects within organisations have an element of slippage.  Often, due to outside circumstances, or something unforeseen, milestones and completion dates often have to be re-set, which can have a big impact on when the project is completed.

In terms of the profit of the organisation, this will have a negative impact, which could have serious repercussions elsewhere e.g. a profits warning if the organisation is a public limited company which could lead to a drop in the share price.

This could apply to the development of a new product or service within the organisation, or a rationalisation of departments within the organisation that would lead to cost savings.

When the decision is taken to undertake a project, a budget is calculated over a period of time to monitor the costs and to ensure that a profit is being made.  After all there is no point in a project which does not have a positive effect on the profit of the organisation.

Excel is an ideal tool to keep track of the profit being made over the timescale.  The timescale could be in weeks, months, or years.

The initial budget that is calculated is effectively the baseline for the project. It is usually the best calculation of the numbers, based on all known factors and constraints at the time. 

Cost of Delay Example in Excel

As an example, an organisation is developing a new product. It is expected that it will take 3 months to develop, and a further 9 months of the financial year to sell.

Calculations can be made to set percentages for cost types, which can easily be adjusted. The projection could also include Discounted Cash Flow calculations for the net return (this is a standard Excel formula).

Excel allows managers to juggle cost and revenue factors, and to do ‘What If’ scenarios (which is a built in Excel function) in order to get the best scenario and profit for the project, taking into account various assumptions and knowledge.

In the example, development would take place in the first 3 months, with some marketing taking place in months 2 and 3 in order to increase public awareness.

In month 4, the product is ready for the market, and there is now growing sales revenue and costs budgeted for subsequent months.

Everything looks good with a large cumulative net profit at the end of month 12.  The project gets approval and off it all goes.

However, the best laid plans do not always work out as expected.  Suppose that in month 3, a component supplier goes into administration and causes a supply chain problem, or the government bans the import of a vital component from abroad. Also, an important resource on this project could be required on another urgent project that is of greater importance. Solutions need to be found to these problems, and this takes time.

These are events that are totally unforeseen and out of control of the project manager.  The effect is to push the product launch back by a month, and to have a further month of development costs and some further marketing costs at month 4.

Sales revenue is likely to be lower in the first month because of the delay (public perception of the product may have been damaged), and the revenue will always be playing ‘catch up’ against the original budget over the subsequent months.

Also, a competitor may have been working on a similar product, and they may have managed to launch their product in month 3 which will damage the sales.

The cost of this one-month delay is actually very serious, and has a negative effect on the overall profit for the 12-month period.

On month 4 there is a huge change in the projected profit from 61K to a loss of 11K.  Working along to month 12, the amended cumulative profit is running well below the budgeted profit which is the cost of the delay that happened back in month 4.

The overall result at month 12 is that profit for the new product is 116K below what was expected.

Management on seeing the effects of the one-month delay in month 4 will want to get things back on track, so that the original profit figure is achieved in month 12.

They could do this by increasing sales volume, but this will need increased marketing costs. Because of the flexibility of Excel, it is very easy to play around with the numbers for subsequent months in order to mitigate the adverse effects of the month 4 delay.

Managers can produce different scenarios in Excel, save each scenario off as a different file so that it can be easily referred to again, and use ‘What If’ functions to ensure that the goal is achieved at month 12.

Excel has very good chart facilities, and data such as this is easier to view as a chart, than just as a string of numbers:

Conclusion

Excel is an extremely good tool for evaluating Cost of Delay on a project.  This can be used to decide between two or even multiple projects, and the order in which they are executed.

If there is an unexpected delay on a project, it can also allow mangers to generate different scenarios to mitigate the financial impact of the delay.

Excel has enormous flexibility in terms of displaying and calculating data, and presenting it in chart form, and it is an easy task for anyone to create a spreadsheet to calculate the Cost of Delay for projects.

#Excelgate for dummies

Last week we learnt that the Government’s Track & Trace System failed because the data exceeded maximum number of rows in an Excel file. Then #excelgate kicked off with a lot of knowledgeable people (and not so knowledgeable) expressing their opinions. For the most of it these opinions expressed their pre-existing stance over the virtues or failings of Excel. if you were pro Excel you highlighted the mistakes in the use of Excel, if you were anti Excel you highlighted the limitations of Excel.

What we know is that the government was using and outdated version of the Excel Workbook file format. The xls file format with a limit of 65,536 rows whereas the current format introduced back in 2007 has a limit of 1,048,576 rows. When the row limit was exceeded the file missed some 16,000 tests and the rest as they say is history. As of writing this there have been over 24,000,000 million tests this year, with over 600,000 people testing positive. Daily this has now reached over 16,000 positive tests.

What we can conclude from this is that the user was not an Excel Expert, and that the government might still be using seriously outdated software. What we can also probably conclude is that they were adding the positive test results to an Excel sheet working on a weekly basis, so when the results went over 10,000 cases a day the end was nigh.

While there is little more to be said other than these facts and conclusions, the opinions expressed #excelgate has raised some issues that need to be addressed.

First, let me outline what Excel is and is not. Excel is part of MS Office and therefore is designed for office and home use. We refer to Excel as ‘Lego for Data’, meaning that it can get a working software model up and running very quickly. In doing so it offers unrivaled value.

At The Excel Experts, we know that Excel is not a database but it can connect to most databases. In fact, with the ‘penny dropping’ about Excel’s ability to deliver better value, building front and back ends to connect to these databases has become a growth area for our services.

Sometimes our clients do not realise is that they have an existing database platform that they are already paying, for that comes with their Web Hosting.

With Excel as with any other software, it is horses for courses. If you are in a tearing hurry to get a solution or require the best possible value, as with most SMEs, then Excel is very much part of it. Unfortunately, of Excels’ many functions, none of them handle incompetence. The incompetence that was behind this story.

#ExcelGate – Is Excel up to the job?

Chaos has been caused by the Government using Excel as a repository for Covid-19 test results in England.

Records have been lost and not reported, and there has been huge reputational damage to the Government and a loss of confidence in the official figures.  The methodology used has become the butt of many jokes on social media.

The BBC (https://www.bbc.co.uk/news/technology-54423988) puts the blame on Public Health England for using an old file format (.XLS) which only allows 65,536 rows as opposed to the new post 2007 file format (.XLSX) which allows 1,048,576 rows, which is a huge difference.

The source data was provided by a number of commercial firms and the transfer medium was CSV (comma separated value) files. Bearing in mind the purpose of the data, these files could be very large, sometimes larger than 65,536 rows.

Excel is very useful for consolidating data and presenting it in any easy to read dashboard format, using charts and summaries, but this is only as good as the integrity of the imported data.

One of the problems with using the old XLS format is that even if you load the file into a post 2007 version of Excel, it still remains at 65,536 rows unless you do ‘Save As’ and save it in the new XLSX format. This looks like it was not done.

I suspect that the developers ‘cannibalised’ an old XLS dashboard without considering the lack of row capacity. This probably had VBA code to automate loading the CSV files into Excel.

If VBA is used to load in a CSV file into an XLS file, there is no warning that the number of rows has been exceeded and the data truncated. I have tested this in Excel 2003 using a very large CSV file (1m records) and using VBA code to open the file, no warning message is shown that the data is truncated.

It is easy to see why the error has occurred. It is basically down to users / developers not being aware of the row constraint on XLS files.

The whole import procedure was probably working quite well until one day a CSV file came along that was over the limit of 65,536 rows, and data was lost.

How this could have been prevented

In the import code within VBA, once the CSV file was imported, a copy should have been saved off as a CSV file under a different name.  VBA could then be used to check the copy file size against the original.  If the sizes did not agree, then this would be flagged up as a danger.

Using a reference in VBA to Microsoft Active Data Objects (ADO), VBA could link directly to each of the CSV files and use each file as a database table.  An ADO recordset could be created using SQL (Structured Query Language) for each CSV file, number of rows would be checked against the recordset ‘RecordCount’ property and the worksheet ‘Rows.Count’ property, and then using the ‘CopyFromRecordset’ method the data would be copied into the worksheet.

Using ADO, it is also possible  to connect back to the current workbook and use each worksheet as a separate table, provide that the information is tabular with column headings at row 1. This enables full SQL commands to be used to manipulate the data with enormous flexibility, and effectively turns Excel into a full database application.

Working on the assumption that the Excel workbook was ‘cannibalised’ from an earlier application, it would have been better if the Excel application had been developed from scratch using XLSX files.  However, I suspect that there was a time constraint here and the whole thing was rushed through without time to create an original application.

Conclusion

Excel can be used as a database, having the advantage of being able to service a dashboard within the same application.

The problem here is poor design and implementation which did not cover every possible import file size. Testing was also lacking – large CSV files should have been used for testing and checks made to see that all data was appearing.

It is possible that the developers of the application did not understand the full capabilities of Excel, particularly using ADO, and sadly, this has reflected badly on the use of Excel workbooks for solving problems like this.

This article was commissioned from one of our senior consultants at The Excel Experts. Further inquiries please email ben@theexcelexperts.co.uk with the subject line #CallTheExperts .

Apprentices, Why we are looking for one.

Like all parents at the moment, we are looking ahead with growing concern about just what kind of world and society our children are going to inherit from us. My wife works with young people, advising them on widening participation in education; the subject of how to help the youth in our society is never far from our collective mindset.

The decision to take on an apprentice should not be taken lightly and certainly for us, the idea resurfaced several times until COVID struck, and it became clear that the need for an apprentice became center stage again.

A young apprentice can I hope, inject some new thinking into our business, and over time become a central figure in how we, as an organization, move forward and adapt to the new, challenging environment. As far as working from home and remote teamwork is concerned, we may have an advantage over lots of businesses, but we can only keep that advantage by exploring new ideas. An apprentice can be the perfect vessel for this change.

As a business we like to think of ourselves as a high value/low overhead organization; so we run our business from the living room and all our experts work remotely from home, in their own time, and only taking on the projects that they really feel match their expertise and interests. The question has always been “How do we fit an apprentice into this environment? “

Things have changed, and the prospects of a remote apprenticeship are now very real. It may not have been the perfect setup in the past, but arguably, with ‘work from home’ becoming a more accepted way to work now, a work-from-home apprenticeship is now a viable proposition. It’s going to be a challenge, but one that we are willing to take. If we as businesses don’t make this work, who will?

Many years ago my professor handed me a book called “Blueprint for a green economy. It was about sustainable growth and development, and it had a lasting effect on my thinking. Suggesting simply that future generations should inherit, at least as much ‘wealth’ as we inherited from our parents.

That ‘wealth’ can be a mixture of technological, societal, and environmental assets, and that it was possible to trade one type of asset off against another. Arguably, financial ‘wealth’ is no longer the most important for young people.

So what ‘wealth’ are we going to leave for the next generation? Alongside our commitment as a business to adhere to our environmental values, we want to support young people with future-proof employability and digital skills. We want to help our youth start their transition into the working life of the future and we feel the urgency of doing that now, more than ever.

We have started down the route of taking on an apprentice. We’ve contacted a couple of local colleges and universities, filled out some forms, and now keenly waiting for the next steps to evolve. I will keep you up to date on the process in #2 of this ongoing blog theme.

If you would like to chat further about our journey taking on an apprentice. Please email Ben@theexcelexperts.co.uk with ‘apprentice’ in the subject line and between myself and my wife, we can advise you further.

#Apprenticeships #Apprentice #Employability #Kickstart #JobsForFuture #Digital #Skills #YoungPeople #Youth #PlanForJobs

Why SME’s should spend money on rebranding when we’re on the edge of a recession

How are you getting on with zoom and other online channels for collaborative working? Do you still have ‘in the flesh’ meetups with your work based colleagues and clients? Do you want to??

At the beginning of lockdown, as a company, we were, like many millions of companies and individuals thrown, unceremoniously, into a sea of doubt and insecurity. That sometimes feels like enveloping us. SURE we all have good and bad days, so WHEN do you start seriously thinking about how you are going to emerge from the other side of these problems in a healthier position than when you went in?

Should we bunker down and dream of sunnier times ahead, and there surely will be many sunny days ahead let’s make no mistake. WHEN we get over this crisis as individuals, as companies, countries, and one planet and its peoples, how will we be? Relieved! Yes!

We decided to hire a design company to rebrand our business. To think ahead to what people want most, and how we as an Excel agency can play our part in the new Post COVID Economy. Believing that we may need a new business model moving forward, and a much clearer vision of who we are and what we represent.

In the end, we are what we are as a business because we like to work with people and meet people, talk to people, and help solve their problems for them. I can vouch that this is true, for every expert in our team and I’m proud of that. At the moment our website just doesn’t look … well.. very friendly… We really hope that the new one will be and that our website visitors and hence our new customers, will come to value that quality all the more.

If you have a great service and want to put that message ‘out there’ then I’d argue that rebranding your business is a really worthwhile thing to consider. It’s not just about making up a good sales pitch, although that is clearly important. It’s about refocusing on the qualities that matter to you about your business. WHY did you start this business in the first place? What gets you up in the morning? Then taking those insights and working them into a new vision, a new brand. Hope you enjoy the trip.

Can we blame Excel for the Covid data fiasco, or the people that used it?

The apparent strengths of Excel can also be its weaknesses.

The problem with the Covid data fiasco was NOT a problem with Excel. From what I can make out, someone didn’t realise that the old Excel format had a large, but possibly limiting, size on the data it can process. (BBC News)

The covid data in question exceeded that limit. IF the government had used the newer xlsx format that every reasonably recent version of Excel can handle perfectly, this problem would not have happened.

I’ve been reading suggestions all morning that the problem was in using a spreadsheet instead of a database. Using Excel I’d argue, was a perfectly good idea, Excel can perform the main functions of a databased perfectly well, it just wasn’t set up quite right…

You could store the data in a proprietary database if you wish. Even use the mysql database that you have on your website but it’s not essential! The problem with rewriting for a database to me is.. it took this long to make it work in Excel!  Stay with the system but use it properly please.

THESE are some of the problems with Excel:

  • If a spreadsheet is created by the ‘end user,’ they will generally be bypassing IT project discipline and oversight as well as development processes and methodologies. If this occurs,you then you may get the problems that you would expect. This is not a problem WITH Excel, only with the way it CAN be used. A robust testing system is required and this is possible to build into Excel.
  • Data can be accidentally or erroneously entered into a spreadsheet with possibly disastrous consequences. It takes a little work to lock Excel down so that accidental deletion and changing of data cannot occur. Again, this is possible though.
  • Sometime you don’t want everyone to have access to everything in a spreadsheet. Again, It’s a simple matter to add a ‘login’ system on Excel so that users only see the areas that they need. Once in, they can only change the cells that they need to change.
  • It is possible to covert old Excel file formats into newer ones very easily. In doing so you also improve the system so it safely and efficiently complete all of the functions needed.

Possible ways to move forward.

This is why I have approached the Government and offered the services of our experienced Excel Experts, for free. To help solve these Excel issues without the need of wasting more money on a bespoke database. If we think back to the NHS database issues, it might be wise for the Government to take us up on this offer, and let us solve their Excel problems.

After all, that is what we love to do best!

How an Agency can help businesses adapt to the new workplace.

The Excel Experts is a large team of people with collectively hundreds of years experience in working from home (WFH) solving problems in Excel and automating complicated procedures in Excel for clients. They love working with Excel and getting the best out of it for a client.

The Excel Experts can make a significant contribution to the new post Covid19 business world, and can help your business succeed in these challenging conditions.

The Covid19 pandemic has completely changed the way in which businesses work.  Many businesses are now allowing their employees to work from home for the foreseeable future in order to observe social distancing. Most businesses are now finding that they cannot social distance their employees with everyone present in the office.

The Excel Experts has a huge amount of experience in dealing with every Excel problem known and also doing this operating remotely (WFH).  The team of experts is very used to sorting out client requirements, making suggestions for improvements, and building a good working relationship without ever visiting the client’s premises.

Almost everyone uses Excel day-to-day for various tasks, but very few people realise the enormous potential that it has in saving time and making your life easier, and this is where The Excel Experts come in. Excel has its own programming language, and this can be used to develop automated systems cutting a task of several hours down to a few minutes, saving costs and freeing employees to deal with other tasks.

With the Excel Experts, you do not need to recruit a permanent ‘Excel Expert’ with all the associated overheads involved e.g. Employers National Insurance (13.8%) and pension contributions. The Excel Experts provides people instantly who are well versed in home working on Excel, and who will provide value for money from day one. 

Whether you want an Excel expert for a day or for a longer period, The Excel Experts can easily adapt to your requirements and offer flexibility. Think of it as Uber for Excel!

The Excel Experts offers significant discounts on day rates, and further discounts for extended periods. Using an Excel Expert would be far more economic than recruiting a permanent staff member.

If an Excel Expert goes sick, or is unavailable due to other reasons, another Excel Expert can seamlessly take over the project.  The Excel Experts work as a team and support one another.

One of the biggest fears of a business over allowing working from home has always been ‘how confidential is my data?’

The Excel Experts is compliant with the General Data Protection Regulation (GDPR) and other data protection regulations.  The Excel Experts assures confidentiality of data and Excel spreadsheet material

The Excel Experts has years of experience in dealing with private data and material, and Non-Disclosure Agreements (NDA) with clients are perfectly acceptable.

How to install and use the phone App for Google Classroom to make it really easy to do.

Installing Google Classroom on your phone

Several parents have complained to me about using Google classroom especially the sequence required to photograph our childrens work and upload it to Classroom.. it can be a several step process.

Here is an easier way, use the phone app – it makes it all much easier 🙂 I’ve made a quick tutorial on how to install and use the app for this purpose. Hope it helps

Picture of Installing Google classroom app on your phone
Install Google classroom app on your phone

So first its a trip to Google Play Store and install the Google Classroom App

on to your phone.

Accessing the work that has been set by the teacher

Open the App and choose the class you want

Picture showing selection of the class on the Google Classroom app
choosing the class on the app

At the bottom of the screen you can see 3 icons in Google Classroom

Picture to illustrate Click on Classwork Icon at the bottom of the android phone sreen

Click on Classwork Icon at the bottom

Submitting work to Google Classroom

Grey means the work has been handed in and blue, still to be submitted

Picture showing selection of a particular assignment in Google Classroom

Lets see whats being set for Foundation on Monday click the icon

Picture of the assignment in Google classroom


And here is the list of all the attachments and video for the days class.

Click on Add attachment button, this is where you will submit a piece of paper that you’ve been working on

click take photo
Take the photo click OK

its done

At the bottom you can see the upload you just created. Your teacher can see what great kids you’ve been and you can add more documents.

submit assignment on Google classroom using the phone app and adding a personal message for the teacher

IF you want to add a comment or two about the submission you can write it here. Then press the ‘Hand In’ button.

Its DONE. Any comments please let me know

Remote Collaboration-Sharing data with a team.

Using Microsoft Flow for process automation – part 2.

Following on from our first blog about Microsoft Power Automation formerly know as Flow, we’re going to move on down the flow program looking at each action as we go…

If there was a buzz phrase for the Post-Covid New Dawn it must be ‘Remote Collaboration’ so we must start with a discussion about this…

As a team, we are a rather disparate group of individuals here at The Excel Experts . We work together on projects sometimes, but essentially we’re all experts at working from home, and using the various forms of Remote Collaboration available such as Skype, Zoom etc.. Within the group, we use mostly emails and, as everyone knows a mailbox can fill up pretty quickly when business is doing well.

So we felt very much that the time was approaching, when we needed a better platform to work from than an outlook address book of consultants and a folder of email projects.

It also became obvious that as an agency that revolves around Microsoft technology, mainly Excel and Outlook… Microsoft Teams was going to be the best platform to reduce our dependence on emails. I was able to add all our consultants as ‘guests’ and this seems to give them the required access that we needed to move to the next stage. Our team now have a really good environment to help collaboration and sharing of ideas, online, remote working from home. Well done Microsoft.

Sharing Data with your Team

We wanted to share the leads with our Team and this is easily done in MS Flow (now known as Power Automate)

a Microsoft Flow diagram

Using the ‘Post a message’ action its straightforward to lift the data inserted into the form, and post it into a teams channel using a little bit of html

POst a message to teams in Microsoft FLow

Same for the ‘Html to text’ action click on the </> symbol to insert the html…

Using HTML to text action in MS Flow

Using Microsoft Planner

The above HTML looks like this

using Microsoft Planner in Flow

So we now have a message in the team channel about the lead, and we have a new task in the team Planner.

In the next part we will be looking at ways of using planner and flow to help link up other parts of the business. Every time we automate a repetitive process, we save ourselves time, time to hopefully do more enjoyable or certainly more productive things.

Reading MS Forms and Gaining ‘Approval’

Using Microsoft Flow for process automation – part 1.

Recently I’ve been using Microsoft power automate tools otherwise known as flow to automate some of the processes that I have to do on a regular basis. I’m hoping that this account may be useful to somebody else who’s thinking of using this technology.

As a small business owner I’m always looking for ways to increase my productivity, and hence increase our value to our customers. The two seem totally linked in my mind. If, as a manager, I spend my whole time doing ‘Admin’ when do I get a chance to plan for the future. Especially at the moment, any manager not thinking hard about the ‘Post Covid era’.. well they should be!

When we first started up as an agency, we simply carried on with much the same business processes as I had developed as a sole trader. Like many offices, we Initially based our ‘system’ around emails. Emails were flying backwards and forwards everywhere and after a couple of years of sustained growth, it became apparent that the most common means of communication wasn’t necessarily the best medium available. The inbox just got more and more full..

The hunt was on to look for another way, and in doing so, reduce the administrative burden. After experimenting with an off the shelf CRM for a year or so, we decided that the only way forward was to build our own system.

if you have Microsoft Office you probably already have free account for power automate, Here’s a decent link to a explanation of how to set the account up and how to access it. Basically if you go to your Office Admin centre it should be listed amongst your available apps.. in this blog we will be using Outlook, Power automate, Forms, SharePoint, Teams and Planner.

The MS office apps available.
300

On our website we have basically three ways for our customers to contact us:

  • calling an 0800 number
  • direct email and,
  • filling out a form.

When people contact us by one of these means, an email is always generated by the existing systems and sent to my inbox.  I would then  allocate the leads manually to team members via email. So during lockdown while business was going to be very quiet, now was a great time to take the time to automate automate automate.

The first thing we did was swap the various forms on our website for Microsoft Forms. This turned out to be very easy.. When you create a new form you are given the option for generating a link or for embedding the form in a normal webpage. The information that visitors now enter into the forms can be easily handled with Flow in many ways.

I wanted to be given the option of not publishing all leads automatically, sometimes I want to allocate a job to a particular individual or group, In Flow this is called an Approval and you can set it up so that when a form is filled in, the information presented is put into a message and sent to your mobile phone where you can accept or reject it.

Send approval request when a MS form is filled in by site visitor

If there is more than one form response we need to put the whole procedure in a loop so that each request is handled. Notice that we then test if the Approval was ‘Approved’ and if so we will take the left hand branch labelled ‘If Yes’ so far so good.

The 'Yes' option for our MS Flow

For us the next consideration was where and how we were going to store our response data, then how we were going to best disseminate that information to our various teams.

After some consideration we chose to use a SharePoint list. Initially I wanted to use a  MySQL database, but after some research it appears that connecting Flow to MySQL was not very straightforward, MS haven’t currently published a connector. Staying within Microsoft is likely to be the best way to use Flow, although there are loads of connectors to many other databases. Anyway, we already have SharePoint as part of our Office 365 subscription, time to use it.

It’s easy to pass Forms data to SharePoint, but we needed each record to have a unique LeadID. This can be done in various ways in SharePoint, but they all turned out to have their issues. So in the end we decided to use a repository called CDS or Common Data Service to generate the LeadID.

CDS is a repository in that it creates a unique ID, so we used it to generate a new record for each form entry and then store the information in our SharePoint List. Why not leave it in CDS you ask? It looks like CDS is really intended for people using Dynamics 365, we’re not, and anyway Dynamics is a bit too big for us just now..

So we create a record in CDS and then after some processing we use that LeadID as a projectID when creating a new SharePoint item.. I can FEEL the criticisms of this approach already, it is a bit messy but after a day or so of hunting around this turns out to be the best scenario for our purpose.

passing CDS id to a SharePoint List item

Next time we will look at how we actually do this, the options we have after these steps, and why we chose to go with MS Teams.