Calculating the Day of the Week Without Code

This blog is inspired by a forum post I read where someone asked to display the day of the week in a CRM view. Unfortunately, I cannot find the request now but it is possible (and without code).

In the above, the Input Date is the date we want the date for and the Day of the Week shows the correct day.

The Calculation

For this one we need the following fields.

Working backwards:

• Day of the Week: An option set of the days of the week (set via a workflow)
• DOTW Number: A Whole Number which shows a number representing the correct day of the week 
  (a calculated field and, in this case, a number between –3 and 3)
• Dodgy DIV: Strictly speaking, not a mathematical DIV but close enough for our purposes. In our case, this represents the week our Input Date is in. It is a calculated field.
• Input Date: The date of interest, entered by the user.
• Reference Date: A date we know the day of the week for. In this case, 1/1/2015 which was a Thursday

To anyone outside of the USA, Belize, or the Federated States of Micronesia, I apologize for using “Middle Endian” formatting for my dates. It is a blatant play to the American market on my behalf and does not affect the calculations.

In terms of the Reference Date, my preference was to incorporate this into the Dodgy DIV calculation but I could not figure out how to input a fixed date e.g. “01/01/2015” into the calculated field. CRM either thought it was an integer or thought it was a string. If anyone knows how to do this, please leave a comment at the end.

Given I could not feed my reference date directly into the Dodgy DIV calculation, I had to automatically enter it into a field via a Business Rule. Here it is.

I tried using the Set Default Value action but struggled to get it to behave so I just used the Set Value action in the end. I also was forced to put in a condition so I selected one which is always true.

With this value set, I could then calculate the Dodgy DIV.

A couple of issues with this formula, which is why it is dodgy. What I wanted it to do was calculate the difference in days between 1/1/2015 and our Input Date and then divide it by seven. It turns out that the DiffInDays function has a pretty huge bug in it, in that it gets the difference in days wrong. I found that if I put in the same two dates into the DiffInDays formula, it returned –1 and not 0, as expected. This is why I add one.

The second issue is, in C (the language I used to code in a loong time ago), if you divided two numbers and put them into an integer variable, it rounded down, effectively being a DIV operation. Dynamics CRM does not play this way and applies a normal rounding operation (x.5 or more goes up and less than x.5 goes down).

Using my Dodgy DIV, I can now calculate the day of the week. The DOTW Number (Day of the Week Number) is also a calculated field.

This is where the clever trick comes in (if I do say so myself). Here we calculate the difference in days again, add one to account for the bug in the DiffInDays formula and take away the Dodgy DIV value, multiplied by 7. Because the Dodgy DIV field has applied a rounding, the difference is the Modulus (also know as the remainder) or it would be for a pure DIV function. In our case we generate a number between –3 and 3, rather than a number between 0 and 6.

Finally, we have our days of the week. Unfortunately there is no simple way to set the Option Set Value with a formula using a number so I had to use a real time workflow.

The workflow is triggered on creation of the record and on the changing of the Input Date field. There are seven IF statements, one for each day of the week, linking the DOTW Number to the right day.

Once all of this is done it works like a treat, with the day value being set on the saving of the record. It is then a case of adding the Day of the Week field to the view you want to see it in.

Conclusions

I like this solution as it opens the way for quite a few other requests seen in CRM systems. For example, with the day of the week we can check whether a day is a work day when setting an appointment. Although I have not fully figured it out how yet, I imagine we could also use a similar technique to work out the number of working days between two dates. So many possibilities thanks to calculated fields.

If you are not exploring how calculated fields can help you manage your business processes in Dynamics CRM, perhaps you should because, as you can see above, they open up a wealth of options, previously inaccessible, without using code.

The Salesforce Drinking Game

 

Here is the Salesforce drinking game I play when listening to the quarterly transcripts (because even I have to make it a little more interesting). You pour a drink every time they mention Non-GAAP. You get to take away a drink if they mention GAAP.

I currently have 12 drams of one of Scotland’s more affordable single malts in front of me thanks to the drinking game. Every quarterly review from now on I will report the ‘dram count’ for the report. Given this may run my whisky supplies dry, I am open to sponsorship by whisky makers or bottle donations (come on Marc, I know you are good for it). All contributions welcome.

