Another part of Carol and Bob Levitt’s business plan for the new Levitt Winery is to analyze current market conditions. The Levitts have created a workbook that explores customer preferences and sales of wine in the United States. The workbook also explores the current wineries in Michigan against which the Levitt Winery will be competing. Bob and Carol asked you to complete their workbook by presenting this data in graphic form using Excel charts. Complete the following:

1. Open the Market workbook located in the Excel4  Review folder included with your Data Files, and then save the workbook as Market Analysis in the location specified by your instructor. 2. In the Documentation sheet, enter your name in cell B3 and the date in cell B4. 3. In the Loan Analysis sheet, enter the data values and formulas required to calculate the monthly payment on a business loan of $225,000 at 8.2 percent annual interest to be repaid in 15 years. 4. In the Market Summary worksheet, use the data in the range E21:F27 showing the types of grapes cultivated by Michigan wineries to create a pie chart comparing production rates. Embed the pie chart in the Market Summary worksheet covering the range B5:G18. 5. Format the pie chart by removing the chart title, applying the Style 11 chart style, and aligning the legend with the right edge of the chart area. 6. In the Michigan Wineries worksheet, create a line chart based on the data in the nonadjacent range B4:B16;F4:F16 showing the increase in the number of wineries in Michigan over the past 12 years. Embed the line chart in the Market Summary worksheet covering the range I5:O16.

7. Format the line chart by making the following changes: a. Format the chart with the Style 14 chart style. b. Change the chart title to Michigan Wineries. c. Change the fill color of the chart area to light blue and the plot area to white. d. Add primary major vertical gridlines to the plot area. e. Change the scale of the primary axis to range from 50 to 140 in steps of 10 units.

8. In the Michigan Wineries worksheet, create a clustered column chart using the data in the range B4:E16 showing the growth of Michigan wineries by region. Embed the chart in the Market Summary worksheet over the range I18:O28. 9. Format the column chart by making the following changes: a. Format the chart with the Style 13 chart style. b. Change the chart title to Michigan Wineries and reduce its font size to 14 points. c. Set the fill color of the chart area to light blue and the plot area to white. d. Add primary major vertical gridlines to the plot area. 10. In the U.S. Wine Sales worksheet, create a stacked column chart using the data in the range B3:E15 showing the breakout of the wine market into table wines, dessert wines, and sparkling wines or champagne. Embed the stacked column chart in the range B30:G43 of the Market Summary worksheet.

11. Format the stacked column chart by making the following changes: a. Format the chart with the Style 6 chart style. b. Change the chart title to U.S. Wine Sales and set its font size to 14 points. c. Add a primary vertical axis title with the text Millions of Cases and remove the ­primary horizontal axis title. d. Add primary major vertical gridlines. e. Set the fill color of the chart area to light blue and the plot area to white. 12. In the U.S. Wine Consumption worksheet, create a combination chart based on the data in the range B3:D15 showing how much wine Americans consume annually. Display the Gallons (millions) data series as a clustered column chart on the primary axis, and then display the Gallons (per Capita) data series as a line chart on the secondary axis. 13. Move the combination chart to the Market Summary worksheet; embed it over the range I30:O43. 14. Format the combination chart by making the following changes:

a. Format the chart with the Style 4 chart style. b. Change the chart title to U.S. Wine Consumption and set its font size to 14 points. c. Add the primary axis title Gallons (millions) and the secondary axis title Gallons (per Capita). Change the font color of the axis titles and scales to match the column and line markers. d. Remove the horizontal axis title and chart legend. e. Change the rotation of the secondary axis title to Rotate Text Down. f. Change the primary axis scale to range from 650 to 950 in intervals of 50. Change the scale of the secondary axis to range from 2.2 to 3.0 in steps of 0.1 units. g. Change the fill color of the chart area to light blue and the plot area to white. h. Add primary major vertical gridlines to the chart.

15. Insert column sparklines in the range G21:G27 of the Market Summary worksheet based on the data in the range C5:N11 of the Michigan Grapes worksheet to show whether the number of wineries growing their own grapes has increased over the past 12 years. 16. Set the axis of the sparklines so that the column heights range from 0 to a maximum of 26 for each sparkline. Ungroup the sparklines and set the color of each to match the color of the corresponding grape in the pie chart. 17. Because the Levitts plan to grow their own grapes rather than purchasing them from out-of-state vendors, they are interested in knowing how many wineries in Michigan also grow their own grapes. The results of their survey are shown in the range B20:C22 in the Market Summary ­worksheet. Add data bars to the values in the range C20:C22 using the blue gradient fill. Define a rule that sets the maximum length of the data bars in those cells to a value of 100. 18. Insert the Watermark.png graphic file located in the Excel4  Review folder as a washed-out watermark in the center section of the header of the Market Summary worksheet. 19. Save the workbook, and then close it.