₦airaland Forum

WhatsApp
Chrome
Spreadshet

airsaylongcome:
50% profit stolen! And that's gross profit o. You haven't removed selling expenses like staff salary and "rent" for the space you are operating from.

Me I've always believed in computerising any business no matter the size. I had a a friend in University years back. He was a hot head like me. Hotter headed sef.

Way back before data science became a trend he said to me: "Santos (that was my nickname back in the day) , the witchcraft is in the data. You go see other businesses dey accuse successful business say na witchcraft dem dey use. Whereas that successful business na sales data dem dey look to make forecasts and set targets"

This was back in 2009. Man ran into trouble again at that Uni (he had previously had trouble at his first Uni for questioning a lecturers solution to a question). Managed to get his transcript just before school Disciplinary Committee suspended him for questioning Dean of Student Affairs. Got admitted into a UK uni and is now doing very well in the UK.

The witchcraft is in the data. That stuck with me ever since.

I'm happy you have been able to identify that your business is being attacked by your employees. Again if I was a consultant, I would advise you to set daily targets for each of your outlets. Anything over the targets, have an agreement to share the profit in some ratio with the staff.

Note that your staff may have their own POS terminals at your outlets running it as their "side hustle". That's something you have to consider and have an antidote for. I have previously consulted for a bar/nightclub and business owner was shocked to find out that his staff were selling their own inventory of drinks inside his club! He was only able to find that out when I suggested that he should reconcile sales based on category of payments. All cash sales should be reconciled separately from all POS sales.

Apologies for replying late boss. Have been busy offline.

But wow, you just opened my eyes to something I never thought of. What if they have their separate termina for side hustle since now they can't easily take money from the business anymore
I will consider placing them on target and come with a sharing formula once they go past the target

Boss Airsaylongcome good morning sir. I just want to say thank you for all the help, the accuracy of this solution is 99%.

Do you know that just yesterday when I applied it, I discovered three of my sales rep were stealing from the profit?

one took 50% of the profit the other took 1k and another staff took 1k. I was speechless.
it left me heartbroken, coz the one that took 50% happens to be someone I could swear with my life that she is 100% honest. she could not say anything this morning rather than cry and ask for forgiveness.

airsaylongcome:
https://www.ablebits.com/office-addins-blog/2014/06/10/excel-conditional-formatting-formulas/#formula-text-values

If you still have issues let me know

Good afternoon boss. Thanks so much for this. This will go a long way to broaden my knowledge of excel. Thank you sir.

airsaylongcome:
So the method I've found just highlights the cells with Failed, not the entire row. Maybe someone reading this thread has an idea how to do that?

Good morning boss. How do the highlighting pls?

airsaylongcome:
Let's link up on this around 8pm tonight. Right now I dey "lick" some bottles of stout so my brain don shift small

But it's straightforward stuff. Something called conditional formatting. But we go link up. Make I finish my "licking" first

Hahahahaha Alright boss thanks.

airsaylongcome:
About this, I'm almost certain that Agency Banking is here to stay. During the height of Corona Lock down, I was talking with a Ugandan friend living in Sierra Leone. He mentioned this Agency Banking stuff and how a lot of people are going into it there. At the time I was evaluating a Core Banking software and he had adapted the software to be able to do agency banking. He spent money to buy these POS machines and ATM card writer to test his changes. And when he did a demo for me it worked well. But me I was just a techie and did not see the value of what he was talking about. In fact to be frank, I did not think Nigerians would accept Agency Banking or that people would want to start up small businesses as Agency Banks. I was damned wrong.

I don't think a future government will ban it. MTN is diving into this big time. In Nigeria, Finance is a big driver of Tech innovations so I expect it to be available for long. Whether it will be sustainable long term is a different matter because small businesses like yours will start competing by reducing their charges

It's safe to make a long term plan using the agency banking as source of livelihood/Means of survival then

Sir Airsaylongcome boss good evening sorry I don come again, pls don't get tired of me.

Pls, how do I make the entire row in which a failed transaction appears to have its text color as red?