Why is Non-GAAP so important to the quarterly reports? Because it allows Salesforce to turn their persistent losses into a mythical profit. If they applied the same accounting treatment to their carbon emissions, they could claim that every time you use Salesforce, a glacier grows.

I fully expect to detox when Salesforce turn a profit because all of their reporting will transform into GAAP numbers. Until then, here is mud in your eye.

Numbers of Note

Normally I post the big table of numbers. I will still use the big table of numbers but it strikes me you are probably more interested in the insights than the data so I will still be using the same numbers, just not filling up the blog with the table. For those new to the game, I take my numbers from Salesforce’s own website but rather than use the ‘creative’ numbers of their press releases, I use the ones they report to the SEC because you get in trouble if they are wrong.

Profitability

In the last two quarters, Salesforce was heading in the right direction. A rental adjustment meant a tiny profit was turned in quarter one and the loss was not huge in quarter two but, unfortunately Salesforce are back to their usual form this quarter with a $25m loss. Compared to the losses of the past (Salesforce lost a little over a quarter of a billion dollars last financial year) it is not huge but still, it is not a profit.

Transaction Growth

I predicted last quarter that transactions would go flat. Fortunately, for Salesforce, I was wrong.

 

Transaction growth, which had been steadily falling, had an uptick this quarter, back to 16% growth.

Revenue and Cost Growth

The year on year revenue and cost growth tell a story about Salesforce’s history and gives insight on where they are heading.

If we look at where the solid green and red lines cross, we see three periods in the history of Salesforce.

The Sensible Years (before 2011 Q1 i.e. before 2010)

Before 2011 Q1, that is 2010, revenue growth was on top. The business flourished combining high revenue growth with increasing profits and market share. Accounting did not have to be creative and the business could do no wrong. Here is the transcript from 2010 Q4 where, not surprisingly, reporting was in GAAP numbers. This changed, and in 2011 Q2, we had a mix of GAAP and Non-GAAP reporting and we finally transitioned to full Non-GAAP reporting in 2011 Q4, just prior to the losses appearing.

The Aggressive Years (2011 Q1 to 2015 Q1 i.e. between 2010 and 2014)

This is when marketing and sales costs grew faster than revenues, thus the creative accounting. The official line is the strategy ensured aggressive market share growth. My interpretation is the executive, being compensated in shares, wanted to maintain the share price while they offloaded their holdings (the executive have consistently sold their shares in Salesforce despite their optimism for the company). With the market fixated on revenue growth, the decision was made to sacrifice profit to ensure revenues continued to go through the roof. Non-GAAP reporting helped hide the losses which resulted.

Interestingly, Salesforce only started making a loss a year into this period (just after the switch to Non-GAAP reporting) so if you wanted to predict when Salesforce would go into the red, the growth chart above predicted it a year in advance. For Salesforce, revenue and cost growth is an excellent leading indicator.

The Buyout Years (2015 Q1 to the present i.e. 2014 onwards)

Since 2014, Salesforce has focussed on getting marketing and sales spend under control and they are doing a pretty good job of it. The last couple of quarters, the growth difference has been a consistent 5%. If revenue and cost growth is a leading indicator, this suggests profitability in the near future.

As suggested in my labelling of this period, and influenced by recent events, I see this as the executive getting the books in order for a buyout.

The idea of a return to profitability is also backed up by the margin graph.

While the solid blue line is all over the shop, the four-period trend line is heading upwards towards the positive.

Earnings Call Buzzword Bingo

Here is the list of words mentioned 10 or more time in the last five quarters

	2015 Q3	2015 Q4	2016 Q1	2016 Q2	2016 Q3
Number of words	3922	4017	3495	4175	5147
Customers/Customer	34	23	34	42	41
Revenue	26	25	24	18	16
Cloud	47	32	28	20	18
Platform(s)	27	18	14	16	13
Service	12	5	9	13	4
Growth	16	13	16	17	21
Cash	9	10	11	11	8
Operating	8	20	11	13	8
Dreamforce	14	2	1	21	7
Analytics	14	11	9	4	1
Software		12	4	9	10

