Overview

Charlotte Cidery was founded by three friends who shared a passion for hard cider, and it now produces and directly sells cider to restaurants, bars, and caterers in the Charlotte area. Despite a slow start due to the Coronavirus pandemic, the company's business began to flourish as the city gradually reopened, and many of its original customers became loyal repeat buyers. In June 2020, Charlotte Cidery began full operations. The company is now contemplating extending its operations to other cities in the southeastern United States. However, the founders wish to ensure that the economics of their current operations are as promising as they expect before making any further bold business moves. The potential issue of unpaid invoices raised by Francine could hinder their expansion plans.

About the Dataset

The owner, Francine, has provided three files. The first file contains the most recent monthly report, which displays all the unpaid invoices as of June 30, 2021. The second file includes old monthly reports dating back to June 2020, and the final file contains customer contact information.

End User Perspective: Francine’s Role

Francine's responsibility is to handle customers who have not paid their bills, and the action she takes varies depending on how long the payment is overdue.

1. If the invoice is current (less than 30 days old), Francine encourages the customer to schedule a payment in advance, like how credit card users can set up "Auto Pay". This reduces the chances of delinquency.
2. For invoices that are due (30-59 days old), Francine sends a reminder email to prompt the customer to pay their bill as soon as possible.
3. For late invoices (60+ days old), Francine must take a more hands-on approach with the delinquent customer. She contacts them by phone to inquire about the unpaid bill and works with them to establish a repayment plan if they cannot pay immediately.

Challenge Prompt (Recommended Analysis)

The owners of Charlotte Cidery are concerned about a growing problem with collecting payments from customers.

1. They have requested help with analyzing this data in Power BI
2. Unpaid invoices on the monthly report have been increasing since the company started operations in June 2020. Some of the unpaid invoices are very old, while others are from recent orders.
3. The owners need assistance with determining the severity of the delinquent payment trend.
4. The owners want to visualize the historical trend and gain summary-level insights into the breakdown of unpaid invoices for the latest month (June 2021)
5. The report should be structured in a way that helps prioritize which unpaid invoices to address first, as chasing down delinquent customers is only one important task that needs to be tackled.

ETL and Data Modeling

The data set used in this analysis is derived from an Excel file that was loaded into Power BI and transformed using Power Query.

1. The first step involved identifying any issues with the data, such as missing values, duplicate entries, or incorrect data types.
2. Next, the data was filtered and sorted to remove any unwanted rows and columns and sort the remaining data as necessary.
3. In the third step, the data was formatted to ensure consistency, such as changing date formats, converting text to numbers, or converting numbers to dates.
4. To clean up text and make it more consistent, functions such as Trim, Clean, and Replace Values were used in the fourth step.
5. The fifth step involved using the Group By and Aggregate functions to group the data by one or more columns and aggregate it as needed, such as finding the sum, average, or maximum of a particular column.
6. Finally, the data was validated to ensure that it met the required quality standards and was ready for analysis.

Insights

1. Despite a substantial increase in sales from June 2020 to June 2021, the number of unpaid invoices has also significantly increased. In fact, the monetary value of unpaid invoices for June 2021 exceeded the sales for that month.
2. The data viz of the unpaid monthly report for June 2021 enables the user to analyze the amount of money owed in unpaid invoices, the average number of days the invoices are past due, and the number of customers who need to make payment. The unpaid invoices are categorized into three groups: current, due, and late.
3. In the customer information dashboard, the unpaid invoices have been separated into three categories: current, due, and late. This provides the owners of Charlotte Cidery with essential information to identify the business, contact name, the amount owed, the order ID, the date of the last payment reminder, days outstanding, and if a payment has been scheduled.
4. Armed with this necessary information, the owners of Charlotte Cidery are now better equipped to address the issue of growing unpaid invoices.