Office 2.0 – That was the word I was looking for

It seems that while I was rambling about online collaboration and Excel as a design and programming tool. Some guys have already called the concept – Office 2.0. Rod Boothby’s blog is an excellent place to start reading about Office 2.0 despite the fact that every so often he gets overly excited about Web 2.0 and Office 2.0.

Purchase-order tracking system based on Excel 2007: current process and requirements

Current process

In a previous post, I said that we would be developing a new purchase-order tracking system in Excel 2007. I started by interviewing the customers. I sat down with Inbal, our “accounting department,” and this is what she said …

“Today, we have a licenses management solution [Nick named Hendrix]. Every purchase is entered into Hendrix, and the customer then receives an automated e-mail from Hendrix with his payment code and download and activation instructions.”

“When a purchase order arrives, it is entered into the system, so that a license will be issued to the customer. I also manage an Excel sheet with a list of POs to track.”

Here is a sample of the Excel sheet that Inbal uses:

Purchase Order Tracking Sheet

“Purchase orders are not paid immediately; they usually have net or net+30 payment terms. it’s my job to see that all the payment orders are eventually paid,” Inbal added.

“So, every week, I single out the POs that have not been paid, and according to how late the payment is, I either send an e-mail or mail, or I call the customer. When a purchase order is paid, I update the tracking sheet to reflect that, this way I know not to bother the customer.”

Users’ requirements

I then asked her what she would like to the tracking system to do.

“I need to be able to define contact details for both customer and end user,” Inbal said. “This is because sometimes (especially with POs), the customer’s accounting department is ordering the software for their users. So I need to send the download and activation instructions to the user and work with the accounting department on payment issues. In any case, the customer has to be notified about the delivery of the software as well as the user.

“I want to be able to define the due date for each PO (net, net+30, etc.).”

“I want to get a weekly report of all the purchase orders that should have already been paid. If possible, I want to get it by e-mail.”

“When closing an order, I need a place to enter the payment date and some sort of transaction ID (check number, bank transfer ID, etc.).”

And that was it.

If you have any ideas on how to make this system better, please, pretty please … add them as a comment to this post.

Purchase-order tracking application with Excel 2007: kickoff

The company I work for, Cogniview, offers customers various ways to purchase its products. A customer can use PayPal to purchase online, send a fax order, or send a purchase order by fax or e-mail.

We have had an unusual amount of purchase orders lately. Everybody is happy when a PO comes in, but our accounting department started complaining that tracking those purchase orders is becoming difficult.

We had a meeting in which we agreed that some sort of purchase tracking system should be created. I persuaded everyone that it would take a tenth of the time to write it in Excel 2007 then in C++ (the language of choice for our programmers). So I have been awarded the pleasure of writing it myself.

In the coming week or so, I will share with you the process of designing this system and implementing it–successfully, I hope.

I will also make the Excel application available to anyone who’s interested.

Excel Web Services: What is it?

In the previous post, I wrote that Excel can be a powerful tool for collaboration between business users and programmers who are designing and developing applications. In order to explain this in greater detail, we have to cover a few topics, the first being Excel Web Services.

Excel Web Services allows programmers to use a spreadsheet or template for programming purposes. This is done by filling the sheet with data, running its formulas and using the resulting information.

To better understand why Excel Web Services can be so helpful to programmers, let’s listen to two conversations between imaginary corporate employees. Meet Mark Money, ACME’s CFO, and Java Jake, a programmer. Unfortunately, ACME has not yet adopted Excel Services.

MM: Jake, good work on the project financials screen. I need you to add a field that shows the NPV for each project.

JJ: Thanks. What’s an NPV?

MM: NPV stands for “net present value.” It’s a way to check whether investing in a specific project will bring in more profit than depositing the same amount in the bank and collecting interest.

JJ: That sounds really complicated. How do you calculate NPV?

MM: I’ve got an excellent book about this (drops a 300-page finance book on the table).

JJ: Well (weighing the book) . . . OK. This is going to take me two weeks minimum to figure out. Plus, since I’m not sure I’ll get it right, I’ll need you to prepare some test data so you can check my algorithm.

MM: Two weeks! Test data! Forget that; I’ll just use my Excel template.

Let’s also eavesdrop on a conversation between Phil Funding and Esther Excel, employees at Early Adopters Corp., the first company to try out Excel Services.

PF: Esther, good work on the project financials screen. I need you to add a field that shows the NPV for each project.

EE: Can we calculate that using the data we already have in the system? Can you prepare an Excel spreadsheet that shows how to calculate it?

PF: Yes to both of your questions.

EE: Great, just send me the spreadsheet and I’ll have it done. It shouldn’t take more than a day.

PF: Wow, that’s wonderful. How can you do this?

EE: I’ll use your spreadsheet as a Web service. For every project you view in the project financials screen, I will fill the spreadsheet with the project’s data, get the result and place it in the NPV field. Also, if you need to change the way this NPV thing is calculated, you can just change the template, and I’ll update the Web service in about five minutes.

PF: Sweet!

For the programmers among you, David Gainer of Microsoft gives a more technical introduction to Excel Web Services here and here.

No fictional characters or companies were damaged during the production of this post.

Excel as a design and programming tool

About three weeks ago, I attended a lecture about Excel 2007 and Excel services. I have to say that I was pretty impressed. Not only does Excel have a ton of new features and a brand-new (and much better) graphics interface, but you can also now publish Excel spreadsheets on a departmental server (Microsoft Sharepoint). This means that you can let your colleagues and partners view business-related data and graphs through the company’s intranet or extranet. You also may use a spreadsheet as a calculation engine (I will try to explain the concept of a calculation engine later on).

As I drove back to the office from the lecture, a thought hit me. Maybe Excel 2007/Services is the first software package that can create a real and effective dialogue between business users and programmers. For the first time, there is a tool which is both understood by huge numbers of business users (who know how to use Excel far better than programmers) and can be used as a development component.

If this is true, this is an opportunity for business users and information technology workers to radically reduce implementation time of new systems and to engage in a much more effective (and pleasant) dialogue.

I will explain more about this in the upcoming posts.

← Previous Page