Logo

Module 4 - End of Module Project 2 #part1

Description

Topics covered:

  1. Geneva Wood is the owner of Tetra Sammander Insurance. Geneva is preparing year-end financial data to summarize and visualize the company's position and modeling costs of a new life insurance product that the company is considering offering. She has created charts to illustrate some of her data and is using functions to calculate the future value of insurance policies. Switch to the Revenues worksheet. In the range F6:F10, add line sparklines based on the data in the range B6:E10 and then add markers. Apply the Green, Accent 6 sparkline color and the Green, Accent 6, Darker 25% marker color.
  2. Apply a Gradient Fill, Green Data Bar conditional formatting rule into the range G6:G10.
  3. Geneva wants a clustered column chart representing the total revenue for each insurance type by year. Create a 2-D clustered column chart based on the range A5:E9. Resize and reposition the chart so that the upper-left corner is located within cell H5 and the lower-right corner is located within cell O24. Enter Yearly Revenues by Insurance Type as the chart title, then bold the title, and change the font size to 16 point.
  4. Apply a Green, Accent 6 border to the plot area of the chart and change the width of the border to 1.5 point. Apply a Glow Shape Effect to the chart area using 5 point Green, Accent Color 6.
  5. In the 2021 Revenues by Insurance Type 3-D pie chart (in the range H26:O43), change the legend position to bottom. Add data labels that contain only percentages (not values) and that use the Center position.
  6. In the Total Revenues by Year 2018-2021 line chart (in the range A11:G28), change the minimum bound of the vertical axis to 2500000, change the major units of the vertical axis to 500000 change the minor units to 250000, and then add Inside tick marks for the minor units on the vertical axis.

#sam #spreadsheet #excel Buy us a coffee: https://www.buymeacoffee.com/taandar ---

Disclaimer: This video is intended for educational purposes only. The purpose of this video is to help students understand how to solve problems in the Cengage business spreadsheet course. However, please note that any attempt to copy or replicate the solutions shown in this video may be considered a violation of academic integrity policies at your college or university. We strongly encourage you to use this video as a guide and to complete the assignments on your own, without resorting to cheating or plagiarism. We are not responsible for any consequences that may result from the use of this video in violation of academic integrity policies.

Transcript

1 00:00:00,000 --> 00:00:05,880 in module 4 in the module project 2 and

2 00:00:04,020 --> 00:00:08,460 it is saying switch to the revenue

3 00:00:05,880 --> 00:00:13,019 worksheet so let's switch

4 00:00:08,460 --> 00:00:17,220 in the range F6 through F10 so I have 6

5 00:00:13,019 --> 00:00:19,560 through F10 add line sparklines based on

6 00:00:17,220 --> 00:00:23,460 the Range in based on the data in the

7 00:00:19,560 --> 00:00:25,740 range B6 through E10 so go to insert tab

8 00:00:23,460 --> 00:00:30,060 and then click on Smart client and use

9 00:00:25,740 --> 00:00:32,460 the Linux pipeline and based on P6

10 00:00:30,060 --> 00:00:36,239 through E10

11 00:00:32,460 --> 00:00:39,059 and then hit OK apply the green accent 6

12 00:00:36,239 --> 00:00:42,780 sparkline color

13 00:00:39,059 --> 00:00:45,059 so let's expand this sparkline color and

14 00:00:42,780 --> 00:00:47,460 then let's overrun it and it is saying

15 00:00:45,059 --> 00:00:50,940 green x and six so these are required

16 00:00:47,460 --> 00:00:54,899 sparkline color and then for markers it

17 00:00:50,940 --> 00:00:57,300 is saying uh green accent 6 darker 25

18 00:00:54,899 --> 00:00:59,879 percent marker color

19 00:00:57,300 --> 00:01:03,719 so go to the marker color and then

20 00:00:59,879 --> 00:01:06,420 markers and then darker 25 percent so

21 00:01:03,719 --> 00:01:09,960 this is the required so yeah marker is

22 00:01:06,420 --> 00:01:12,840 also had it after that apply a gradient

23 00:01:09,960 --> 00:01:15,260 seal green data bar conditional

24 00:01:12,840 --> 00:01:19,619 formatting rule to the ring G6 through

25 00:01:15,260 --> 00:01:22,439 Z10 so let's select G6 through G10 and

26 00:01:19,619 --> 00:01:24,060 it is in conditional formatting so go to

27 00:01:22,439 --> 00:01:26,460 the Home tab and here this conditional

28 00:01:24,060 --> 00:01:28,740 formatting and here is data bar and it

29 00:01:26,460 --> 00:01:32,159 is saying gradient fill green

30 00:01:28,740 --> 00:01:34,380 so over it is green gradient field so

31 00:01:32,159 --> 00:01:38,100 these are required

32 00:01:34,380 --> 00:01:40,560 uh Geneva wants a clustered column chart

