Answered step by step
Verified Expert Solution
Question
1 Approved Answer
TornadoPrior to stating, save Excel File and Cloud File to folder 2 . In the Tornado worksheet, select the range A 3 :E 1 3
TornadoPrior to stating, save Excel File and Cloud File to folder In the Tornado worksheet, select the range A:E and then insert a D clustered column chart. Move the embedded chart to a chart sheet named Sightings Chart Change the chart style to Style the last chart style in the second row of the Chart Styles gallery Insert the chart title Tornado Sightings: at the top of the chart, and then change the font size to points Add the vertical axis title Sightings in a point font with horizontal orientation. Add the horizontal axis title Years in a point font Move the legend to the bottom of the chart, and then set its font size to points Change the line style of the horizontal gridline from a solid line to a dashed line. Hint: Use the Dash type button in the Line Style options in the Format Major Gridlines dialog box. Add vertical gridlines to the major tick marks in the chart. Display the gridlines as dashed lines Change the chart type of the All data series from a column chart to a D line chart. Change the color of the line to standard blue Select the plot area and change the fill to a picture fill, using the Cloud.jpg file located in the ExcelCase folder included with your Data Files. Hint: Open the Format Plot Area dialog box. In the Fill options, click the Picture or texture fill option button, click the File button, and then locate and select the picture file.Bowls In the Bowls worksheet, create an Excel table. Rename the table as ProductSales. Format the Sales column in the Currency number format with no decimal places Make a copy of the Bowls worksheet, and then rename the copied worksheet as QHint: Press the Ctrl key and drag the sheet tab to the right of the Bowls sheet tab to make a copy of the worksheet. Sort the table in ascending order by territories, then by product group, then by year, and then by month. Month should be sorted in chronological order Jan Feb, Mar, not in alphabetical order Make a copy of the Bowls worksheet, and then rename the copied worksheet as Q Display records for Automotive and Electronics products in excluding sales in Vancouver. Sort this data by sales in descending order. Add a Total row and calculate the average sales for the filtered data. Change the label in the Total row to Average. Split the worksheet into two panes. In the top pane, display all the rows except the last row on your screen. In the bottom pane, display the Total row Make a copy of the Bowls worksheet, and then rename the copied worksheet as Q Display subtotals for sales Sum by year, month Jan Feb, Mar, and territory Create a PivotTable of total sales by product group, format as $ with no decimals. Create a PivotChart of the data and move it directly below the PivotTable. Add the Chart title: Sales by Product Group and remove the legend from the chart. Create a second PivotTable of total sales by territory in the same worksheet, format as $ with no decimals. Create a PivotChart of the data and move it directly below the PivotTable. Add the Chart title: Sales by Terrirtory and remove the legend from the chart. Rename the worksheet as Q Create a PivotTable to show four calculations: average, total, minimum, and maximum sales, categorized by territories and product group, and filtered by year and territories using two slicer filters. Display the results for and the Vancouver and Washington territories, format all values as $ no decimal. Rename the worksheet as QMustard In cell C insert an IF function that tests whether the value in cell B is equal to an empty text string If so display an empty text string; otherwise, use the VLOOKUP function to look up the name of the item corresponding to the item ID in cell B in the Product List table. Specify an exact match. Copy the formula in cell C to the range C:C In cell D insert an IF function that tests whether the value in cell C is equal to an empty text string. If so display an empty text string; otherwise, use the VLOOKUP function to look up the price of the item in the Product List table. Again, specify an exact match and copy the formula in cell D to the range D:D In cell F insert an IF function that tests whether the value in cell B is equal to an empty text string. If so display an empty text string; otherwise, multiply the item price in cell D by the quantity in cell E Copy the formula in cell F to the range F:F In cell F calculate the total charges from the range F:F The Mustard Hut gives a percent discount for sales over $ Insert an IF function in cell F that calculates the discount if any for the order. In cell F calculate the cost of the order minus the discount There is a percent sales tax on all orders. Calculate the sales tax in cell F Calculate the total cost of the order charge after discount plus the sales tax in cell F Test the worksheet by inserting the following sample customer order: Customer Paul Nichols Date Order No Shipping Address Palm Drive Clearwater, FL Customer Order: EX item HVM items HVM items FMitems FM items HD item Verify that the subtotal is $ the discount is $ the cost after discount is $ the percent sales tax is $ and the total cost of the order is $
TornadoPrior to stating, save Excel File and Cloud File to folder In the Tornado worksheet, select the range A:E and then insert a D clustered column chart. Move the embedded chart to a chart sheet named Sightings Chart Change the chart style to Style the last chart style in the second row of the Chart Styles gallery Insert the chart title Tornado Sightings: at the top of the chart, and then change the font size to points Add the vertical axis title Sightings in a point font with horizontal orientation. Add the horizontal axis title Years in a point font Move the legend to the bottom of the chart, and then set its font size to points Change the line style of the horizontal gridline from a solid line to a dashed line. Hint: Use the Dash type button in the Line Style options in the Format Major Gridlines dialog box. Add vertical gridlines to the major tick marks in the chart. Display the gridlines as dashed lines Change the chart type of the All data series from a column chart to a D line chart. Change the color of the line to standard blue Select the plot area and change the fill to a picture fill, using the Cloud.jpg file located in the ExcelCase folder included with your Data Files. Hint: Open the Format Plot Area dialog box. In the Fill options, click the Picture or texture fill option button, click the File button, and then locate and select the picture file.Bowls In the Bowls worksheet, create an Excel table. Rename the table as ProductSales. Format the Sales column in the Currency number format with no decimal places Make a copy of the Bowls worksheet, and then rename the copied worksheet as QHint: Press the Ctrl key and drag the sheet tab to the right of the Bowls sheet tab to make a copy of the worksheet. Sort the table in ascending order by territories, then by product group, then by year, and then by month. Month should be sorted in chronological order Jan Feb, Mar, not in alphabetical order Make a copy of the Bowls worksheet, and then rename the copied worksheet as Q Display records for Automotive and Electronics products in excluding sales in Vancouver. Sort this data by sales in descending order. Add a Total row and calculate the average sales for the filtered data. Change the label in the Total row to Average. Split the worksheet into two panes. In the top pane, display all the rows except the last row on your screen. In the bottom pane, display the Total row Make a copy of the Bowls worksheet, and then rename the copied worksheet as Q Display subtotals for sales Sum by year, month Jan Feb, Mar, and territory Create a PivotTable of total sales by product group, format as $ with no decimals. Create a PivotChart of the data and move it directly below the PivotTable. Add the Chart title: Sales by Product Group and remove the legend from the chart. Create a second PivotTable of total sales by territory in the same worksheet, format as $ with no decimals. Create a PivotChart of the data and move it directly below the PivotTable. Add the Chart title: Sales by Terrirtory and remove the legend from the chart. Rename the worksheet as Q Create a PivotTable to show four calculations: average, total, minimum, and maximum sales, categorized by territories and product group, and filtered by year and territories using two slicer filters. Display the results for and the Vancouver and Washington territories, format all values as $ no decimal. Rename the worksheet as QMustard In cell C insert an IF function that tests whether the value in cell B is equal to an empty text string If so display an empty text string; otherwise, use the VLOOKUP function to look up the name of the item corresponding to the item ID in cell B in the Product List table. Specify an exact match. Copy the formula in cell C to the range C:C In cell D insert an IF function that tests whether the value in cell C is equal to an empty text string. If so display an empty text string; otherwise, use the VLOOKUP function to look up the price of the item in the Product List table. Again, specify an exact match and copy the formula in cell D to the range D:D In cell F insert an IF function that tests whether the value in cell B is equal to an empty text string. If so display an empty text string; otherwise, multiply the item price in cell D by the quantity in cell E Copy the formula in cell F to the range F:F In cell F calculate the total charges from the range F:F The Mustard Hut gives a percent discount for sales over $ Insert an IF function in cell F that calculates the discount if any for the order. In cell F calculate the cost of the order minus the discount There is a percent sales tax on all orders. Calculate the sales tax in cell F Calculate the total cost of the order charge after discount plus the sales tax in cell F Test the worksheet by inserting the following sample customer order: Customer Paul Nichols Date Order No Shipping Address Palm Drive Clearwater, FL Customer Order: EX item HVM items HVM items FMitems FM items HD item Verify that the subtotal is $ the discount is $ the cost after discount is $ the percent sales tax is $ and the total cost of the order is $
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started