Sadly we lost ‘Marketing’ and ‘Enterprise(s)’ which seem to no longer be a focus. I have heard mixed reports on the success of the ExactTarget acquisition which may explain why Marc is moving away from talking about it. In terms why ‘enterprise’ is on the nose, I assume Salesforce is no longer bringing in the big deals they used to brag about so much.

As for other trends in the words, ‘Revenue’ mentions are steadily declining as the business slows down. Talk of the cloud and Salesforce as a platform is also diminishing. Marc says the literally hundreds of CEOs he talked to in the quarter do not care about the cloud, but about their customers so maybe this is to blame. This being said, one of the key selling points of the past was the Force platform so to move away from this is curious.

Google Trends

I am dropping the Google trends from the quarterly report. Dynamic CRM has won the Google search war and is the more international product. To replace it, I am adding the ‘Truth in Transcripts’ section below.

Insider and Institutional Sales

It is a Black Friday sale for the shares of Salesforce.

	2015 Q3	2015 Q4	2016 Q1	2016 Q2	2016 Q3
Insider Sales	4.70%	4.60%	4.50%	4.40%	4.90%
Institutional Sales	3.20%	3.11%	3.04%	3%	5.87%

As I predicted last quarter, the insiders (such as the board) have increased their selling and the institutions are also selling out, going from selling 3% of their holdings to almost 6%. The insiders are getting nervous? Do they think the price of the shares is about to crash?

Looking to the Future

I predicted Salesforce would break even but they made a 25 million dollar loss so no joy there. In terms of revenues, I was 4% off which is not too bad. Next quarter I predict $1.8b in revenue and another $25m loss.

Truth in Transcripts

My new section where I examine the claims made in the quarterly transcript and challenge them in reference to the GAAP numbers reported to the SEC. All quotes taken from the Seeking Alpha transcript.

Here are the creative claims this quarter:

“And of course, Salesforce, as you can see will be the fourth largest software company in the world next year, but you can see that we'll be one of the only software companies ever to reach $10 billion in revenue.” – Marc Benioff

I think this was a genuine mistake by Marc. I think he meant to say “…$10 billion in revenue within <x> years”. Microsoft alone pulls in over $20b a quarter in revenue so the statement is clearly wrong.

 

“…we also delivered…margin improvement” – Marc Benioff

In Non-GAAP terms perhaps. In GAAP terms, margins fell 1.4%. As shown above though, the longer term trend suggests things may improve in the future.

 

“…we are also deeply committed to continuing to increase our profitability, and the results this year are evidence of that.” – Marc Benioff

“From a bottomline perspective, we delivered another quarter of improving profitability.” – Mark Hawkins

GAAP margins have gone down each quarter this financial year as have profits with the last two quarters showing a loss. The long term trend is positive but this quarter, and the previous one were not great for margin improvement.

 

So the misdirection is mostly around profitability. Clearly this is the chink in the Salesforce armour. It is really difficult for them to make as much noise as they do through marketing and make a profit.

Conclusions

Salesforce continues to struggle with making the business profitable and, while things have improved compared to the aggressive years, the executive are still wrestling to stabilise the business. If they can keep revenue growth above cost growth they should be ok but it is clearly hard for them to keep costs under control. As usual, I look forward to the next quarter to see if management solve the riddle of making a profit or whether they will continue to keep me drinking, stick with the Non-GAAP fairytales and pretend the ship is in calm waters, rather than admitting it is heading for an iceberg.

Post MVP Summit Roundup

 

Another Summit comes to an end and I am back in Australia waiting until next November. In the photo, I am in green mid-way up on the left in front of CRM greats Chris Cognetta and Gustaf Westerlund.

What is the MVP Summit?

The MVP Summit is one of the bigger perks for being a Microsoft MVP. Every year, Microsoft hosts a conference exclusively for all of the MVPs across all products. This year about 2,000 MVPs came to Redmond to meet their respective product teams and learn about what is coming up with their product.