33 00:01:38,100 --> 00:01:43,619 representing a total revenue for each

34 00:01:40,560 --> 00:01:45,720 Insurance type buyer Creator 2D cluster

35 00:01:43,619 --> 00:01:51,180 column chart based on the Range A5

36 00:01:45,720 --> 00:01:53,840 through E9 so select A5 through E9 E9

37 00:01:51,180 --> 00:01:56,460 and then go to insert tab

38 00:01:53,840 --> 00:01:59,040 and then click here

39 00:01:56,460 --> 00:02:02,280 and then the just overrun it and it will

40 00:01:59,040 --> 00:02:05,219 show close that column so it is enclosed

41 00:02:02,280 --> 00:02:07,799 yeah cluster column let's click here

42 00:02:05,219 --> 00:02:12,900 and then resize and reposition upper lid

43 00:02:07,799 --> 00:02:16,280 at 5 to over 24. so let's deposition it

44 00:02:12,900 --> 00:02:16,280 as five

45 00:02:16,680 --> 00:02:22,620 this is S5 and then over 24

46 00:02:21,300 --> 00:02:25,680 oops

47 00:02:22,620 --> 00:02:30,319 and then just just click on the corner

48 00:02:25,680 --> 00:02:30,319 and then resize it over 24

49 00:02:31,800 --> 00:02:36,660 so yeah this is done

50 00:02:34,739 --> 00:02:38,879 after that

51 00:02:36,660 --> 00:02:42,000 uh inter yearly Revenue by Insurance

52 00:02:38,879 --> 00:02:45,239 type as a title hold the title change

53 00:02:42,000 --> 00:02:46,680 the font size to 16 points so just copy

54 00:02:45,239 --> 00:02:48,840 this

55 00:02:46,680 --> 00:02:49,980 and then double click on the title

56 00:02:48,840 --> 00:02:52,440 double click

57 00:02:49,980 --> 00:02:54,540 so make sure the font is same

58 00:02:52,440 --> 00:02:57,120 now paste it

59 00:02:54,540 --> 00:02:58,739 so fund is same sometimes at the front

60 00:02:57,120 --> 00:03:00,420 of the instruction is different and if

61 00:02:58,739 --> 00:03:03,060 we copy paste it then it will also

62 00:03:00,420 --> 00:03:06,540 change the font of this so we should

63 00:03:03,060 --> 00:03:08,280 make sure that one is same and yeah it

64 00:03:06,540 --> 00:03:13,220 is it is in bold

65 00:03:08,280 --> 00:03:13,220 now change the font size to 16 points

66 00:03:13,680 --> 00:03:19,580 it is saying apply a green accent 6

67 00:03:16,140 --> 00:03:19,580 border to the plot area

68 00:03:19,739 --> 00:03:23,280 so

69 00:03:21,060 --> 00:03:25,739 just overrun it and it will so yeah this

70 00:03:23,280 --> 00:03:28,680 is plot area and this whole chart area

71 00:03:25,739 --> 00:03:31,440 so for the plot area uh it is saying to

72 00:03:28,680 --> 00:03:35,159 add the Border

73 00:03:31,440 --> 00:03:39,120 and border of green accent 6 and width

74 00:03:35,159 --> 00:03:42,780 of 1.5 points so just just click on

75 00:03:39,120 --> 00:03:45,900 click on here plot area go to format and

76 00:03:42,780 --> 00:03:49,200 then save outline press encoder of green

77 00:03:45,900 --> 00:03:51,840 accent 6. and then again click on Save

78 00:03:49,200 --> 00:03:53,099 outline and then click on wait and then

79 00:03:51,840 --> 00:03:56,459 more lines

80 00:03:53,099 --> 00:03:59,400 and here you can access the width and it

81 00:03:56,459 --> 00:04:01,920 is saying 1.5

82 00:03:59,400 --> 00:04:05,220 so this is done

83 00:04:01,920 --> 00:04:09,000 uh yeah apply

84 00:04:05,220 --> 00:04:11,220 xm6 okay now apply a glossive effect to

85 00:04:09,000 --> 00:04:14,220 the Chart area using five point green

86 00:04:11,220 --> 00:04:16,680 action color six now glue save effect to

87 00:04:14,220 --> 00:04:20,220 the Chart area charterium is this whole

88 00:04:16,680 --> 00:04:22,740 area just click on here go to format and

89 00:04:20,220 --> 00:04:25,500 click on Save effects and then click on

90 00:04:22,740 --> 00:04:30,300 glue and then green

91 00:04:25,500 --> 00:04:33,300 green 0.5 accent color 6.

92 00:04:30,300 --> 00:04:35,820 so let's make sure you have 0.5 green

93 00:04:33,300 --> 00:04:38,520 accent color sticks after that it is

94 00:04:35,820 --> 00:04:39,660 saying in the 2021 Revenue by Insurance

