Go to Top

Capitalize Analytics Blog

Upcoming Webinar! Vendor Spotlight: Agile Upstream

Know More with AI: Let the Software Read Your Leases and Agreements

Wednesday, June 06, 2018, 12:00 pm – 1:00 pm CDT

Finding critical information in lease contracts is a tedious process and can be costly even if one single provision is missed.

Agile Upstream has developed an Artificial Intelligence platform providing E&P land departments the ability to quickly upload lease documents, extract critical data, and review and validate vital information. A process that typically takes months can be completed in days, allowing for decreased financial risk and liability, and improved operational efficiency.

Join Capitalize Analytics and Agile Upstream to hear how Agile is transforming the way information is managed and leveraged in upstream and midstream.

Ideal for: Land professionals, Technology and Strategy Experts at E&P and midstream companies

Key takeaways: Hear how cutting-edge technology is quickly surfacing the critical data in contracts and lease documents needed to make accurate strategic decisions.

After registering, you will receive a confirmation email containing information about joining the webinar.

Can’t make it? Still register and we’ll send you the recording!

Register

NAPAC 2018

This week, Capitalize will attend and exhibit at NAPAC, the North American Petroleum Accounting Conference, Thursday, May 17 & Friday, May 18 in Dallas, Texas at the Westin Galleria.

We look forward to seeing old friends and introducing ourselves to new ones!

Stop by and see us!

 

O&G Tax Calculation: A Cognos Success Story

When it comes to taxes, the US government can be particular about how they want taxes prepared and calculated. In the state of Louisiana, taxes are applied to the physical well’s volume and sales allocated, and not to the tract. A tract is an ownership percentage of one-to-many physical wells.

The problem: Our client has an allocation system that applied taxes to the tracts and not to the physical wells. The client attempted to take those tract results back into the physical wells, which was proved to be difficult and time-consuming. Complicating matters further, two sets of physical wells were added together and then split 25/75 based on contract terms, and some were Take-in-Kind contracts. This led to too many errors in taxes paid.

The client also had to deal with paying on six different types of taxes. Three of them were volumetric-based, while the remaining three were based on sales value. Depending on the exemption status of the physical well, applying three tiers of rates depending on exemption certificate was also needed. This led to 12 different tax entries for each owner tract settled.

The solution: The allocation system that the client used already contained all the information we needed to produce the required result: physical wells’ volume, the rates, liquid allocation, and meter type to determine which rate to use.

We created a report that would calculate the information we needed and be easily loaded via template into the client’s database. This report calculated the taxable volume for taxes paid on volume per physical well and on calculated sales condensate values. Each meter was broken out by the six taxes charged in the report to be uploaded. Unlike the tracts, physical wells could only have six different charges of tax on Gas and Liquids, while tracts could have up to 18.

We created another report to help the tax department determine which physical meters needed to apply for tax exemption or reduction based on flow hours. Lower flow hours allowed them to apply for a reduction in tax rates in a timelier manner.

The Conclusion: These new reports helped the tax department process tax filings faster and created a more efficient means of communication between the tax department and accounting pertaining to the application of exemption to physical wells. The solution also prompted the client to invest resources in accounting software that mimics the report function in the application itself, by calculating the taxes before dividing them into tracts.

For additional information, please contact us at marketing@capitalizeconsulting.com!

Upcoming Higher Education Webinar: Five Cognos Tips Every Banner Client Needs to Know!

If your institution has Banner and you want to take full advantage of Cognos, then this is the webinar for you!

Capitalize Analytics is an IBM premier business partner focused on helping higher education get real meaning from their data. We understand today’s institutions are pulled in multiple directions and challenged to meet higher expectations with fewer resources.

In this webinar, we will demonstrate:

1. Dynamic Dashboarding: Explore where your departments are spending their budgets.
2. Geographic Dashboarding: Use maps to compare the high school performance of admitted      students to the campus average.
3. Drill Through Dashboard to Reports: Discover student retention demographics by course        and department with drill through reports.
4. Communicating Information through Bursting: Burst your budget reports so college                deans know where they stand financially.
5. Getting Notified about Changes in Status from Event Studio: Notify your financial aid            students when they fail multiple classes.

Attend our webinar and let us show how you can get the most out of Cognos for Banner!

Register

After registering, you will receive a confirmation email containing information about joining the webinar.

Upcoming Live Webinar! Revolutionize Analytics for the Oil, Gas, and Energy Industries

Thursday, May 10th , 2018 at 1:00 pm CDT

Join Alteryx and Capitalize Analytics as we take a deep dive into how self-service analytics, combined with a complete end-to-end platform, can revolutionize how people in the Oil, Gas, and Energy industries can use data to gain valuable insights. Walk away knowing how the right platform can help you:

 

