The AvocadoAnalysis

It is a well-known fact that Millennials love avocado toast. It is also a well-known fact that Millennials (and everyone, for that matter) loves living in a city where they can afford the rent. Finding a city where rent is affordable and avocados and plentiful and cheap sounds like a dream. Your task is to analyze avocado sales in the United States with demographic factors about the sales regions. Your final goal is to provide Pivot Tables, a Pivot Chart, and a Forecast Sheet to display the results of your analysis. Useful metadata about the Avocado data: Complete the following requirements using the Avocado.xlsx file: For the following requirements, create any calculated fields and/or measures you need for the analysis in the Data Model. Round all price calculations up to two decimal places and round up all integer calculations. Format all data appropriately for display. Give descriptive names for any measures created. Save and close the AvocadoAnalysis.xlsx file. All the sales data is real, from the Hass Avocado Board Average Price is per Avocado The volume measures (Total Volume through XLargeBags) are weighed in ounces An average Hass avocado weighs 6 ounces The PLU codes 4046, 4225, and 4770 refer to Small, Large, and Extra-Large Hass avocados, respectively Save the file as AvocadoAnalysis(yourlastname).xlsx. In the Region sheet, in column D, make a header called “AverageAvocadoPrice” and calculate the average price of an avocado for each region. Format the data in the three tabs as appropriate data types, keeping the visual appeal of display in mind. Create a Data Model in Power Pivot that includes all three sheets of data. Name the sheets in the data model appropriately. Create relationships as appropriate. Create a Pivot Table in a new sheet to the right of Rental Price called Price per Region. This pivot table should compare the total sales price of avocados per region per month. Display the region names. Use a timeline to allow the date range to be dynamically selectable. Place the timeline above the pivot table (inserting rows as needed to move the table). Apply conditional formatting to the sales figures (not the totals) that shows the top 10% of numbers as light green with a green fill, and the bottom 10% of figures as light red with a red fill. Create a Pivot Table in a new sheet to the right of Price per Region called Volume per Timeframe. This pivot table should show overall volume of avocado sales per month per year. Include the region name under the year. For example, you should be able to easily see the difference in overall amounts between December 2015 (184,582,890) and January 2016 (296,287,516). Include a timeline set to years and a slicer for the region name. Set the slicer to display the results for Atlanta, Louisville, and Nashville. In cells directly under the pivot table, calculate the overall Ounces of Avocado per person (across all time periods) in each of these regions. In your formula, round up the result to two decimal places. Below the calculations, state which of the three cities has the highest ratio. Create a Pivot Chart on a new sheet to the right of Volume per Timeframe called Profitability. This pivot chart should show which regions (names, not ids) generated the most profit from overall avocado sales. Assume that the cost (to the seller) of a conventional avocado is $1.00 and the cost of an organic avocado is $1.50. Place the chart stretching from cell A1 to T30. Have the chart show only the top 10 highest profit regions and add a date filter show it can switch between years. Give the chart an appropriate title and axis labels. Add data values and format them as black text, bold, and as currency. In cell A31, state which region had the highest cells in 2015 and the value. For example: “In 2015, Nashville had the highest profit at $4,963,097.94” (this is not true, just an example). Do the same in cells A32 for 2016, A33 for 2017, and A34 for 2018. Create a Pivot Table on a new sheet to the right of Profitability called Rental and Ratio. The pivot table should show the name of every region and a KPI that you calculate. Create a KPI that considers the price of avocados in a region and the price of rental housing in that region. Ensure that some of the regions score high (a green light) and that others do not (yellow and red). Below the pivot table, explain what your KPI is, how it is calculated, and what exactly it means to someone looking at your table. Create a forecast sheet that predicts overall volume of sales. You will need to sum the total volume of avocados per date before creating this sheet. Create a new sheet to the right of Ratio and Rental called 4cast. Create your table for the Forecast Worksheet on the 4cast sheet. Set the forecast worksheet to end the forecast on 6/1/2020. Rename the new forecast worksheet to Forecast and move it to the right of 4cast. Add an Exponential trendline based on the forecast to your forecast chart.In cell G1, state how much the difference is between the forecasted volume of 6/1/2020 and 5/31/2015. For example: “6/1/2020 is forecasted to have 200,000 ounces greater volume than 5/31/2015”.