We Have Numbers Of Free Samples

There are three parts to this assignment. Part A uses VBA Excel and the VBA Solver module. Part B tests your knowledge of VBA for importing, analyzing and visualizing large text datasets. Part C involves creating a dashboard from your learnings in Parts A and B.

PART A: VBA Solver and Outbound Call Centre Sales Scenario

1. Using VBA Excel code, create a named range for each of the shaded groups of cells in the “OutboundCallCentreSales” worksheet.

2. Write the VBA code macro – based on your named ranges above – that will run VBA Solver to find the maximum Profit possible that may be generated with the current Call Centre Rep Shift Costs and Call Centre Rep Shift business assumptions.

1. How would the number of call centre reps used each shift change if you revise the Solver model assumption and specify that you require a Profit (a specific target) value of $32,000?
2. What happens if the commission rate paid to each call centre rep changes to 10%?

Call Centre Rep Shift	Morning	Noon	Afternoon	Evening
# Hours	4	4	4	2
# Calls	40	20	10	50
# Sales	20	5	8	40
Revenue ($) from Sales	$340.00	$500.00	$110.00	$400.00

 

Call Centre Rep Shift Allocation
Time of Shift	Morning	Noon	Afternoon	Evening
# Call Centre Reps Used	0	0	0	0
	<=	<=	<=	<=
Maximum # Call Centre Reps Possible	100	100	100	100

 

Call Centre Staffing Resource Constraints	Reps Used		Reps Capacity
# Hours of Labour	0	<=	560
# Calls	0	<=	4,800
# Sales	0	<=	2,920
$ Commission from Sales Revenue	0	<=	8,000

 

Call Centre Revenue and Profit Summary

Shift	Morning	Noon	Afternoon	Evening	Total
Revenue	$0	$0	$0	$0	$0
Call Centre Costs
Wages	$0	$0	$0	$0	$0
Overhead	$0	$0	$0	$0	$0
Fulfillment	$0	$0	$0	$0	$0
Commissions	$0	$0	$0	$0	$0
Profit	$0	$0	$0	$0	$0

 

PART B: Using VBA Pivot Tables to Analyze Mortgage Default Data

1. Write the VBA macro to add a new worksheet named “MortgageDefaultData” after the “OutboundCallCentreSales” worksheet and import the .csv datafile “MortgageDefaultData.csv” into this new worksheet.

1. Your macro should include a function prompting a user to browse for the file.

2. Using VBA code, create an overall summary pivot table with title “Mortgage Default Analysis” from the “MortgageDefaultData” worksheet that has only column totals and uses Arial 12 italics font – and place it in a new worksheet named “MortgageDefaultDataPivot”.

1. “Default on Mortgage?” should be your pivot table’s default row field.
2. Use the “Marital Status”, “Age”, “Payments Per Year”, and “Annual Income” fields to complete your pivot table.

3. Based on your summary pivot table, in two bullet points, describe what type of customer is most likely to default on a mortgage.

1. Use VBA code to shade the appropriate rows/columns of your pivot table that support your bullet points.

