segunda-feira, 30 de março de 2020

Use Google Sheet and go on!

In
these days of working at home, the information piece has become more critical. Any type of information, usually stored in folders on-site servers, which is necessary to share. In this case, the need was to share excel files with budget information, where they should be read by a PowerBI platform.

I didn’t want to use the VPN (Virtual Private Network), in order not to be restricted to the Firewall’s internal access and permissions, but I shouldn’t expose the information either. The information would have to be kept private. One of the ideas for remote connection to the file was to put it in Google’s cloud, Drive.
Two problems arose with this decision:
1-PowerBI have no native connection to Google Sheet.
2- The connection to Excel (xlsx) saved in the Drive has connection errors.
One apart from this discussion, has a connection with so much platform in PowerBI, that it is hard to believe that there is no connection with Google Sheet. Market issues or lack of commercial ability to know that today Google applications are fighting on an equal footing in the market.
Studying the problem I found the workaround for this connection. 3 steps for this solution:
1-First it will be better to transform your excel sheet into a Google Sheet. It’s automatic using Google conversion.


2-Share your Google Sheet by obtaining the shareable link.

3-Define your PowerBI connection link as Web, and use the URL link obtained in step 2.



Here we have a hint, with the relationship the URL. You need to change the spreadsheet URL to the following format: https://docs.google.com/spreadsheets/d/[id=spreadsheet] / export? format = xlsx & id = [spreadsheet id]

An example:


Your shared URL is: https://docs.google.com/spreadsheets/d/5aEqaRdzheBRpTez_Y0GBxBH2vKBIzT-jcmds2VJjbIs/edit?usp=sharing.

Look that the ID is only ‘5aEqaRdzheBRpTez_Y0GBxBH2vKBIzT-jcmds2VJjbIs’

After our modification the URL will be:

https://docs.google.com/spreadsheets/d/58-0c4aNM0i0npydD8VKZfinye-y6DH4WrbvCC-eLvrw/export?format=xlsx&id=58-0c4aNM0i0npydD8VKZfinye-y6DH4WrbvCC-eLvrw

So, put this URL on the Web connection in PowerBI.

Let’s go test:


1-First my original excel file, load on Drive:


2-Transforming excel on Google Sheet

3-Obtain the link to share and put it on the Configuration data source, type Web.



Now we can watch our data on PowerBI:

I tested the modification of some customer records in the Drive Google Sheet. At PowerBI I push the Refresh button and the update was immediate.

Conclusion


The use of multiple platforms and the knowledge of their basic skills can be a strong ally in decision making, in record time.
In the case above, the need for an ingenious solution that solved a basic and urgent problem led to the complementary use of two products from competing companies. This use solved the proposed problem very well.

References:

domingo, 29 de março de 2020

SAP Innovation Awards 2020 winners!




We are the winners!


Congratulations to the

SAP Innovation Awards 2020 winners!

The judges have made their decision. Congratulations to winners that made the cut for their brilliant and compelling examples of innovation.




Submission details:






SAP Winners Innovation Awards
https://www.sap.com/idea-place/sap-innovation-awards/2020-entries.winners.html
https://www.sap.com/idea-place/sap-innovation-awards/submission-details-2020.html?idea_id=1429
https://www.sap.com/bin/sapdxc/inm/attachment.7761/pitch-deck.pdf

Foi notícia na Aço Cearense
http://www.grupoacocearense.com.br/imprensa/noticias/gac-e-indicado-ao-premio-de-inovacao-sap




Reporting system to OBZ Budget



Reporting system to OBZ Budget

This is a development of a reporting system for the zero base budget, using PowerBI.

To extract the data, run a report from SAP tables COSP and COSS (that have the accounting costs by cost center and account).
  • We have a SAP SET OBZ_TIPO_DESPESA to classify the accounts the Type of Expense
  • We have a SAP SET GAC_P% sets have the accounts for spending packages

Excel File layout to import into PowerBI


  • Empresa - Company in text field
  • Exercício - Year in text field (YYYY)
  • Centro custo - Number of cost center
  • Denominação - Name of cost center
  • Data de lançamento - Date (the system makes a single date per month, on 01 / mm / yyyy)
  • Real - Actual month value
  • Plano - Planned month
  • Comparativo - Actual value of the month for the comparative year
  • Tipo Despesa - Expense type
  • Pacote - Spending Package
  • Nome 1 - Directors (Department)
  • Nome 2 - Management

What is OBZ - Zero Base Budget

Zero-based budgeting is one of the budget planning methodologies that is commonly known as OBZ, this tool requires managers to justify all of their budgeted expenses, rather than a more common approach of just requiring justification for incremental changes to the budget or actual results the previous year. Thus, a manager is theoretically assumed to have a zero expenditure baseline. OBZ is a plan made from scratch, with no background so it works, everything needs to be carefully budgeted and aligned.
In reality, a manager is assumed to have a minimum amount of funding for basic department operations, above which additional funding must be justified. The intention of the process is to continually focus the financing on key business objectives and to close or reduce any activity that is no longer related to those objectives.
The basic process flow under a zero-based budget is:
  • Identify strategic business objectives
  • Create and evaluate alternative methods to achieve each goal
  • Assess alternative funding levels, depending on the planned performance levels to establish priorities.