I have attached an image below. these were done manually (I changed their color one at a time, it could be a whole lot of work if one is dealing with a huge record.

airsaylongcome:
Lol! Even the guys I linked up with the Data giveaway you did, neither of them know me! You go shock. Na here and one other platform na hin we dey "meet". I kind of like it that way. No real reason.
I'm a bit active on the Programming section here so you go dey see my moniker well well

Wow. Alright sir. I noticed that you frequent this section. Thank you so much for the help. Once I start my Program God's willing with HiiT, I'd still contact you for advice and solutions to any excel that is beyond me sir.

Thank you so much for what you did for me so far.

airsaylongcome:
Interesting stuff. I'm really interested in these analysis.

Quick one. Don't put the date like you have put in that spreadsheet. It can make analysis difficult in future. Instead, Create a column called Date and enter the Date there for each transaction/row.

oh wow, I just learned this. let me quickly do that. wow thanks so much, sir.

airsaylongcome:
Na to find correct location. And know say Lagos na jungle. I know location wey like Four Agency Banks dey around within 2 metres of each other

lolz it is everywhere. even here POS are just scattered around like I said boss, na game of numbers remain wey all of us wey dey do the business dey do now

airsaylongcome:
Sorry to be putting your business analytics out here o, but the Gross profit margins I'm looking at, are phenomenal! Nobi here at all at all!

just like you helped me without knowing me, I do hope others will see this and learn from it.

thanks one more time, God bless you abundantly sir.


you sha no want make I link up with you sir you may have your reasons and I won't question that, I will keep your moniker at heart and will always seek for your help sir. also when I enter Lagos for my training, will notify you sir. Thank you very much sir

airsaylongcome:
In D2,
=IF(OR(C2="Failed",A2="Airtime",0,IF(C2="Bill_Payment",IF(B2<=10000,100,200),IF(C2="Card_Transfer",ROUNDUP(IMDIV(B2,10000),0)*200,ROUNDUP(IMDIV(B2,10000),0)*100)))

wow!!! and it worked just like magic SIR!!!
So this particular location made a profit of 2,253 naira on the first of JUNE 2022

airsaylongcome:
So I'm looking at a withdrawal of ₦25k. Why is the charge you put ₦100 when given your description of ₦100 for every ₦10k there's a ₦100 charge. Should it not be
₦10,000 - ₦100
₦10,000 - ₦100
₦ 5,000 - ₦100

Should give
₦25,000 - ₦300

Or am I wrong?

You are absolutely correct sir!!!

in fact, N20,001 is N300 while N20,000 is N200

airsaylongcome:
(In other news, we dey roast for this Lagos. People charge ₦100 for every ₦5k here)

na hin be say first thing I'd do once I get to Lagos by November na to open POS outlet before heading to HiiT be that

airsaylongcome:
Looking at the spreadsheet, are you saying if I withdraw say ₦8,500 your charge is ₦100?

YES. Even if you withdraw 10,000, we will take 100 naira from you

Sir Airsaylongcome we do not charge for failed transaction

airsaylongcome:
Sure. Do you charge only for withdrawals? Are you here any charges for the other transaction types?

For withdrawal I know the bands are

₦1.00-₦5,000.99= ₦100.00
₦5,001-₦10,000.99 = ₦200.00

Are there any other bands?

let me put them in an excel sheet
Sir airsaylongcome I have MODIFIED it and added an image of what the charges for each transaction type looks like.

airsaylongcome:
Here you go

=IF(C2="Failed",0,IF(A2="Card_Transfer",IF(B2<19999.99,20+(B2*0.05),120),IF(A2="Withdrawal",IF(B2<19999.99,B2*0.05,100),IF(A2="Transfer",20,0))))

Type this in E2 and fill down.

In this formula, I have assumed the following

Column A: Transaction Type
Column B: Amount
Column C: Status
Column D: Charges
Column E: POS Charges

thanks so much, boss.
coming to column D (the column that contains the charges I collect from customers, can we do an autofill for it? in the sense that if I put amount withdrawn or deposit, or card_transfer, or bill-payment, or airtime it automatically fills the column D (which is the charges I collect from customers)

For example, my charge for withdrawal of 5000 is 100. if I put 5000 can it automatically fill the charges column with 100 without me having to type it there myself? could that be possibly done?

airsaylongcome:
Happy to hear that.

But Ọmọ...is this the profit margin in Agency Banking!

Your lowest profit is like 50%. I'm even seeing 95% gross profit!

lols there is money in the business to be frank it is more of a game of numbers. if you don't make plenty of transactions daily, you may end up going home with just 2k

but here is my strategy, open as many outlets as my small money can (it is one of the reasons why I am looking for the easiest method of record-keeping) I currently have 5 locations and make a minimum of 20k daily from Monday to Saturday. somedays we make up to 50k from all locations combined and I don't have to be at all the locations. I just put people there to run it for me while I stay back at home and record everything dey do and we meet in the evening to do check and balance.

my only problem is it doesn't have a future. what happens if tomorrow another government comes and abolishes it? what will I do? I don't have any skills. By November, I will turn 30, I will let my fiance manage the business while I move to Lagos to enroll in HiTT so I can study data analysis.

I knew I was not wrong when I referred to you as a wizard.
I don't know if I can ever stop thanking you or if I even deserve this from you, but I want to say thank you so much for this, I will never forget this sir. I really appreciate and I wish you would agree to be my mentor.

omg it worked

this is what the formula looks like when I opened it

airsaylongcome:
Here you go

=IF(C2="Failed",0,IF(A2="Card_Transfer",IF(B2<19999.99,20+(B2*0.05),120),IF(A2="Withdrawal",IF(B2<19999.99,B2*0.05,100),IF(A2="Transfer",20,0))))

Type this in E2 and fill down.

In this formula, I have assumed the following

Column A: Transaction Type
Column B: Amount
Column C: Status
Column D: Charges
Column E: POS Charges

thanks so much sir.
let me post an image of the result, some of the result I think need to be checked sir

airsaylongcome:
There is a quick way to do it. Copy the entire Column A to a new worksheet. In that new Worksheet, select the entire column you copied then click on Data Menu (File, Home, Insert, Page Layout, Formulas, Data, Review, View, Help)

Click on Remove Duplicates. It will leave only Unique entries.

it is here sir

airsaylongcome:
Also for column C (Status) are the values either completed or failed only?

yes sir, it can either be completed or failed

airsaylongcome:
Are there any other options there?

No sir

airsaylongcome:
Is there a charge when the transaction fails?

No charges on failed transactions sir.

airsaylongcome:
So first stuff, can you list out all possible options for Transaction type. (Transfer, Withdrawal, Fund Wallet, etc).

alright sir

Modified
Transaction types are
TRANSFER
WITHDRAWAL
AIRTIME
FUND_WALLET
FUND_TRANSFER
CARD_TRANSFER
BILL_PAYMENT

airsaylongcome:
Give me a bit. Kind of busy now but I will come around to this later today. Also anyone who wants to contribute a solution is welcome to. We rise by lifting others.

So first stuff, can you list out all possible options for Transaction type. (Transfer, Withdrawal, Fund Wallet, etc).

alright sir, thank you so much for your response, I'd humbly and patiently wait till you are free sir. thank you

Sir, also, in COLUMN A, there are more than two variables. there is withdrawal , transfer, bill_payment, Card_Transfer, Airtime and each variable has its own seprate charges, for instance, deposit is 20 naira for any amount, withdrawal is 0.5% for any amount below 20,000 while 100 naira fro anyamount above 20,000, bill payment is free of charge, airtime is free of charge too but card_transfer is charges for withdrawal + charges for deposit example, let's say a customer comes with his atm card and asks me to do a card transfer of 10000, POS charges will be 10000*0.5/100 which is charges for withdrawal + 20 naira which is charges for deposit

airsaylongcome:
Assume amount withdrawn or deposited is in A2, transaction type (Withdrawal or Deposit) is in B2 and charge is in C2

In C2 type the following
=IF(B2="Deposit", 20,IF(A2<=19999.99,0.005*A2,100))

Boss Good morning Sir. I wish words can explain how happy I was last night even till this morning (I was even telling Mr. Osasere this morning).
it was even on my WhatsApp status and I received a lot of congratulatory messages from my friends on WhatsApp yesterday after they saw my status about how you solved it within seconds because most of them are aware of the problem since 2021 that I have been searching for solution. Honestly, If I say I am not glad to have met known you on this thread yesterday, I will obviously be an ingrate thanks so much sir, I really appreciate it. However regarding what you said yesterday.........

airsaylongcome:
Please note that this function assumes that there are only two possible transaction types in B2, Withdrawal or Deposit. If there are other possible options (such as "Failed" , this function will not work correctly. If you can share an example of what your spreadsheet looks like, I can provide you with a more accurate function Let me know if you still need assistance.

it didn't execute it perfectly due to the presence of a column for "Failed and completed" of every transaction carried out on the POS. I will attach an image of the excel page below so you could see it, sir.

MirthOsas:
Right away boss.

Good morning sir. Kindly make available your number on my WhatsApp.
Sorry I slept of last night

airsaylongcome:
Next thing for you would be to learn Pivot Tables in excel so you can do analysis across your various outlets and various withdrawal amounts. Looking at your figures, if I was a consultant advising you I would say you need to encourage patrons to pay the charges by cash rather than including it in the amount withdrawn. Also need to focus on those that withdraw small amounts while paying cash for the charges.

Pls boss, I have a lot to learn from you. I beg you do not deny me that chance pls

remi1444:
I sent a message on S. I wish you could check it at your convenient time.

Boss check my signature