95 00:04:38,520 --> 00:04:45,380 type

96 00:04:39,660 --> 00:04:45,380 3d5 chart yes 26 through o43

97 00:04:47,820 --> 00:04:54,060 now it is saying uh change the legend

98 00:04:50,759 --> 00:04:56,060 position to bottom and data level that

99 00:04:54,060 --> 00:04:58,860 contains only percentage

100 00:04:56,060 --> 00:04:59,940 so first of all let's change the legend

101 00:04:58,860 --> 00:05:02,280 position

102 00:04:59,940 --> 00:05:04,440 click on the chat chart and double click

103 00:05:02,280 --> 00:05:07,620 on it and it will appear and click on

104 00:05:04,440 --> 00:05:10,560 this drop down and click on Legend

105 00:05:07,620 --> 00:05:13,380 and after this click on this icon and

106 00:05:10,560 --> 00:05:15,960 then choose photo

107 00:05:13,380 --> 00:05:19,080 after that it is saying

108 00:05:15,960 --> 00:05:21,000 where where we add data level that

109 00:05:19,080 --> 00:05:22,560 contains only percentage

110 00:05:21,000 --> 00:05:24,860 so

111 00:05:22,560 --> 00:05:28,259 only percentage

112 00:05:24,860 --> 00:05:31,080 and that use the center position okay

113 00:05:28,259 --> 00:05:33,300 so again click on the chart and then

114 00:05:31,080 --> 00:05:35,820 click on this plus sign and then check

115 00:05:33,300 --> 00:05:38,639 mark data level after that we need to

116 00:05:35,820 --> 00:05:41,759 adjust this again click on this drop

117 00:05:38,639 --> 00:05:44,759 down and then data level and then click

118 00:05:41,759 --> 00:05:46,800 on this icon and then label options and

119 00:05:44,759 --> 00:05:48,479 we should only show the percentage sign

120 00:05:46,800 --> 00:05:49,320 so just click on the percentage check

121 00:05:48,479 --> 00:05:52,380 mark

122 00:05:49,320 --> 00:05:55,020 and then uncheck it and then it is

123 00:05:52,380 --> 00:05:57,479 saying Center position okay

124 00:05:55,020 --> 00:06:00,259 this is done

125 00:05:57,479 --> 00:06:04,520 under in the total revenue by year 2018

126 00:06:00,259 --> 00:06:07,699 2021 11 through G

127 00:06:04,520 --> 00:06:10,199 28 so that this is the required chart

128 00:06:07,699 --> 00:06:12,300 and it is saying

129 00:06:10,199 --> 00:06:15,300 change the minimum bound of the vertical

130 00:06:12,300 --> 00:06:17,100 axis to this Crystal scope is

131 00:06:15,300 --> 00:06:19,259 and then click on this chart and then

132 00:06:17,100 --> 00:06:22,560 this section will appear and then click

133 00:06:19,259 --> 00:06:24,840 on this drop down and then vertical axis

134 00:06:22,560 --> 00:06:27,660 and then click on this icon and then

135 00:06:24,840 --> 00:06:31,080 here is access option and then change

136 00:06:27,660 --> 00:06:31,979 the minimum bound just copy it and paste

137 00:06:31,080 --> 00:06:33,660 it

138 00:06:31,979 --> 00:06:36,539 and then hit enter and it will

139 00:06:33,660 --> 00:06:38,699 automatically adjust maximum value also

140 00:06:36,539 --> 00:06:42,479 now change the major unit of the

141 00:06:38,699 --> 00:06:46,919 vertical axis so measure unit

142 00:06:42,479 --> 00:06:48,660 so this is copy and paste it and now it

143 00:06:46,919 --> 00:06:50,819 is not changing let's let's change this

144 00:06:48,660 --> 00:06:53,520 minority and let's see if that has just

145 00:06:50,819 --> 00:06:55,560 automatically so change the mineral to

146 00:06:53,520 --> 00:06:58,460 this so just copy it

147 00:06:55,560 --> 00:07:01,440 and then

148 00:06:58,460 --> 00:07:05,660 paste it

149 00:07:01,440 --> 00:07:05,660 then now again for this copy it

150 00:07:06,120 --> 00:07:09,919 and then face details

151 00:07:10,440 --> 00:07:15,180 change the major unit of the vertical

152 00:07:12,660 --> 00:07:17,759 axis when the unit of the vertical axis

153 00:07:15,180 --> 00:07:20,940 to this

154 00:07:17,759 --> 00:07:23,280 okay now this is the

155 00:07:20,940 --> 00:07:25,740 apply a solid fill the effect so from

156 00:07:23,280 --> 00:07:28,160 Step 7 3 step 12 we will be doing in

157 00:07:25,740 --> 00:07:28,160 next video

Recommended Books

Reading books is a great way to learn. Here are some of the books we recommend.