Founded in 2002, Analytics Software provides innovative search software, accessibility testing software, and usability testing software. All serve as part of its desktop and enterprise content management solution for government, corporate, educational, and consumer markets. The company’s solutions are used by website publishers, digital media publishers, content manager, document managers, business users, consumers, software companies, and consulting companies. Analytics Software
2) Save the spreadsheet as Tutorial 6_InDepthAnalysis_Last Name.xlsx. (Replace “Last Name” with your last name.)
4) Copy the table provided in Growth, Trends, Forecasts worksheet. 5) Create two pivot tables in the Weekly Analysis worksheet.
6) Create a “Line with Markers” pivot chart (or “Line with Markers” chart using Insert Line “Line with Markers” for MAC users) for the Sales Volume pivot table. Add the chart title “Sales Volume Weekly Analysis”, label the Y-axis “Sales Volume”, and label the X-axis “Weeks” (Dates = Horizontal Axis)
7) Create a “Line with Markers” pivot chart (or using Insert Line “Line with Markers” for MAC users) for the Actual Calls pivot table. Add the chart title “Actual Calls Weekly Analysis”, label the Y-axis “Actual Calls”, and label the X-axis “Weeks” (Dates = Horizontal Axis) Forecast Tab
8) Create a new worksheet tab and name it Forecast
9) Copy the table provided in “Growth, Trends, Forecasts” worksheet.
10) Move the Actual Calls column over 1 column into the D column to give you a blank column between sales and calls.
11) To create your forecast, you will need a 3 day moving average. Click on the Data tab and find “Data Analysis”. Select the “Moving Average” from the Data Analysis Popup.
12) Complete step 11for Actual Calls with the Output Range = $E$3:$E$181 (the column after calls)
and name the column “Calls Moving Average”. Widen the column to show the whole title.
13) Create two separate “Line with Markers” chart using Insert Line “Line with Markers” for
the Sales Moving Average and the Calls Moving Average: (Dates = Horizontal Axis)
14) Create a new worksheet tab and name it Growth
15) Copy the table provided in Growth, Trends, Forecasts worksheet.
16) Create two pivot tables in the Growth worksheet.
17) Create a new table in $H$3to $J$10 with headers “Months”, “Sales Volume”, and “Sales Growth”
18) Fill in the month’s column with the numbers 1 – 12 as seen above.
19) Copy the sales volume values from the pivot table into the first 6 rows of the “Sales Volume” column.
20) Complete the sales growth column
21) Create a new table in $H$17 to $J$29 with headers “Months”, “Actual Calls”, and “Calls
Growth”, Fill in the month’s column with the numbers 1 – 12 similar to the Sales table.
22) Complete the Actual Calls and Calls Growth columns similar to the Sales table above.
23) Create 2 separate “Line with Markers” pivot charts (or “Line with Markers” chart using Insert Line “Line with Markers” for MAC users) for the Sales Volume and Actual Calls pivot tables. Place them next to each of the new tables you created. (Months = Horizontal Axis)
24) Create a new worksheet tab and name it Trend
25) Copy the table provided in Growth, Trends, Forecasts worksheet.
26) Create two pivot tables in the Trend worksheet.
27) Create a new table in $H$3to $J$10 with headers “Months”, “Sales Volume”, and “Sales Trend”
28) Fill in the month’s column with the numbers 1 – 12 as seen in Growth.
29) Copy the sales volume values from the pivot table into the first 6 rows of the “Sales Volume” column.
30) Complete the sales trend column as seen below: (Do for all 12 months. Ignore the grey boxes; I am using fake data here so I covered it up.)
31) Create a new table in $H$17 to $J$29 with headers “Months”, “Actual Calls”, and “Calls Trend”, Fill in the month’s column with the numbers 1 – 12 similar to the Sales table.
32) Complete the Actual Calls and Calls Trend columns similar to the Sales table above.
33) Create 2 separate “Line with Markers” pivot charts (or “Line with Markers” chart using Insert Line “Line with Markers” for MAC users) for the Sales Volume and Actual Calls pivot tables. Place them next to each of the new tables you created. (Months = Horizontal Axis)
35) Insert a textbox for your report, resize as needed
36) Your report should include your detailed recommendation to Analytics Software on what they can do to help business using the future trends, growth, and forecasts and stay within their informal motto as stated above. The report should be 100+ words and include numbers and ranges referenced from the charts and tables you created. It should not be only be a summarization of the tables. (Think as though you are giving advice to a friend with their own company. What would be helpful for them? What would you want to hear if the positions were reversed? Don’t answer these exact questions in your report. They are just thought activities for how to write your recommendations)
Buy now
Instructions:
1) Open Tutorial 6_InDepthAnalysis.xlsx.2) Save the spreadsheet as Tutorial 6_InDepthAnalysis_Last Name.xlsx. (Replace “Last Name” with your last name.)
Weekly Analysis Tab
3) Create a new worksheet tab and name it Weekly Analysis4) Copy the table provided in Growth, Trends, Forecasts worksheet. 5) Create two pivot tables in the Weekly Analysis worksheet.
- 1 pivot table using Date and Sales Volume Group by 7 days so you have a sum of Sales Volumes for each week.
- 1 pivot table using Date and Actual Calls with Group by 7 days so you have a sum of Actual Calls for each week.
6) Create a “Line with Markers” pivot chart (or “Line with Markers” chart using Insert Line “Line with Markers” for MAC users) for the Sales Volume pivot table. Add the chart title “Sales Volume Weekly Analysis”, label the Y-axis “Sales Volume”, and label the X-axis “Weeks” (Dates = Horizontal Axis)
7) Create a “Line with Markers” pivot chart (or using Insert Line “Line with Markers” for MAC users) for the Actual Calls pivot table. Add the chart title “Actual Calls Weekly Analysis”, label the Y-axis “Actual Calls”, and label the X-axis “Weeks” (Dates = Horizontal Axis) Forecast Tab
8) Create a new worksheet tab and name it Forecast
9) Copy the table provided in “Growth, Trends, Forecasts” worksheet.
10) Move the Actual Calls column over 1 column into the D column to give you a blank column between sales and calls.
11) To create your forecast, you will need a 3 day moving average. Click on the Data tab and find “Data Analysis”. Select the “Moving Average” from the Data Analysis Popup.
- a. For the Input range, select: $B$3:$B$181 (or the whole Sales Volume column without the header)
- b. Interval = 3
- c. Output Range = $C$3:$C$181 (the column between sales and calls)
- d. Click OK
- e. The first two rows should be #N/A
- f. Name the column “Sales Moving Average”. Widen the column to show the whole title.
12) Complete step 11for Actual Calls with the Output Range = $E$3:$E$181 (the column after calls)
and name the column “Calls Moving Average”. Widen the column to show the whole title.
13) Create two separate “Line with Markers” chart using Insert Line “Line with Markers” for
the Sales Moving Average and the Calls Moving Average: (Dates = Horizontal Axis)
- a. For Sales include 3 columns: Sales Volume and sales Moving Average as your series, and Date as the horizontal axis. Name the series “Sales Volume” and “Sales Moving Average”, label the X-axis “Averages”, and the Y-axis “Dates”.
- b. For Calls include 3 columns: Actual Calls and Calls Moving Average as your series, and Date as your horizontal axis. Name the series “Actual Calls” and “Moving Average”, label the X-axis “Averages”, and the Y-axis “Dates”.
- c. Title the sales chart: “Sales Moving Average” and the calls chart: “Calls Moving Average”.
- d. Warning: The graphs will be large; you will need to enlarge them to be seen. Make sure both are on the same page and easy to see.
14) Create a new worksheet tab and name it Growth
15) Copy the table provided in Growth, Trends, Forecasts worksheet.
16) Create two pivot tables in the Growth worksheet.
- 1 pivot table using Date and Sales Volume in $E$3 Group by months so you have a sum of Sales Volumes for each month.
- 1 pivot table using Date and Actual Calls in $E$12 Group by months so you have a sum of Actual Calls for each month.
17) Create a new table in $H$3to $J$10 with headers “Months”, “Sales Volume”, and “Sales Growth”
18) Fill in the month’s column with the numbers 1 – 12 as seen above.
19) Copy the sales volume values from the pivot table into the first 6 rows of the “Sales Volume” column.
20) Complete the sales growth column
21) Create a new table in $H$17 to $J$29 with headers “Months”, “Actual Calls”, and “Calls
Growth”, Fill in the month’s column with the numbers 1 – 12 similar to the Sales table.
22) Complete the Actual Calls and Calls Growth columns similar to the Sales table above.
23) Create 2 separate “Line with Markers” pivot charts (or “Line with Markers” chart using Insert Line “Line with Markers” for MAC users) for the Sales Volume and Actual Calls pivot tables. Place them next to each of the new tables you created. (Months = Horizontal Axis)
- a. For Sales, add the series: Months, Sales Volume, and Sales Growth. Make sure to name each series “Sales Volume” and “Sales Growth”. Add the chart title “Sales Volume Growth Analysis”, label the Y-axis “Sales”, and label the X-axis “Months”
- b. For Calls, add the series: Months, Actual Calls, and Calls Growth. Make sure to name each series “Actual Calls” and “Calls Growth”. Add the chart title “Actual Calls Growt Analysis”, label the Y-axis “Calls”, and label the X-axis “Months”
24) Create a new worksheet tab and name it Trend
25) Copy the table provided in Growth, Trends, Forecasts worksheet.
26) Create two pivot tables in the Trend worksheet.
- a. 1 pivot table using Date and Sales Volume in $E$3
- Group by months so you have a sum of Sales Volumes for each month.
- b. 1 pivot table using Date and Actual Calls in $E$12
- Group by months so you have a sum of Actual Calls for each month.
27) Create a new table in $H$3to $J$10 with headers “Months”, “Sales Volume”, and “Sales Trend”
28) Fill in the month’s column with the numbers 1 – 12 as seen in Growth.
29) Copy the sales volume values from the pivot table into the first 6 rows of the “Sales Volume” column.
30) Complete the sales trend column as seen below: (Do for all 12 months. Ignore the grey boxes; I am using fake data here so I covered it up.)
31) Create a new table in $H$17 to $J$29 with headers “Months”, “Actual Calls”, and “Calls Trend”, Fill in the month’s column with the numbers 1 – 12 similar to the Sales table.
32) Complete the Actual Calls and Calls Trend columns similar to the Sales table above.
33) Create 2 separate “Line with Markers” pivot charts (or “Line with Markers” chart using Insert Line “Line with Markers” for MAC users) for the Sales Volume and Actual Calls pivot tables. Place them next to each of the new tables you created. (Months = Horizontal Axis)
- a. For Sales, add the series: Months, Sales Volume, and Sales Trend. Make sure to name each series “Sales Volume” and “Sales Trend”. Add the chart title “Sales Volume Trend Analysis”, label the Y-axis “Sales”, and label the X-axis “Months”
- b. For Calls, add the series: Months, Actual Calls, and Calls Trend. Make sure to name each series “Actual Calls” and “Calls Trend”. Add the chart title “Actual Calls Trend Analysis”, label the Y-axis “Calls”, and label the X-axis “Months”
Report Tab
34) Create a new worksheet tab and name it Report35) Insert a textbox for your report, resize as needed
36) Your report should include your detailed recommendation to Analytics Software on what they can do to help business using the future trends, growth, and forecasts and stay within their informal motto as stated above. The report should be 100+ words and include numbers and ranges referenced from the charts and tables you created. It should not be only be a summarization of the tables. (Think as though you are giving advice to a friend with their own company. What would be helpful for them? What would you want to hear if the positions were reversed? Don’t answer these exact questions in your report. They are just thought activities for how to write your recommendations)
Buy now
Comments
Post a Comment