Tags: MIN

Analyse all possible combinations of cheques received and identify the combination which gives maximum benefit to the Customer

{0 Comments}

In a Stock Broking business (Firm), Customers deposit margin money through cheques. Although cheques are received from customers at branches, clearing (realization) of the same normally takes 3-4 days.  On one hand, the Firm wishes to allow trading to the customers against the deposited cheques from the day it receives the cheque, on other hand there is a risk associated with this  i.e cheque may get dishonored and the Firm may incur huge financial losses because of allowing customers to trade in anticipation of the cheque realization. Hence, there is a trade off between customer centricity & Financial Risk management.

The Firm may decide to take a calculated risk in order to retain its customer centric approach (it is a delightful experience for customers if they get trading benefit against the deposited cheque instantly before realization). The Firm may decide to give the benefit of cheque which are below a set threshold amount (say 50,000) instantly and for cheques which are above threshold amount the credit will be given only after realization. Sometimes there are  multiple unrealized cheques deposited by one client but the Firm needs to cap the total benefit of unrealized cheques to 50,000 (partial benefit against any cheque is not possible i.e. either a cheque will be considered in full or not considered) E.g. if a customer deposits 3 cheques of 40,000 each then also the total instant benefit against all unrealized cheques shall be capped at 50,000 - hence only one cheques of 40,000 will be credited to customers ledger before realization.

Now the question here is with respect to selection of cheques to give best results i.e. maximum total below 50,000.  Consider the following example:

Cheque1: 55,000
Cheque2: 44,000
Cheque3: 28,000
Cheque4: 7,000
Cheque5: 6,500
Cheque6: 4,000
Cheque7: 3,000
Cheque8: 500

The Firm wants to identify the "Combination of cheques" which gives the maximum benefit to the Customer subject to a limit of 50,000.  The result should be cheques 3-8.  They add up to 49,000.

Here's another example:

Cheque1: 55,000
Cheque2: 45,000
Cheque3: 25,000
Cheque4: 5,000

The result here should be cheque 2 and 4.  They add up to 50,000.

Here are my thoughts:

1. I have assumed that the total number of cheques received from customer will be capped to 8.  This limit can be relaxed but processing will then take time.

2. Create all possible combinations of cheques which can be credited.  So, since no partial credit is allowed, the cheque can either be accepted in full or be rejected.  Hence the possible values can be 2 i.e. 1 (Accepted) or 0 (Rejected).

3. So if there are 8 cheques received from a certain customer, then there will be 2^8 i.e. 256 possible combinations.  This can also be computed by using the =PERMUTATIONA(2,8) formula.  I'd like to than Saurabh Gupta for sharing the formula to generate all these combinations.

4. After creating all possible combinations, add the cheque amount for each combination.

5. Scan this total column and highlight the one which is the largest value <= the threshold value i.e. 50,000.

You may refer to my solution in the Solution worksheet of this workbook.

Compute month wise pending audits

{0 Comments}

Assume a three column dataset showing Audit ID, Date of receipt of audit mandate and Date of audit completion.  There are other columns as well but they are not important for our Analysis.  One may want to compute the following month wise:

1. Which (Audit ID) are the audits pending at the end of every month; and
2. When (Date of receipt of audit mandate) was the mandate for these pending audits received; and
3. Ageing of these pending audits i.e. this would be computed as the last date of the month less Date of receipt of audit mandate

Here's an example:

In January 2014, there are a total of 10 audits reports which were received (Filter "Date of receipt of audit mandate" column on January 2014).  Of these 10 audits, 4 were completed in January 2014 (Filter "Date of audit completion" column on January 2014) itself and therefore there are 6 pending audits.  To this figure of 6, we need to add the audits pending from previous months.  If one filters column "Date of receipt of audit mandate" column on Oct-Dec 2013 and "Date of audit completion" column on dates after January 2014, 8 rows will appear.  This means that there are 8 audits which were received before 1 January 2014 but were completed only after 31 January 2014.  So the total number of pending audits as at 31 January 2014 are 8+6=14.  This task needs to be carried out for all months.

You may refer to my solution in this workbook.

Story telling with Excel Power BI

{6 Comments}

With Power Business Intelligence (BI) tools of Excel 2013, one can metamorphose raw data and/or results of complex calculations into stunning and interactive visualizations.  Power View (one of the four components of Power BI) allows one to create a PPT like flow in Excel thus allowing one to weave a story.  To be able to interact with/create visualizations, you will need to install Microsoft Office Professional Plus 2013 (this version will already have two of the four components of Power BI - PowerPivot and Power View).  Additionally, you will have to install the following add-ins from Microsoft (the other two components of Power BI)