The concept of tiered expenses can also be used in reverse, where you outline the specific costs and capital investment that will be incurred if you add an additional service or function. Thus, management can make discrete determinations of the exact combination of incremental cost and service for its business. This process will usually result in at least a minimum service level, which establishes a cost base below which it is impossible for a company to go, along with several service gradations above the minimum.

References:

Some Screens






References:

My GitHub: https://github.com/MRobalinho/Reporting_OBZ
Doc. Reporting:   https://github.com/MRobalinho/Reporting_OBZ/blob/master/DB_OBZ_v09.pdf
http://im-inteligenciademercado.blogspot.com/2017/03/obz-orcamento-base-zero.html



terça-feira, 24 de março de 2020

Credit Score using Machine Learning

 The goal is to use machine learning to create a credit score for customers. This score gives the degree of confidence that the customer will meet the agreed payments. The higher the score, define the greater the probability of non-payment.

Multiple Linear Regression in Python with Scikit-Learn


We just performed linear regression in the above section involving two variables. Almost all the real-world problems that you are going to encounter will have more than two variables.



Linear regression involving multiple variables is called “multiple linear regression” or multivariate linear regression. The steps to perform multiple linear regression are almost similar to that of simple linear regression.

We will use customer information to generate a ‘trust’ score on the customer. The scoring formula can be adapted for each company according to its credit context. In this example, we are going to use the average number of days the customer is late, and the average billing amount for the past 2 years to calculate a score that combines the 2 information.

After calculating the score, we submit the information to a machine learning with Scikit-Learn, so that the system can predict new scores based on the learning information.

Our formula for Score calculation described on Score calculation.xlsx

Customer information is in the excel: Customers_CODE.XLSX


Customer company information:

  1. Customer from date
  2. State, Region, Postcode, Salesman, Main CNAE (type of company classification in Brazil)
  3. Highest Billing Date
  4. Maximum billing amount
  5. Last Date invoice issued
  6. Largest credit exposure date
  7. Highest credit exposure
  8. Average historical delay
  9. Average revenue last 48 months
  10. Amount payable Overdue
  11. Amount payable due
  12. Customer Last Order Date
  13. Date of this information

SERASA Information 

( Serasa it’s a company that sell’s information about other companies)
  1. Serasa Score
  2. Probability of not paying
  3. Last date non payment
  4. Amount of unpaid documents
  5. Value of unpaid documents
  6. Last date bad checks
  7. Amount of bad checks
  8. Last date protests
  9. Value protests
  10. Last date judicial actions
  11. Value judicial actions
  12. Last date overdue debts
  13. Value overdue debts

I create some Python code to read and clean data from my data set.

After we create a routine to read all records and calculate my score (newscore) using my definitions. I have an excel file with my definitions to calculate my score.


Applying Keras-Model to training and test the model. Create Train and Test datasets, 80% for Train and 20% to Test. In graphic mode, we can compare Train and Test results:


I need to predict some individual records, so, I made a python function to predict score:


Now testing, running to predict my record:
Using tkinter python library, we can create a screen with better visual:




Conclusion:

The creation of a score using the information known to a customer, can automate and make a credit system more reliable. This approach contributes to cooler and more reliable risk analysis, resulting only from market data and information, removing from the system the criteria of proximity to the client and the emotions that negotiation can generate.


References:


MY GITHUB

MY MEDIUM

sábado, 7 de março de 2020

PMP Certificate

PMP Certificate





About PMI Institute


SCRUM ALLIANCE CERTIFICATE


SCRUM ALLIANCE CERTIFICATE


About Scrum Alliance ... 

Scrum AllianceⓇ is a nonprofit organization that is guiding and inspiring individuals, leaders, and organizations with agile practices, principles, and values to help create workplaces that are joyful, prosperous, and sustainable.



Deep Leaning Applications Course


Deep Leaning Applications Course

Held at FBUni in Fortaleza, Ceará, Brazil, on June 23 and 30, 2018


CERTIFIED MACHINE LEARNING APPLICATIONS COURSE

CERTIFIED MACHINE LEARNING APPLICATIONS COURSE

Centro Universitário Farias Brito, in Fortaleza, Ceará, Brazil, in 2018


PMI Congress 2019

PMI Congress 2019 in Fortaleza - Brazil

PMI-CE 11º Congresso Regional de Gestão, Projetos e Liderança | 18.09.2019





About PMI...go here.



Participation in the 3rd Engineering Doctoral Congress-DCE19

Participation in the 3rd Engineering Doctoral Congress-DCE19 in Faculdade de Engenharia from Porto University, in June 2019



SAP Inside Track Recife participation certificate

SAP Inside Track Recife participation certificate in February 2020



https://www.linkedin.com/feed/update/urn:li:activity:6639321410814693376/