• Accurately anticipate energy demands, like peak usage time, so you – and the power grid – are never caught off guard

• Understand the root cause of failures to eliminate them in the future, or predict and address possible problems before they happen

• Closely watch your supply chain and easily keep track of all moving parts with automated reporting

 

 

 

 

With examples and use cases from companies like Shell Oil, this webinar will be useful for everyone from the citizen user to a professional proficient in R and python.

Register

Upcoming Webinar – ThoughtSpot: Stop building reports/dashboards and just “search” for answers!

Thursday, April 26th at 12:00 pm CT

Capitalize Analytics works with dozens of vendors to solve data challenges. Rarely are we excited or blown away by a technology. ThoughtSpot will be the answer to a lot of our clients’ data/reporting challenges.

Tableau, Qlik, Cognos, BusinessObjects, etc. are all great tools in the correct hands. The problem is that even the simplest tools are too complex for average users. ThoughtSpot changes this by allowing end users to “search” for answers just like they would in Google, Amazon, etc.

With ThoughtSpot you just search for “Sales merchant product weekly last 3 months” and immediately get the answer!

Gartner has recognized ThoughtSpot as an analytics visionary and the biggest mover on the Magic Quadrant. 

 

ThoughtSpot doesn’t replace your traditional BI strategy. Instead, it gives a new, non-technical audience the ability to truly “self-serve” for the first time.

 

Join Capitalize Analytics  to see how ThoughtSpot will have your company “searching” for answers more quickly than any other tool available!

 

Can’t make it? Still register and we’ll send you the recording!

 

Register

INDEX and MATCH Functions – Multiple Criteria VLOOKUPs

In an ideal world, we would always receive the data we need in exactly the format we need to have it in. However, in the real world, analysts are often given dumps of data from various sources that need to be consolidated into a useful report. Many times, this consolidation is done in Excel using the VLOOKUP function. A limit of the VLOOKUP function, however, is that it can only be used to join data on a single criterion. A less commonly used way to join the data together is through a combination of the INDEX AND MATCH functions. The INDEX/MATCH method can be used when we need to join the data on single or multiple criteria, making it more flexible than the VLOOKUP function.

Following is a case where we need to join data on multiple criteria. Suppose our company is a Natural Gas producer and uses its budgeted Ethane (C2) and Propane (C3) volumes to hedge against price fluctuations. We are asked to build a report showing the Actuals-to-Budget variance of our sales volumes by meter by month for our six (6) highest volume meters. We have built the structure of our report and updated the Budgeted volumes as well as the formulas to calculate the variances. All that we need to complete the report are the Actual volumes.

 Actuals to Budget Variance Report:

Actuals Data:

For the Actuals Data, we receive a dump of all NGL sales for all the company’s meters and all months.

To bring the Actuals data accurately into our report, we need to join the data on not only the Meter #, but also on the Product and the Month. A VLOOKUP would be able to join the data on any one of these criteria, but not all three. To join on all three criteria, we can use the Index/Match option.

First, let’s break down the Index function and Match functions separately.

 INDEX Function (array, row_num, [column_num]):

With the INDEX Function, we specify a range of data and a row and column number within that range (where the upper left cell of the range is in row 1 and column 1). The INDEX function will return the cell where the specified row and column numbers intersect.  For example, below we have set our range to be A1:E541 which is the entire set of our data dump. We then specify it to return the value in Row 3, Column 5. This value is equal to 530.

The INDEX function gives us a way to return an individual cell from a range. To make this function more valuable though, we need a way to find a specific row of data based on the data required in our report. To accomplish this, we can use the MATCH function.

 MATCH Function (lookup_value, lookup_array, [match_type]}:

The MATCH function allows us to enter the value (lookup_value) we want to join and the range (lookup_array) where we want to find that value. For example, below we told the MATCH function to find Meter # 12354 (lookup_value) within range A1:A541 (lookup_array). The function returns number 3 which is the row within the range where Meter # 12354 is first encountered.

Rather than returning the row number with the first occurrence of Meter #12354, suppose we want to return the row for Meter #12354 with product C3 in Production Date 03/01/2017. In other words, we need to return the row number base on multiple criteria.

We can accomplish this by using an Array formula. Specifically, we can use the “&” symbol to join multiple lookup_values and multiple lookup_arrays. One important caveat is that any time you create or edit an Array formula, you must type Ctlr+Shift+Enter in the formula bar to evaluate it. After typing Ctrl+Shift+Enter, curly brackets will appear on the outside of your formula. If you only type Enter after editing an Array formula, a #VALUE! Error will be returned.

