Adham Ayman

Portfolio Adham Ayman Excel & Google Sheets Services Provider Hello Software Skills I’m Adham. Excel Skills Here’s the backstory… Driven by an early fascination with analytics, problem-solving and design, I found joy in the world of numbers and patterns from a young age. I found a unique way to blend my passion for design with the precision of data. Pivot Tables Data Visualization Power Query Data Cleaning KPIs Data Modeling Data Governance Dashboards Data Analyzing Debugging Data Reporting Example Case A retail Company in New Zealand for selling computers and video games that has branches in some locations worldwide that needs to sort and refine and analyze their sales and finance, marketing and advertising and managerial data according to specific criteria and highlights. Firstly, preparing the data via sorting, cleaning, quality checking and modeling and determine what preliminary insights that can be pulled out and what insights that will benefit or most importantly directly asked from the client. Secondly, analyzing the sales, marketing, advertising, managerial data via any calculations needed or tables needed to be constructed or futuristic insights or comparisons needed to be done and visualize what obtained via various charts and composite charts. Thirdly, organizing the highlights whether charts, visualizations, numbers matter and KPIs in a dashboard or scorecards in order to show the big picture and conclude decisions and finally filing the data. Data Preparation Cleaning data related of supply chain and obtain preliminary insights like the frequency and share of each order. Data Preparation Using power query to import sales data (and other files) in order to prepare the data for quantity and date (monthly and quarterly) and to calculate the revenue and cost of goods. Resulting in new tables ready to establish relationships and further refinement. Data Preparation Establishing relational data source from various tables. This will help in further effective visualizing of the data. Data Analyzing Starting by dynamically analyzing the sales performance of the company: - Sales for every type of products - Sales through the years - Sales through the month - Sales by salesperson Using slicers, data validation lists and option buttons. Data Analyzing Dynamically highlighting the min and max sales through the year or the criteria of interest. Data Analyzing Dynamically highlighting the range of interest with help of lists to show the price changes through the year. Data Analyzing Forecasting Prices and sales with various probabilities (worst case, base case, best case and any in between) - Straight-line forecasting (Chosen for this case) - Moving Averages - Linear regression This will help later strengthen the establishment of sensitivity analysis and risk matrices and price volume mix analysis. Data Analyzing Dynamically comparing the actual sales performance with the forecast through the year as the sales data that are under the forecast are converted to red dots. Data Analyzing Calculating monthly profit margin and comparing them dynamically to the target to see if they achieve (in green) or not (in red). Data Analyzing Using “measure” and “KPIs” to determine which salesperson could achieve the specified sales conversion Creating a customized chart with moving indicator to indicate the region entered performance is within. Data Analyzing Checking the financial health of the company with the cash flow through the company and how it moves through the year. Similarly for quick ratio, acid test ratio, financial modeling, EBIT margin and inventory turnover. Data Analyzing Geographically analyzing the sold items per region whether using bubble chart or maps. Data Analyzing Visualizing the share of item sold through time. Visualizing the categories and the types and amounts of items in the inventory. Data Analyzing Dynamically comparing between employees salaries with the ability of increase the number of employees in the chart with scroll bar. Data Analyzing Creating a dynamic simple timeline for tracking the tasks for the company. Data Analyzing Increasing the complexity of the timeline to be able to track the progress of each task vs what achieved and what actually planned. Data Analyzing Increasing the complexity even more using DAX and conditional formatting and adding slicers and timeline to choose specific range and show which task is assigned to whom and dividing the tasks per department. Debugging While analyzing the data, errors can appear all the time. Solving them will not be an issue. Dashboards This dashboard reviews the sales behavior through 2 years along side with the quantity sold in each month for various items. It presents also each item share for generating these sales and which customer is contributing the most. It celebrates the best salesperson for the year and the contribution. Also it highlights some KPIs like the productivity, market share and SR headouts for each year. And finally which employee that cause the highest sales in each year. Dashboards This dashboard reviews the sales behavior through 2 years along side with the quantity sold in each month for various items. It shows the item share, the customer contribution and the region. In addition to slicers for the salesperson, region, item sold and the year. Dashboards For 3 years, this dashboard provides a visual overview of your marketing programs and their effectiveness. It includes: Highlights for KPIs like ROIs to show the return of each marketing and advertising strategy on the investment. A flow chart that shows the different marketing programs you're running, and how many people each program or event has attracted over the past three years. You can use the slicer to see the results for each year. The chart also highlights the most successful program for each year. A sunburst chart that shows all of your marketing programs and events, categorized by type. The size of each slice of the pie chart represents the number of people who were attracted to that program or event. You can also see callouts with additional information about some of the programs. This dashboard can help you understand which marketing programs are most effective, and how you can improve your overall marketing strategy. Dashboards For 4 years, this dashboard is a brief to Income sources (highlighting the highest value) and their percentage of share for the total income. In addition to the income targeted and the percentage of the income achieved and the effect of the marketing strategies to the company and their share to the income. Along side a card in the bottom showing the taxes percentages and amounts to give insight of the deductions from the income achieved. Dashboards For 4 years, this dashboard is a brief to geographically analysis the sales through branches and the main store in New Zealand and the inter-communication between them all In addition to highlighting the highest sales for which country and their share and the percentage achieved from the target value. Thanks Let’s have a chat.