Meeting the CRM product team is great but, just as delightful, is meeting my fellow MVPs from around the world. About 50 of the world’s CRM MVPs made it to Summit this year and I am yet to meet an MVP I did not like. Their passion and willingness to share their knowledge is incredible.

The days were spent in presentations with the Microsoft CRM product team.

but there was also time for a bit of socialising after hours.

Lots was learned both on the Redmond campus and off campus.

What Did You Learn?

Unfortunately, much of what is discussed at MVP Summit is under a Non-Disclosure Agreement (NDA). If I reveal too much I get kicked out of the MVP program. To be honest, the roadmaps and directions of the product are subject to change and refinement so there is little value in publicising a feature only for it to slip another six to twelve months.

What Can You Tell Us?

The good news is some of our time with the product team was spent reviewing the changes coming in Dynamics CRM 2016, as described in the Preview Guide, which I went through a few weeks ago. With it being tricky to get demos of the complementary products to Dynamics CRM such as Microsoft Dynamics Marketing, seeing them in the flesh gave me a much better appreciation for their capabilities so I thought I would go through some of my takeaways for what is coming towards the end of this year. To play it safe, I will focus on the products reviewed in the Preview Guide (although Field One looks really good!).

Parature

Parature got as much coverage as it did in the Review Guide i.e. it was not shown at all. Read into this what you will.

Microsoft Dynamics Marketing (MDM)

We got to see first-hand the SMS Marketing capabilities coming in the next version. You can send SMS messaging out, with custom fields and the recipients can reply for actions such as unsubscribing. Imagine doing a mass email in CRM and the experience is similar. If SMS marketing is important to your business and you are already using MDM, this will be a welcome addition. Unfortunately it will only be available in limited markets initially which does not include Australia. Given SMS marketing is not that big over here, I can wait.

CRM App for Outlook

This is not to be confused with the Outlook Client for Dynamics CRM. The CRM App for Outlook is an add-on to Outlook.com which allows tracking of emails, creating contacts, and the opening of CRM records directly from the outlook.com interface. This is huge for a bunch of reasons but one reason is it gives people with Apples the ability to track, which was not previously possible. Seeing it in action really brought home how useful this will be.

OneDrive for Business

OneDrive will now be a document store for Dynamics CRM. Its behaviour will be slightly different to SharePoint in regards to security and visibility. While SharePoint security is managed by SharePoint with documents being visible to all who can access the store, regardless of CRM access, OneDrive security its with the CRM user.

So, if a user adds a document to a OneDrive store in CRM, unless they share it, only they can see it in CRM. If another user goes to the same CRM record, the document will not be visible until the author shares it.

Initially I thought this was a bad thing but, on reflection, it simply provides options when implementing the system. For example, let us say CRM is being used for storing counselling sessions. While the fact that a meeting was scheduled may be public, the visibility of the notes of the meeting can now be controlled by the counsellor.

Interactive Service Hub

We got to see these first hand and, for service centres, this is a big leap forward. While managing cases is handled very well in Dynamics CRM, doing it in volume is hard. The new service hub seeks to address this by providing tools for high volume call centers. There is also a new form type, specifically for the service hub to help with efficient case processing. My preference would have been to bring the benefits of the new form type to the normal form layout but perhaps this will happen in time.

Mobile Offline Support

We saw this in action and it works in a similar way to the Outlook client’s offline capability with the playback graph. I am very happy for this one and to see it uses mechanisms already familiar to the product team gives me confidence it will do the job.

Next Generation Search

This was another feature demonstrated which, in effect, takes the columns of results generated by the universal search tool and compiles them into one list making good guesses as to the most relevant record. I like the idea of it and, if the universal search is activated for many entities it should make finding records much easier.

Conclusions

I was excited when I did my original summary of the Review Guide but seeing the functions in the flesh at Summit has made me realise how much effort Microsoft is putting into the product and how efficient they are at achieving results. We really are seeing the kinds of innovation in 12 months we used to see in three or four years in the past. I now have two things to look forward to; the release of CRM 2016 and next year’s MVP Summit in November.