Below is a screenshot showing the use of an Array Match formula to identify the desired row based on Multiple Criteria. Note the curly brackets on the outside indicating that it is an Array formula.

The formula returned row number 138. When we look, we can see that row 138 does in fact match all 3 of our criteria: Meter #=12354, Production Date=3/1/2017, and Product=C3.

Populating Actuals in our Report (INDEX/MATCH combination):

Now that we have a way to return a value from a row/column intersection (INDEX) and a way to identify specific rows of data (MATCH), we can combine the two functions to bring the Actuals data accurately into our report. Since our goal is to return the data within certain cells, we start with the INDEX function and specify the column array where the data we want to return is located. In this case, the data we want to return is Mbbls (thousands of barrels), and these values are in range AA4:AA544.

Next, we need to specify the row number for the INDEX function. Instead of specifying a specific number, we can nest an Array MATCH function inside the INDEX function to identify the row number based on our 3 criteria: Meter #, Product, and Month.

We use a combination of Relative and Absolute references ($ symbol) to make sure our formulas will reference the correct cells once we copy them.

Since we only have one column in the Array for our INDEX function, specifying the column number is optional. Here we will explicitly specify it equal to number 1.

We have entered all the arguments for the INDEX function, so we close the final parentheses and press Ctrl+Shift+Enter to evaluate the Array formula. We know we did this correctly because the curly braces appear around our formula.

The formula returned 2,422 MBBLs of C2 for Meter #12347 in January. Searching through our raw data, we can verify that the formula did return the desired value.

We can now simply copy our formulas through the rest of the Actuals columns, and our Actuals-to-Budget Variance report will be complete!

We see that through March, we have exceeded our C2 budgeted volumes on our highest volume meters, but we have fallen short on C3 volumes. The largest drivers of these variances are Meter 3 and Meter 6.

In conclusion, the major advantage of the INDEX/MATCH method is the ability to lookup values on multiple criteria. Using this method can be very helpful for quickly bringing raw data into monthly or ad hoc reports that have been built in Excel.

For additional information, please contact us at marketing@capitalizeconsulting.com!

Cognos Web Services: Direct Access to Report Results

Cognos Web Services (CWS) allows report authors to share a URL that provides direct access to report results. Traditionally, generating report results requires a user to log in to Cognos Connection, navigate through the UI to the desired report, and select prompts and run options each time new results are needed. Utilizing CWS for a report streamlines the user experience to using a URL and getting up-to-date results without the user knowing or accessing Cognos.

CWS URLs can be modified to perform several different tasks including:

  • specifying filters for a report
  • accessing previous versions of a report
  • changing the output data format
  • selecting a specific subsection of the report to display

Because every parameter required to get reporting results is contained within a single URL, it is easier to interface Cognos reports with other software options like Alteryx and Pentaho. Modifying a report for use with CWS is simple and many existing reports won’t require any modification.

Implementation Example:
A company needs to automate a verification process that compares the reports of two different data software packages. They decide to outsource this process to a contractor and would like to share to-the-minute report results without the contractor needing access to the Cognos environment. Using CWS, the contractor only needs the connection address and the relevant report IDs to filter and download data for the comparison.

URL Format:
http://webservername:portnumber/ibmcognos/cgi-bin/cognos.cgi/rds/resource_type/source_type/source_id?option1=val1&option2=val2…

  • webservername:portnumber: Local Cognos Connection Address
  • resource_type: Type of result desired, e.g. outputFormats, promptDescriptions
  • source_type: Specifies source such as path or report
  • source_id: Report-specific unique identifier

Once the source is specified, the user can alter the output with parameters like:


User credentials can be saved in-browser, specified for each URL, using a credentials prompt option.

Examples:
http://test.server/ibmcognos/cgi-bin/cognos.cgi/rds/outputFormat/report/
i4FAD7993E84645DEA0B8C2C555E5E5EF/CSV&selection=UPlist&p_usage=sale


http://test.server/ibmcognos/cgi-bin/cognos.cgi/rds/outputFormat/report/
i4FAD7993E84645DEA0B8C2C555E5E5EF/XML&p_product=res

For additional information, please contact us at marketing@capitalizeconsulting.com!

 

Contact us to join our Blog!

Powerful Date and Time Functions in Excel

Another installment of our educational blog series is here! If you have suggestions for future blog topics, we want to hear them. Email us at marketing@capitalizeconsulting.com with your ideas!

Excel has numerous date and time functions that are very powerful in manipulating and deriving dates and times. In these examples, we are going to use one of them – WEEKDAY – as well as supporting numerical functions – FLOOR and TEXT.

In this first example, I have a goal of walking an average of 10,000 steps a day for the next 10 weeks, and would like to track my progress via a scorecard. I have a spreadsheet where I will log my steps daily and need to report weekly whether my average step count meets my goal of 10,000 steps.