1. Power Query; and
2. Power Map

I have tried to showcase the prowess of Power BI tools of Excel 2013 in these two workbooks:

1. An overview of the BRIC Economies
2. Sales data analysis

Just in case you do not have Microsoft Office Professional Plus 2013, you can see the features of Power BI in this 10 minutes video of the first workbook - "An overview of the BRIC Economies".

Determine cumulative expenses per employee when per diem rates vary by block of dates

{8 Comments}

Assume per diem travel rates vary by block of dates (from and to).  So, assume the per diem rate for travel dates between 26/2/2013 and 28/2/2013 is Rs. 78,000/day.  Likewise, if a person travels between 1/3/2013 and 25/3/2013, the per diem rate applicable is Rs. 70,000/day.  With different travel dates (from and to) specified per traveller, the task is to determine total travel expenses per individual.

You may refer to my solution in this workbook.

Minimise the total number of inverters to be used for different electricity load factors

{0 Comments}

Assume there are two types of inverters - Inverter1 with a capacity of 15kW and Inverter2 with a capacity of 20kW.  For an electricity load factor of, say 60kW, the objective is to determine the minimum number of inverters to be used.  Therefore the number of inverters which can be used for a load factor of 60kW are:

Option1: All 4 of 15kW; or
Option2: All 3 of 20kW

Since the objective is to mimimise the number of inverts to be used, the result should be all 3 of 20kW.  To take another example, for an electricity load of, say 100kW, the number of inverters which can be used are:

Option1: All 5 of 20kW; or
Option2: 2 of 20kW and 4 of 15kW

The result should be Option1 because the number of total inverters used is minimised there i.e. 5 instead of 6 in Option2.

To acheive this via Excel formulas, try this

1. Enter 15 in cell C2 and 20 in cell C3
2. In range A8:A18, enter different load factors such as 45,50,60,100,75,220,240,90,120,160,275
3. Enter 15 in cell B7 and 20 in cell C7
4. In cell B8, enter the following array formula (Ctrl+Shift+Enter) and copy down till cell B18

=SUMPRODUCT(((INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,))=MIN(IF((INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,))>0,(INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,)))))*({0,1,2,3,4,5,6,7,8,9,10}))

5. In cell C8, enter the following array formula (Ctrl+Shift+Enter) and copy down till cell C18

=SUMPRODUCT(((INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,))=MIN(IF((INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,))>0,(INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,)))))*({0;1;2;3;4;5;6;7;8;9;10}))

For each load factor, the combination of inverters of each capacity will be the minimum number of inverters (of all possible combinations) that can be used.

The assumption in both formulas above is that no more than 10 inverters of each inverter type will ever be used i.e. no more than 10 inverters of 15kW and no more than 10 inverters of 20kW will be used.  This assumption can obviously be relaxed by making minor changes to both formulas.

Calculate turn around time excluding Sundays and public holidays

{85 Comments}

Assume a two column database showing starting date/time and ending data/time (Data/time stamp appear in a single cell).  Given a list of public holidays in a year and starting and ending work times, one may want to know the turn around time excluding Sundays and public holidays.

You may refer to my solution in this workbook.

Extract numeric data and dates from string

{12 Comments}

In column A, there are some alphanumeric entries.  Somewhere in the alphanumeric entries (beginning, middle or end) there are the following:

1. Dates
2. Numbers (without decimals)
3. Numbers (with decimals)

Furthermore, there may or may not be spaces between numbers/dates and text entries.  In Excel 2010 and prior versions, the only way to solve this problem would be write a formula or a VBA code.  In Excel 2013, one can solve this problem by using the "Flash Fill" feature.  Flash Fill is identifies patterns in your sample output data which you type (for a few entries) and suggests the output for the remaining cells of the range.

You may refer to my solution in this workbook.

Return closest numeric match

{2 Comments}

In range A2:A6, the numbers are 7.5, 2, -12, 11.2 and 8.  In cell B8, a user enters 8.  One may want to answer the following questions:

1. Return the closest number which is less than or equal to the number in cell B8
2. Return the closest number which is greater than or equal to the number in cell B8
3. Return the closest match

You may refer to my solution in this workbook.