Excel Charting Tools

User Generated

5Ubjneq6

Business Finance

Description

Please complete this excel charting exercise that is attached. I have also attached and example of another charting exercise please take a look at that.

Unformatted Attachment Preview

DATA BARS (under Conditional Formatting) Sales of Bike Year 2010 2011 2012 2013 2014 2015 2016 2017 2018 Year 2010 2011 2012 2013 2014 2015 2016 2017 2018 Year 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 14850 15320 16500 14200 19530 18000 15300 13000 22500 Sales of Sale of Bike Helmets Clothing 1500 5000 2450 6750 2000 4000 2700 3500 3500 8000 2300 4500 1750 3200 1500 2500 3300 5500 Weather (Temperature - in F degree) Jan Feb Mar 8 40 12 12 18 40 5 50 13 10 15 30 9 13 60 8 35 25 11 17 50 13 20 40 5 15 30 Actual Sales 14850 15320 16500 14200 19530 18000 15300 13000 22500 Projected Sales 22500 25000 26000 28000 Apr May 50 60 45 50 50 55 60 60 50 70 55 75 65 63 65 48 45 75 To create Data Bars 1) Highlight the Cell range with values you want to display as data bars 2) Go to Conditional Formatting 3) Go to Data Bars 4) Choose the Different Gradient Fill Colors for the 3 columns (Sales of Bike, Sales of Bike Helmet, Sales of Clothing) June Column for Sparklines 80 55 85 70 85 88 60 65 65 To create Sparklines 1) Choose cell where you want to display Sparkline 2) Go to 'INSERT" Tab 3) Choose Sparklines - either column or Line 4) You can change the background color of the individual cell with 'Fill Color' Creating a Projected Chart Create your Projected Sales chart here 1) Split data in Actual and Project Sales 2) Choose the Cells with the 2 Column Data sets (exclude the Year) 3) Go to 'Insert' Tab and choose - Line with Mark 4) Then right click on chart, choose Select Data and Edit Horizontal Axis Label 5) Choose the Year Column to represent the new Horizontal Axis Label. This should now display the Year data individual cell with 'Fill Color' ls with the 2 Column Data sets ab and choose - Line with Mark k on chart, choose Select Data ear Column to represent the Item # 1 2 3 4 Selling 4 Types of DVD Player (SEE POWERPOINT TO INPUT NUMBERS & DETERMINE FORMAT Profit per Total Profit Retail Price Wholesale Cost Unit Product (Calculate) (Data) (Data) (Calculate ) Panasonic DVD-LS50 Audiovox DVD1500 Mintek MDP-1810 Toshiba SD-P2600 Total Aggregate Profits $ $ $ $ Place Stacked Column Chart here TYPE YOUR NAME HERE 349.95 329.00 199.99 279.00 $ $ $ $ 192.47 180.95 109.99 153.45 RMINE FORMAT Purchase Quantity (Data) 380 395 100 281 Total Cost (Calculate) 8 THINGS TO DO - You need to fill in the Yellow cells with formula 1 2 3 4 5 6 7 8 Total Profit = Profit per Unit x Purchase Quantity Profit Per Unit = Retail Price - WholeSale Cost Total Cost = Purchase Quantity x Wholesale Cost Total aggregate Profits (use SUM) Total of all Purchase Quantity (use SUM) Total aggregate Cost (use SUM) Create Stacked Column Chart (use Products & Total Profit) Create Exploding Pie Chart (use Products & Total Profit) Place Exploding Pie Chart here DATA BARS (under Conditional Formatting) 2010 2011 2012 2013 2014 2015 2016 2017 2018 Sales of Bike 14850 15320 16500 14200 19530 18000 15300 13000 22500 2010 2011 2012 2013 2014 2015 2016 2017 2018 Weather (Temperature - in F degree) Jan Feb Mar 8 40 12 12 18 40 5 50 13 10 15 30 9 13 60 8 35 25 11 17 50 13 20 40 5 15 30 Year Year Year 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 Actual Sales 14850 15320 16500 14200 19530 18000 15300 13000 22500 Sales of Bike Helmets 1500 2450 2000 2700 3500 2300 1750 1500 3300 Sale of Clothing 5000 6750 4000 3500 8000 4500 3200 2500 5500 Projected Sales Apr May 50 60 45 50 50 55 60 60 50 30000 25000 20000 15000 10000 22500 25000 26000 28000 5000 0 70 55 75 65 63 65 48 45 75 To create Data Bars 1) Highlight the Cell range with values you want to display as data bars 2) Go to Conditional Formatting 3) Go to Data Bars 4) Choose the Different Gradient Fill Colors for the 3 columns (Sales of Bike, Sales of Bike Helmet, Sales of Clothing) June 80 55 85 70 85 88 60 65 65 To create Sparklines 1) Choose cell where you want to display Sparkline 2) Go to 'INSERT" Tab 3) Choose Sparklines - either column or Line 4) You can change the background color of the individual cell with 'Fill Color' Creating a Projected Chart Actual Sales Projected Sales 1) Split data in Actual and Project Sales 2) Choose the Cells with the 2 Column Data sets (exclude the Year) 3) Go to 'Insert' Tab and choose - Line with Mark 4) Then right click on chart, choose Select Data and Edit Horizontal Axis Label 5) Choose the Year Column to represent the new Horizontal Axis Label. This should now display the Year data individual cell with 'Fill Color' ls with the 2 Column Data sets ab and choose - Line with Mark k on chart, choose Select Data ear Column to represent the Item # 1 2 3 4 Selling 4 Types of DVD Player (SEE POWERPOINT TO INPUT NUMBERS & DETERMINE FORMAT Profit per Total Profit Retail Price Wholesale Cost Unit Product (Calculate) (Data) (Data) (Calculate ) Panasonic DVD-LS50 $ 59.842,40 $ 349,95 $ 192,47 $ 157,48 Audiovox DVD1500 $ 58.479,75 $ 329,00 $ 180,95 $ 148,05 Mintek MDP-1810 $ 9.000,00 $ 199,99 $ 109,99 $ 90,00 Toshiba SD-P2600 $ 35.279,55 $ 279,00 $ 153,45 $ 125,55 Total Aggregate Profits $ 162.601,70 $60.000,00 $50.000,00 $40.000,00 $30.000,00 Place Stacked Column Chart here $20.000,00 $10.000,00 $Panasonic DVD-LS50 TYPE YOUR NAME HERE Audiovox DVD1500 Mintek MDP1810 Toshiba SDP2600 RMINE FORMAT Purchase Quantity (Data) 380 395 100 281 1156 Total Cost (Calculate) 8 THINGS TO DO $ 73.138,60 $ 71.475,25 $ 10.999,00 $ 43.119,45 $ 198.732,30 1 2 3 4 5 6 7 8 Total Profit = Profit per Unit x Purchase Quantity Profit Per Unit = Retail Price - WholeSale Cost Total Cost = Purchase Quantity x Wholesale Cost Total aggregate Profits (use SUM) Total of all Purchase Quantity (use SUM) Total aggregate Cost (use SUM) Create Stacked Column Chart (use Products & Total Profit) Create Exploding Pie Chart (use Products & Total Profit) Place Exploding Pie Chart here Panasonic DVD-LS50 Audiovox DVD1500 Mintek MDP-1810 Toshiba SD-P2600
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached.

DATA BARS (under Conditional Formatting)
Sales of
Bike

Year
2010
2011
2012
2013
2014
2015
2016
2017
2018

Year
2010
2011
2012
2013
2014
2015
2016
2017
2018

Year
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021

14850
15320
16500
14200
19530
18000
15300
13000
22500

Sales of
Sale of
Bike Helmets Clothing
1500
5000
2450
6750
2000
4000
2700
3500
3500
8000
2300
4500
1750
3200
1500
2500
3300
5500

Weather (Temperature - in F degree)
Jan
Feb
Mar
8
40
12
12
18
40
5
50
13
10
15
30
9
13
60
8
35
25
11
17
50
13
20
40
5
15
30

Actual Sales
14850
15320
16500
14200
19530
18000
15300
13000
22500

Projected Sales

Apr

May
50
60
45
50
50
55
6...


Anonymous
Great! 10/10 would recommend using Studypool to help you study.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags