Accounting Problem. Set

Dashboard Creation

Project is Due (July 25, 2021 No Extensions of time will be given) Create the following excel pivot table and corresponding graphs and develop a dashboard that is both easy to read, use an appropriate title to label what the graph is showing, and is interactive with all other graphs through slicers. These graphs will be used to answer and display your analysis using data visualization techniques in conjunction with your narration.

When you are reviewing the data set, make sure you create a table so you can easily refer to it in your pivot tables. Be sure to name your worksheets and the Pivot tables so you can easily identify them when putting them into your dashboard.

Trended data

2020 sales actual vs. budget

2019 sales actual vs. budget

• In the dashboard place them side by side so you can see the comparison

Y-0-Y Revenue

Create a graph that shows the monthly 2020 actual revenue and compares this to the monthly 2019 actual revenue

Totals

Create a totals graph that shows 2020 actual vs. budget revenues and on the same graph show the 2019 actual vs. budget revenues

Revenue by Category and Product

Create a graph that compares 2020 actual revenue by category to 2019 actual revenue by category and shows the difference in dollars.

Create a graph that compares 2020 actual revenue by product to 2019 actual revenue by product and shows the difference in dollars.

Revenue by Customer

Create a graph that shows actual revenues by customer for 2020 and the % of total revenue. On the same graph, you are to show the % difference from last year ( use a Pie Chart or horizontal bar graph)

Create a graph that shows actual revenues by customer for 2019 and the % of total revenue. (use a Pie Chart or horizontal bar graph) place the graphs next to each other for easy comparison

Make sure to include Slicers (Filter) for the following items:

Month Quarter Customer Product Category

PowerPoint Development

Develop a PowerPoint to answer the following questions. Each question should have at least one slide that provides an analysis and uses the Excel Dashboard to provide a data visualization that corresponds to the question asked. Make sure you include the question for each slide and you clearly point out the answer to your question (Both written and visualized)

Questions to Answer

1. Develop an introduction: In one to three slides, provide an overview of the totals 2020 vs. 2019. And show the comparative trends between the two years. Provide a narrative highlighting your summary of findings. (e.g. sales for 2020 decreased by 20%. Actual revenue vs. budget revenue was off by XXX%, etc.)

2. Analysis by Quarters: Provide an analysis comparing 2020 quarterly revenues actual vs. budget. Identify the best performing quarter and the worst performing quarter. Visualize your data and provide some insight to what may be driving the performance. (e.g. Walmart sales increase by, Macy’s sales decrease by, Accessories revenue was up, outerwear revenue was down by x%)

3. Analysis of Categories: Provide a graph that shows comparison of revenues (2020 vs. 2019) by category and show the change in revenue dollars from 2019. Show the sales products for the category with the biggest decrease and the category with the smallest decrease in revenues. In your narrative, identify the products that are driving the largest and smallest decreases.

4. Analysis of Customers: Recreate the graph shown in Fig. 1 and make sure it is included in your dashboard. The graph should interact with all of the other graphs when the criteria for other items is selected. Create a power point slide and give your analysis (narrated points) on what you think might have contributed to the very large declines in some customers while other customers see little declines. Provide a summary as to what product category increased or decreased. Provide a summary of what products drove the performance in the categories you highlight. Create another slide (1 to two) providing analysis of the customers and show if their performance improved or declined as the year progressed. Use the dashboard to create a data visualization of your findings. Make sure your narration explain what you found and your findings are clearly identified in the graphs shown. Fig. 1