I have built a simple data tracker to track my daily steps:

My next step is to be able to report weekly to see if I am meeting my goal. To do so, I will use the date function DAYS, along with supporting function FLOOR, in order to derive the week number (1, 2, 3, … 10). I want to determine what week each date falls into, relative to my start date of 9/10/2017. Looking at the data above, 9/10 – 9/16 would fall into Week 1, 9/17 – 9/23 into Week 2, and so on, but I want Excel to determine the week number for me.

I start by determining the number of days that have elapsed since the start of my goal, using the DAYS function. The DAYS function takes two arguments – end date and start date – and returns the number of days between those two dates. For end date, I pass the date cell for that row, and for start date, I pass an absolute reference of the start date of my goal (9/10/2017) in this case. As shown in the below screenshot, I have now calculated the Days from the Start using the formula “=DAYS(A2,$A$2)”, entered once, and dragged to the other cells:

Now that I have Days from the Start, I can derive the week that each date falls in using the FLOOR function. In our example, those dates less than 7 days from the start are in Week 1, greater than 7 but less than 14 days are in Week 2, and so on. In other words, if we ignore the remainder, always rounding down, then the formula of (Days from the Start / 7 + 1) would give you the Week. The FLOOR function does just that. It takes whatever number is given and rounds that down to the nearest specified multiple. FLOOR takes two arguments – number and multiple. The function then rounds the number down to the nearest multiple passed.

See below for a few examples of the result FLOOR would output, given the inputs of number and multiple specified:

In our case, we need to derive the week from the given date, knowing we have a start date of 9/10. We can do that using FLOOR, one of two ways.

  1. “=FLOOR(D2/7,1)+1” – Take the number of days and divide by 7 to derive the fraction of week “completed,” adding 1 so that we start with week 1 rather than week 0.
  2. “=FLOOR(D2,7)/7+1” – Take the number of days and round to the nearest multiple of 7 (0, 7, 14, etc.). Divide that result by 7 to convert to Week, and again add 1 so that we start with week 1 rather than week 2.

Either formula will produce the same result, shown below:

Armed with the derived Week, we can now create a quick Pivot Table showing me the average steps per week:

Now that I have my average steps by week, I would like to dive in deeper to see if there are any trends in my walking pattern by day of the week. For example, do I walk more on the weekends when I am not as busy with work?

To glean any patterns associated with day of the week, I first need to determine each day of the week. To do so, I can use the WEEKDAY function. The WEEKDAY function returns the day of the week, in numeric value, of a date passed. The function has two parameters – the date passed, which is required, and an optional parameter called “return type” which determines what days the numbers 1 through 7 correspond to. By default, a return type of 1 is used, meaning a value of 1 is Sunday and a value of 7 is Saturday. If you want to use the default value, you do not need to enter an argument for return type. See below for the full list of return types:

Excel prompts you for return type when entering a new formula, so you won’t need to memorize the return types associated with each configuration:

In my case, the default return type was all I needed, so my formula read, “=WEEKDAY(A2)”, with results shown below:

While I now have what I need to report steps by day of the week, I wanted my report to actually show day of the week, rather than a number. To do so, I used the TEXT function. The TEXT function is used to convert a numeric or date value into text, based on certain criteria. The function takes two arguments – the value to be converted, and a format text parameter which defines the formatting criteria to be used. The format text parameter, in the context of date, has the following definitions:

Using the TEXT function, I am able to derive the day of the week with the following formula: “=TEXT(B2,”dddd”)”. Or, if we wanted to derive in one step, we could via the formula: “=TEXT(WEEKDAY(A2),”dddd”)”. Final data for use in our analysis is:

Now that we have our steps by day of the week, we can move forward with analyzing our daily patterns. There are many examples to build, but I have chosen two.

  1. A pivot table that shows my average weekly step count, with ability to drill down to see steps by day of the week:
  2. A chart that shows, by day, steps by weekFor additional information, please contact us at marketing@capitalizeconsulting.com!

Upcoming Live Webinar! Five Cognos Tips Ever Banner Client Needs to Know

Five Cognos Tips Ever Banner Client Needs to Know

Tuesday, May 15th @ 12 pm CT

Does your institution use Banner? Do you have a love/hate relationship with Cognos? If you answered yes to these questions, you are not alone and this is the webinar for you!

Capitalize Analytics is here to help you get the most out of Cognos. In this webinar we will show you five Cognos tips every Banner client needs to know!

Attend our webinar and let us show you how to you can take full advantage of Cognos for Banner!

Register

Can’t make it? Still register and we’ll send you the recording!

Contact us to join our blog!