Background:

The Office Supply dataset contains comprehensive information about sales transactions, including product details, sales quantities, prices, discounts, and geographical information. By analyzing this dataset, we can uncover valuable insights into the financial performance of different products, segments, and regions.

Goals:

Calculate financial metrics such as gross sales, discount, revenue, profit before tax, profit margin, tax on profit, and profit after tax.

Provide a report overview by segment, state, and product category to understand performance patterns.

Analyze the sales data for the period between January and December 2014 to determine growth or decline in product lines.

Compare the financial figures for 2013 Q4 and 2014 Q4 to assess year-on-year results at different levels such as product category and state.

Identify the key products driving the overall performance.

Identify any underperforming product subcategories that should be discontinued based on poor outcomes.

Determine the products that incur the highest tax liabilities overall, by state, and by segment.

Methodology:

Calculate Financial Metrics:

a. Gross Sales = Sum of the sales amounts for all transactions.

b. Discount = Sum of the discount amounts for all transactions.

c. Revenue = Gross Sales - Discount.

d. Profit before Tax = Revenue - (Cost of Goods Sold + Operating Expenses).

e. Profit Margin = (Profit before Tax / Revenue) * 100.

f. Tax on Profit = Profit before Tax * Tax Rate.

g. Profit after Tax = Profit before Tax - Tax on Profit.

Report Overview:

a. Segment: Calculate and present key financial metrics by segment (e.g., consumer, corporate, home office).

b. State: Analyze and report financial performance by state, including gross sales, revenue, and profit after tax.

c. Product: Provide insights into the performance of different products by calculating and comparing financial metrics.

Analysis of Products Sold in 2014:

Analyze sales data for the period between January and December 2014. Calculate the number of sales, sales proportions, and financial performance for each product. Determine growth or decline in product lines based on the sales trends and financial metrics.

Year-on-Year Comparison:

Compare the financial figures for 2013 Q4 and 2014 Q4. Reproduce the report at higher levels such as product category and state to identify any significant changes in performance and highlight year-on-year results.

Key Product Drivers of Performance:

Identify the products that contribute the most to the overall financial performance by analyzing their revenue, profit margin, and profit after tax. These key products will provide insights into the areas of focus for improving performance.

Product Subcategories for Discontinuation:

Identify product subcategories with poor outcomes based on their financial performance. Consider discontinuing these subcategories to optimize resources and focus on more profitable areas.

Products with Highest Tax Liabilities:

a. Overall: Calculate the tax liabilities for each product and determine the product with the highest