Customer Number	Age	Marital Status	Annual Income	Mortgage Amount	Payments Per Year	Total Amount Paid	Default on Mortgage?
1	37	SINGLE	172,125.70	473,402.96	24	581,885.13	YES
2	31	SINGLE	108,571.04	300,468.60	12	489,320.38	NO
3	37	MARRIED	124,136.41	330,664.24	24	493,541.93	YES
4	24	MARRIED	79,614.04	230,222.94	24	449,682.09	YES
5	27	SINGLE	68,087.33	282,203.53	12	520,581.82	NO
6	30	MARRIED	59,959.80	251,242.70	24	356,711.58	YES
7	41	SINGLE	99,394.05	282,737.29	12	524,053.46	NO
8	29	SINGLE	38,527.35	238,125.19	12	468,595.99	NO
9	31	MARRIED	112,078.62	297,133.24	24	399,617.40	YES
10	36	SINGLE	224,899.71	622,578.74	12	1,233,002.14	NO
11	31	MARRIED	27,945.36	215,440.31	24	285,900.10	YES
12	40	SINGLE	48,929.74	252,885.10	12	336,574.63	NO
13	39	MARRIED	82,810.92	183,045.16	12	262,537.23	NO
14	31	SINGLE	68,216.88	165,309.34	12	253,633.17	NO
15	40	SINGLE	59,141.13	220,176.18	12	424,749.80	NO
16	45	MARRIED	72,568.89	233,146.91	12	356,363.93	NO
17	32	MARRIED	101,140.43	245,360.02	24	388,429.41	YES
18	37	MARRIED	124,876.53	320,401.04	4	360,783.45	YES
19	32	MARRIED	133,093.15	494,395.63	12	861,874.67	NO
20	32	SINGLE	85,268.67	159,010.33	12	308,656.11	NO
21	37	SINGLE	92,314.96	249,547.14	24	342,339.27	YES
22	29	MARRIED	120,876.13	308,618.37	12	472,668.98	NO
23	24	SINGLE	86,294.13	258,321.78	24	380,347.56	YES
24	32	MARRIED	216,748.68	634,609.61	24	915,640.13	YES
25	44	SINGLE	46,389.75	194,770.91	12	385,288.86	NO
26	28	MARRIED	132,323.76	518,999.12	24	776,210.07	YES
27	41	MARRIED	101,799.27	242,974.82	12	519,801.54	NO
28	42	SINGLE	72,708.46	277,473.87	4	351,375.45	YES
29	30	MARRIED	170,494.76	507,883.95	12	738,913.49	NO
30	35	SINGLE	61,742.37	178,309.62	4	204,693.76	YES
31	43	MARRIED	92,851.73	295,768.37	4	315,403.24	YES
32	27	SINGLE	67,039.07	268,602.19	12	436,736.67	NO
33	23	SINGLE	71,105.79	188,100.06	12	320,195.33	NO
34	39	SINGLE	58,421.01	249,958.88	12	427,480.93	NO
35	40	SINGLE	98,472.26	320,908.70	12	545,924.99	NO
36	37	MARRIED	112,622.95	337,873.51	12	561,803.09	NO
37	37	SINGLE	87,125.65	275,337.14	4	278,656.59	YES
38	33	SINGLE	78,298.50	260,700.34	12	517,794.52	NO
39	33	SINGLE	31,248.32	200,414.73	12	383,638.01	NO
40	35	MARRIED	99,648.61	261,283.23	24	510,798.86	YES
41	26	MARRIED	136,035.76	292,050.94	4	339,563.87	YES
42	38	MARRIED	96,591.66	189,436.48	12	366,573.56	NO
43	37	MARRIED	129,464.73	313,174.24	12	533,717.09	NO
44	35	SINGLE	56,651.96	222,577.20	12	474,357.73	NO
45	44	MARRIED	112,858.83	383,158.23	4	406,464.87	YES
46	40	MARRIED	127,668.20	369,952.30	12	677,767.03	NO
47	38	SINGLE	48,365.92	193,899.23	12	334,420.11	NO
48	37	MARRIED	92,635.16	232,272.87	12	450,877.46	NO
49	34	SINGLE	34,928.91	172,785.56	12	370,440.52	NO
50	38	SINGLE	65,939.90	230,516.29	12	470,650.97	NO
51	19	SINGLE	19,994.29	156,295.92	12	278,866.87	NO
52	28	SINGLE	18,601.00	154,948.22	24	262,810.96	YES
53	29	MARRIED	75,981.76	214,715.42	24	306,402.48	YES
54	34	SINGLE	51,892.16	222,951.96	24	322,612.12	YES
55	37	SINGLE	77,717.05	195,609.04	12	306,284.44	NO
56	33	SINGLE	48,989.15	242,739.62	12	463,181.03	NO
57	36	MARRIED	62,748.89	192,981.96	12	317,668.95	NO
58	32	SINGLE	65,887.16	211,080.01	12	285,776.62	NO
59	31	SINGLE	80,356.29	258,271.23	12	405,874.43	NO
60	38	SINGLE	101,823.49	258,203.73	12	455,916.58	NO
61	34	SINGLE	94,379.82	233,052.90	12	358,169.25	NO
62	42	SINGLE	76,018.73	295,266.94	12	489,264.03	NO
63	36	SINGLE	97,478.37	311,764.35	12	512,631.11	NO
64	36	MARRIED	115,339.70	327,722.22	24	574,984.95	YES
65	38	SINGLE	90,270.96	229,971.85	12	363,401.39	NO
66	38	SINGLE	103,902.75	222,053.28	12	376,566.99	NO
67	41	SINGLE	80,237.13	304,681.98	12	546,536.30	NO
68	33	SINGLE	81,043.08	283,421.43	4	325,346.58	YES
69	31	MARRIED	66,598.74	191,783.43	12	314,767.43	NO
70	45	MARRIED	126,044.94	336,101.28	24	506,615.89	YES
71	35	MARRIED	57,062.96	221,619.05	12	368,017.39	NO
72	37	SINGLE	97,453.05	330,548.09	12	546,945.30	NO
73	31	SINGLE	24,040.73	166,529.37	24	268,259.89	YES
74	40	MARRIED	69,542.57	273,280.70	12	500,719.21	NO
75	51	SINGLE	30,819.87	158,343.89	12	257,609.33	NO
76	36	SINGLE	111,452.32	273,157.77	12	414,238.78	NO
77	37	SINGLE	99,197.30	274,266.57	12	516,528.92	NO
78	30	SINGLE	111,849.07	252,755.64	12	553,600.26	NO
79	38	MARRIED	58,093.23	220,651.85	4	252,043.29	YES
80	32	SINGLE	90,339.04	257,357.73	12	394,492.24	NO
81	36	SINGLE	42,447.42	170,528.44	12	252,252.65	NO
82	24	MARRIED	186,071.89	567,462.00	24	992,917.97	YES
83	40	SINGLE	66,214.85	192,854.99	12	302,333.12	NO
84	31	MARRIED	177,926.22	486,118.62	24	596,495.17	YES
85	35	MARRIED	89,193.96	228,914.19	12	304,701.44	NO
86	26	MARRIED	34,006.75	185,085.90	24	316,653.63	YES
87	40	MARRIED	134,263.43	349,814.02	12	804,160.15	NO
88	32	SINGLE	73,671.39	264,767.11	12	400,427.13	NO
89	31	MARRIED	122,894.71	289,073.81	24	524,542.99	YES
90	35	MARRIED	108,777.80	308,950.00	12	609,864.32	NO
91	36	MARRIED	83,714.38	265,419.96	12	515,446.37	NO
92	33	MARRIED	62,451.47	260,270.82	12	488,596.72	NO
93	35	SINGLE	91,943.21	319,802.49	12	551,391.02	NO
94	45	SINGLE	98,582.70	302,275.29	12	567,126.53	NO
95	32	MARRIED	81,544.82	257,044.81	4	269,415.63	YES
96	35	MARRIED	100,967.67	239,231.13	4	280,087.15	YES
97	39	MARRIED	47,150.86	199,196.79	24	362,529.81	YES
98	35	SINGLE	77,333.82	294,000.47	12	542,660.46	NO
99	28	MARRIED	82,462.67	203,264.92	24	261,211.20	YES
100	33	SINGLE	74,674.75	240,254.71	12	381,989.52	NO
101	37	SINGLE	104,188.20	323,031.40	12	624,321.11	NO
102	43	SINGLE	123,232.44	320,184.68	12	571,037.41	NO
103	38	SINGLE	51,992.80	248,885.84	12	408,466.99	NO
104	36	MARRIED	96,864.47	325,280.69	12	694,314.16	NO
105	27	MARRIED	45,013.91	199,287.61	12	333,627.54	NO
106	43	MARRIED	88,648.06	319,401.06	12	623,661.22	NO
107	32	MARRIED	95,894.04	306,566.67	12	517,195.81	NO
108	30	MARRIED	128,339.80	311,681.25	12	580,313.99	NO
109	32	SINGLE	69,843.02	247,400.94	24	396,745.34	YES
110	40	SINGLE	97,362.82	334,584.25	12	606,592.51	NO
111	35	SINGLE	145,202.29	561,487.42	4	576,618.67	YES
112	34	MARRIED	61,016.14	244,668.14	24	336,515.76	YES
113	43	MARRIED	113,537.95	273,378.39	12	486,190.61	NO
114	33	SINGLE	44,449.59	270,391.40	12	485,361.47	NO
115	36	SINGLE	86,877.47	321,229.07	12	617,746.22	NO
116	36	MARRIED	88,830.50	307,726.32	12	569,442.86	NO
117	21	MARRIED	48,306.54	183,374.02	24	228,257.85	YES
118	37	MARRIED	109,528.64	278,388.40	12	488,405.12	NO
119	39	MARRIED	69,610.23	211,674.18	12	319,212.02	NO
120	34	MARRIED	132,458.56	310,287.28	24	389,241.80	YES
121	44	SINGLE	41,419.14	181,694.89	12	328,366.01	NO
122	33	MARRIED	119,395.92	299,316.74	24	412,072.27	YES
123	35	MARRIED	108,397.44	323,258.88	12	488,917.99	NO
124	46	MARRIED	132,174.19	330,062.95	4	371,805.08	YES
125	28	SINGLE	181,745.89	473,971.66	24	710,276.46	YES
126	34	SINGLE	90,438.35	286,530.97	24	400,865.80	YES
127	36	MARRIED	120,462.75	329,641.61	24	470,286.11	YES
128	35	SINGLE	26,104.55	205,515.41	4	246,329.16	YES
129	32	SINGLE	77,533.66	232,544.91	12	376,664.40	NO
130	39	SINGLE	54,209.10	173,022.07	24	234,027.85	YES
131	33	MARRIED	87,544.18	277,832.86	4	302,722.08	YES
132	37	MARRIED	180,203.55	490,200.51	12	866,749.89	NO
133	49	MARRIED	89,253.72	230,386.93	24	368,137.95	YES
134	33	MARRIED	141,736.02	320,115.13	4	340,835.55	YES
135	35	SINGLE	49,736.90	179,975.61	24	290,478.01	YES
136	35	SINGLE	177,249.75	458,109.65	4	500,596.91	YES
137	41	MARRIED	103,055.32	255,603.17	24	404,694.21	YES
138	31	SINGLE	175,779.80	584,808.59	12	1,045,501.01	NO
139	32	SINGLE	54,519.49	150,615.61	12	244,805.94	NO
140	36	SINGLE	76,998.53	208,106.85	12	347,905.60	NO
141	35	SINGLE	73,102.99	243,519.34	12	393,882.22	NO
142	33	MARRIED	95,001.37	273,681.46	24	405,241.42	YES
143	32	MARRIED	121,872.16	334,050.38	4	384,701.09	YES
144	32	SINGLE	86,785.17	236,157.17	12	483,940.57	NO
145	25	SINGLE	24,746.10	192,830.50	12	352,652.88	NO
146	33	SINGLE	93,367.68	236,732.32	12	343,032.46	NO
147	26	MARRIED	62,181.47	194,059.64	24	333,282.85	YES
148	41	MARRIED	137,568.22	308,288.15	4	356,288.38	YES
149	41	SINGLE	96,756.34	332,159.18	24	420,481.11	YES
150	38	SINGLE	99,706.09	317,686.10	24	381,810.15	YES
151	36	SINGLE	187,612.28	587,336.02	12	1,029,789.33	NO
152	40	MARRIED	83,604.48	281,093.24	24	353,788.39	YES
153	31	MARRIED	52,708.58	228,390.61	24	310,795.54	YES
154	39	SINGLE	121,308.47	582,055.66	12	1,180,593.94	NO
155	28	SINGLE	109,247.99	256,098.86	12	451,771.72	NO
156	35	MARRIED	75,032.99	214,813.65	4	252,375.03	YES
157	41	MARRIED	102,315.80	281,608.63	12	587,281.18	NO
158	29	SINGLE	96,620.07	247,444.54	12	301,477.00	NO
159	34	SINGLE	68,495.86	279,179.48	24	536,059.99	YES
160	33	SINGLE	212,215.45	607,295.86	12	1,222,021.84	NO
161	38	SINGLE	66,740.97	201,011.43	12	360,101.83	NO
162	36	MARRIED	70,161.21	201,572.84	12	380,692.92	NO
163	32	MARRIED	87,175.20	265,370.37	24	428,386.22	YES
164	34	MARRIED	72,628.62	230,619.11	12	448,178.80	NO
165	43	SINGLE	39,279.00	204,632.21	12	318,940.31	NO
166	37	MARRIED	190,861.88	563,073.48	12	1,246,036.95	NO
167	34	SINGLE	56,310.66	316,059.32	12	435,329.87	NO
168	27	SINGLE	58,530.50	151,551.75	12	207,896.07	NO
169	28	SINGLE	112,166.68	225,588.31	4	244,597.21	YES
170	31	MARRIED	76,239.29	230,020.35	12	403,705.85	NO
171	40	SINGLE	92,656.81	226,620.70	4	237,661.06	YES
172	38	MARRIED	59,859.67	195,609.41	12	324,725.74	NO
173	26	SINGLE	70,361.54	154,603.79	12	264,168.64	NO
174	35	MARRIED	61,549.48	198,467.91	4	215,231.84	YES
175	31	MARRIED	63,461.74	299,382.51	24	560,444.53	YES
176	36	SINGLE	95,202.65	300,838.81	12	561,299.99	NO
177	38	SINGLE	113,795.54	321,240.83	4	393,011.29	YES
178	32	SINGLE	62,068.51	245,699.09	24	295,089.00	YES
179	33	SINGLE	147,755.29	245,051.37	24	300,076.81	YES
180	33	SINGLE	195,212.82	508,433.13	12	895,907.99	NO
181	41	MARRIED	171,138.66	485,089.64	24	716,956.95	YES
182	36	SINGLE	117,373.60	315,229.04	12	666,951.82	NO
183	46	SINGLE	128,834.65	379,618.94	12	713,664.04	NO
184	39	MARRIED	88,564.06	290,387.70	4	306,528.40	YES
185	38	SINGLE	122,541.24	299,887.03	12	540,245.40	NO
186	30	MARRIED	98,200.81	229,150.43	12	404,912.78	NO
187	37	MARRIED	72,308.34	217,091.50	4	225,305.93	YES
188	44	SINGLE	78,924.20	178,309.90	12	349,886.32	NO
189	37	SINGLE	74,316.23	285,249.33	12	460,382.50	NO
190	34	SINGLE	67,855.56	180,488.75	24	232,397.36	YES
191	32	SINGLE	65,124.36	152,165.45	12	281,732.26	NO
192	31	MARRIED	100,428.80	248,533.44	24	439,351.14	YES
193	23	SINGLE	58,438.51	253,581.73	12	481,262.66	NO
194	36	SINGLE	81,625.98	323,656.88	12	612,117.65	NO
195	35	SINGLE	90,417.65	290,820.00	12	509,994.40	NO
196	38	MARRIED	38,302.27	244,673.40	12	411,724.20	NO
197	41	MARRIED	96,721.35	302,336.03	12	422,068.58	NO
198	35	MARRIED	118,534.70	278,223.15	12	447,927.84	NO
199	38	SINGLE	206,474.18	523,952.69	12	1,047,859.62	NO
200	34	SINGLE	49,694.50	163,292.03	4	182,796.91	YES
201	30	MARRIED	182,403.29	608,954.17	4	614,217.40	YES
202	32	MARRIED	74,096.78	246,503.24	4	274,782.49	YES
203	30	SINGLE	113,039.60	297,379.34	12	576,818.59	NO
204	45	SINGLE	100,136.43	170,076.01	24	294,784.04	YES
205	25	MARRIED	108,917.73	230,214.04	12	525,868.13	NO
206	27	MARRIED	38,900.85	198,978.82	24	281,281.79	YES
207	33	MARRIED	79,023.40	206,609.96	24	252,105.40	YES
208	34	MARRIED	86,420.57	262,772.66	12	527,581.75	NO
209	35	MARRIED	101,792.55	285,532.30	12	492,567.60	NO
210	31	SINGLE	54,969.06	190,128.91	12	293,608.25	NO
211	31	SINGLE	46,268.26	189,844.42	12	362,190.96	NO
212	32	SINGLE	107,631.50	233,234.39	12	395,830.04	NO
213	24	MARRIED	76,800.43	253,000.89	24	308,062.50	YES
214	31	SINGLE	67,797.22	188,821.20	12	337,597.43	NO
215	32	SINGLE	78,948.66	207,395.87	12	342,952.00	NO
216	30	SINGLE	48,099.55	246,550.25	12	426,982.14	NO
217	35	MARRIED	208,312.70	591,780.25	12	958,162.12	NO
218	39	MARRIED	112,866.23	344,698.35	24	474,304.92	YES
219	41	SINGLE	89,078.91	177,387.49	12	332,414.80	NO
220	36	SINGLE	76,229.48	245,567.96	12	430,302.26	NO
221	31	SINGLE	87,092.64	209,624.40	24	398,832.92	YES
222	37	MARRIED	100,940.65	269,966.33	12	465,376.73	NO
223	33	SINGLE	91,531.66	191,454.82	24	262,250.46	YES
224	40	SINGLE	72,183.79	221,950.43	24	458,028.16	YES
225	33	SINGLE	91,082.48	303,495.09	12	550,704.94	NO
226	32	SINGLE	101,358.81	217,013.04	12	359,657.63	NO
227	21	MARRIED	116,147.75	262,175.22	12	421,728.68	NO
228	30	MARRIED	68,756.60	177,865.10	4	187,756.56	YES
229	35	SINGLE	79,109.43	184,137.48	12	315,429.51	NO
230	29	MARRIED	101,185.65	212,461.75	4	230,897.77	YES
231	33	MARRIED	90,360.92	244,723.13	24	336,062.63	YES
232	39	MARRIED	107,973.63	354,041.05	24	601,978.53	YES
233	39	MARRIED	105,755.51	308,012.18	24	374,955.30	YES
234	37	SINGLE	73,652.79	259,017.66	12	447,761.44	NO
235	31	SINGLE	69,572.42	269,188.47	12	615,595.59	NO
236	30	MARRIED	101,575.02	279,844.13	4	288,736.61	YES
237	34	MARRIED	114,262.32	275,926.21	12	499,182.35	NO
238	33	SINGLE	81,684.01	259,677.76	12	516,904.22	NO
239	43	MARRIED	78,106.18	236,226.87	24	336,135.45	YES
240	33	MARRIED	61,397.20	187,161.23	4	222,380.91	YES
241	39	SINGLE	97,196.63	168,044.36	12	286,783.15	NO
242	28	SINGLE	144,576.44	541,450.33	12	815,208.10	NO
243	29	MARRIED	78,413.21	293,925.14	24	552,612.97	YES
244	27	MARRIED	85,507.92	252,220.09	12	522,299.14	NO
245	39	MARRIED	122,156.90	361,361.76	12	630,285.75	NO
246	37	MARRIED	143,533.46	526,365.24	24	841,024.35	YES
247	39	SINGLE	200,758.88	493,625.77	12	850,001.64	NO
248	30	MARRIED	111,937.87	317,038.37	12	577,807.08	NO
249	31	MARRIED	71,519.24	217,019.89	24	278,992.23	YES
250	38	MARRIED	75,329.11	251,061.74	4	332,880.18	YES
251	45	MARRIED	81,947.59	225,575.91	12	395,747.15	NO
252	33	SINGLE	95,232.07	237,096.14	12	476,628.98	NO
253	36	MARRIED	175,219.34	574,438.04	12	961,345.97	NO
254	41	SINGLE	124,093.64	308,394.74	12	522,866.58	NO
255	26	MARRIED	77,475.35	214,101.28	12	388,481.04	NO
256	36	MARRIED	58,418.84	225,758.17	4	270,341.27	YES
257	39	MARRIED	109,265.42	303,961.16	4	350,941.26	YES
258	37	MARRIED	107,562.02	335,223.26	24	596,205.50	YES
259	39	SINGLE	144,719.77	334,685.29	12	573,325.11	NO
260	28	SINGLE	80,112.35	206,094.15	24	261,097.38	YES
261	37	MARRIED	61,196.21	211,659.77	12	413,736.03	NO
262	32	MARRIED	73,274.18	226,347.14	12	414,731.91	NO
263	36	SINGLE	109,038.37	317,606.41	4	372,411.88	YES
264	35	SINGLE	103,710.12	316,466.36	12	544,561.11	NO
265	39	SINGLE	128,352.42	330,784.42	4	335,525.28	YES
266	42	MARRIED	82,048.49	339,738.23	12	595,577.50	NO
267	41	MARRIED	116,734.39	310,763.17	4	325,850.20	YES
268	34	SINGLE	29,529.50	183,429.46	12	323,290.97	NO
269	38	SINGLE	197,832.40	509,975.65	12	760,907.85	NO
270	29	SINGLE	72,006.48	253,121.65	4	325,012.71	YES
271	40	SINGLE	182,258.38	608,929.50	12	1,249,695.52	NO
272	26	MARRIED	191,863.78	517,087.98	12	1,003,049.28	NO
273	38	MARRIED	106,167.42	242,084.45	4	249,291.19	YES
274	22	MARRIED	62,309.95	217,824.42	24	315,203.66	YES
275	35	SINGLE	122,884.53	305,731.32	4	347,881.78	YES
276	34	MARRIED	60,062.20	192,870.18	12	403,345.44	NO
277	34	MARRIED	90,519.96	288,716.89	12	501,489.52	NO
278	45	SINGLE	59,007.20	179,568.69	24	246,187.30	YES
279	37	SINGLE	57,253.88	219,934.23	24	345,784.94	YES
280	31	MARRIED	104,692.29	299,944.04	4	312,998.71	YES
281	32	MARRIED	71,009.62	187,107.45	12	326,577.31	NO
282	41	MARRIED	77,040.65	255,298.11	4	282,781.58	YES
283	32	SINGLE	47,832.94	225,084.27	12	454,431.25	NO
284	30	SINGLE	72,479.68	202,491.18	12	316,951.40	NO
285	39	MARRIED	101,216.79	354,018.93	12	565,592.90	NO
286	26	MARRIED	99,901.18	184,798.63	12	329,437.74	NO
287	38	SINGLE	86,602.20	311,372.47	12	613,509.93	NO
288	33	SINGLE	36,452.22	220,113.74	12	376,178.27	NO
289	32	SINGLE	67,182.27	281,545.92	12	633,614.84	NO
290	38	SINGLE	99,433.65	316,440.45	12	527,965.81	NO
291	43	SINGLE	127,375.75	356,266.99	12	629,610.21	NO
292	35	MARRIED	24,376.62	187,328.95	24	301,014.67	YES
293	36	SINGLE	84,854.45	199,257.21	24	274,100.95	YES
294	32	MARRIED	138,449.24	329,901.47	24	482,704.24	YES
295	37	MARRIED	84,791.08	179,676.63	24	256,361.65	YES
296	33	MARRIED	83,498.89	235,907.50	12	437,145.85	NO
297	41	SINGLE	16,597.53	151,972.20	4	171,289.87	YES
298	30	SINGLE	49,293.95	186,043.13	12	376,694.27	NO
299	35	SINGLE	84,241.80	194,417.84	12	352,597.79	NO
300	31	MARRIED	94,428.15	264,175.55	24	434,102.49	YES

 

PART C: Create a Dashboard from Parts A and B

1. Write the VBA macro to add a new worksheet named “Dashboard” after the “MortgageDefaultDataPivot” worksheet.

2. Using VBA code, reference important findings from Parts A and B on your “Dashboard” worksheet using a combination of charts, tables, messages boxes, and descriptive/summary statistics.

1. Please try to keep your Dashboard limited to the width and height of the computer screen so it is not necessary to scroll too far right or down to review it, but some overlap is fine.

Sales Call Scenarios

Scenario	Morning	Noon	Afternoon	Evening	Profit Generated:
Max profit at current level :	100	100	100	100	$65,098
Shift allocation to generate 32k :	100	9.08	0	100	$32,000
Max profit with comissions of 10% :	100	100	100	73	$54,524

[Download not found]

Download