Case Study Presentations and Discussions

Studentnumber2-CaseStudyProject_Ward-2.xlsx

Home >Mathematics homework help >Case Study Presentations and Discussions

Data

GM Capital
									DMM if account receives a letter Chris Albright: There is no 0-due row because a letter is never sent to a 0-due account.
									From\To	0-due	1-due	2-due	3-due	Bad debt
									1-due	60%	10%	30%	0%	0%
Contact Methods	Time Required (Min):	Time Required (Hrs):							2-due	15%	15%	40%	30%	0%
Phone Call	40	0.67							3-due	0%	0%	20%	60%	20%
Letter	20	0.33							Bad debt	0%	0%	0%	0%	100%
No Contact	0	0.00
	Minutes	Hours
Time Available	300000	5000							DMM if account receives a phone call Chris Albright: There is no 0-due row because a phone call is never made to a 0-due account.
									Chris Albright: There is no 0-due row because a letter is never sent to a 0-due account.	From\To	0-due	1-due	2-due	3-due	Bad debt
									1-due	70%	20%	10%	0%	0%
Average Monthly Payment	$ 10,000.00								2-due	30%	25%	30%	15%	0%
									3-due	0%	0%	50%	40%	10%
No of Accounts									Bad debt	0%	0%	0%	0%	100%
0-Due	40000
1-Due	4000
2-Due	4000								DMM for do-nothing action
3-Due	2000								From\To	0-due	1-due	2-due	3-due	Bad debt
Bad Debt	0								0-due	90%	10%	0%	0%	0%
									1-due	20%	25%	55%	0%	0%
MONTH 1:									2-due	10%	10%	20%	60%	0%
	Send Letter	Phone Call	Do Nothing						3-due	0%	0%	10%	40%	50%
0-Due	0	0	40000	40000	=	40000			Bad debt	0%	0%	0%	0%	100%
1-Due	4000	0	0	4000	=	4000
2-Due	0	3500	500	4000	=	4000			Number of Accounts	0-due	1-due	2-due	3-due	Bad debt
3-Due	0	2000	0	2000	=	2000				40000	4000	4000	2000	0
Bad Debt	0	0	0	0	=	0			Earnings Matrix
Total:	80000	220000	0.00	300000.00	<=	300000			From\To	0-due	1-due	2-due	3-due	Bad debt	Total:
									0-Due	$ 10,000.00	$ - 0	$ - 0	$ - 0	$ - 0	$ 10,000.00
MONTH 2:	Send Letter	Phone Call	Do Nothing						1-due	$ 20,000.00	$ 10,000.00	$ - 0	$ - 0	$ - 0	$ 30,000.00
0-Due	0	0	39500	39500	=	39500			2-due	$ 30,000.00	$ 20,000.00	$ 10,000.00	$ - 0	$ - 0	$ 60,000.00
1-Due	5050	0	275	5325	=	5325			3-due	$ 40,000.00	$ 30,000.00	$ 20,000.00	$ 10,000.00	$ - 0	$ 100,000.00
2-Due	0	3350	0	3350	=	3350			Bad debt	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0
3-Due	0	1625	0	1625	=	1625
Bad Debt	0	0	200	200	=	200
Total:	101000	199000	0	300000	<=	300000
MONTH 3:	Send Letter	Phone Call	Do Nothing
0-Due	0	0	39640	39640	=	39640
1-Due	4995	366	0	5361	=	5361
2-Due	0	3484	0	3484	=	3484
3-Due	0	1153	0	1153	=	1153
Bad Debt	0	0	363	363	=	363
Total:	99900	200100	0	300000	<=	300000
MONTH 4:	Send Letter	Phone Call	Do Nothing
0-Due	0	0	39975	39975	=	39975
1-Due	4096	1312	0	5408	=	5408
2-Due	0	3157	0	3157	=	3157
3-Due	0	984	0	984	=	984
Bad Debt	0	0	478	478	=	478
Total:	81910	218090	0	300000	<=	300000
Revenue
	Send Letter	Phone Call	Do Nothing
0-Due	$ - 0	$ - 0	$ 9,000.00
1-Due	$ 13,000.00	$ 16,000.00	$ 6,500.00
2-Due	$ 11,500.00	$ 17,000.00	$ 7,000.00
3-Due	$ 10,000.00	$ 14,000.00	$ 6,000.00
Bad Debt	$ - 0	$ - 0	$ - 0
Total:				$ 2,009,988,625.00
Alternate Strategy (No Contact)		M1		M2		M3		M4
0-Due	40000	$ 360,000,000.00	37200	$ 334,800,000.00	34880	$ 313,920,000.00	32863	$ 295,767,000.00
1-Due	4000	$ 26,000,000.00	5400	$ 35,100,000.00	5390	$ 35,035,000.00	5228.5	$ 33,985,250.00
2-Due	4000	$ 28,000,000.00	3200	$ 22,400,000.00	3930	$ 27,510,000.00	4070.5	$ 28,493,500.00
3-Due	2000	$ - 0	3200	$ 19,200,000.00	3200	$ 19,200,000.00	3638	$ 21,828,000.00
Bad Debt	0		1000	$ - 0	2600	$ - 0	4200	$ - 0
Total:		$ 414,000,000.00	50000	$ 411,500,000.00	50000	$ 395,665,000.00	50000	$ 380,073,750.00	$ 1,601,238,750.00
Difference between initial contact strategy and alternate, no contact strategy:				$ 408,749,875.00

Data_STS

1	1
$C$11	$B$7
1	1
5000	5
6000	40
100	5

$E$65	$B$8
Hours Available	1
	5
	20
	5

	$E$65
	Time Required (Phone Call)
	Time Required (Letter)

STS_1

Oneway analysis for Solver model in Data worksheet			Sensitivity of Total_Revenue to Hours Available
Hours Available (cell $C$11) values along side, output cell(s) along top			Data for chart
	Total_Revenue	1	Total_Revenue
5000	$ 2,009,988,625.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,009,988,625.00	DIFFERENCE:
5100	$ 2,015,045,935.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,015,045,935.00	$ 5,057,310.00
5200	$ 2,019,240,100.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,019,240,100.00	$ 4,194,165.00
5300	$ 2,023,254,775.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,023,254,775.00	$ 4,014,675.00
5400	$ 2,026,730,200.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,026,730,200.00	$ 3,475,425.00
5500	$ 2,029,936,000.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,029,936,000.00	$ 3,205,800.00
5600	$ 2,033,141,800.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,033,141,800.00	$ 3,205,800.00
5700	$ 2,036,347,600.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,036,347,600.00	$ 3,205,800.00
5800	$ 2,039,416,600.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,039,416,600.00	$ 3,069,000.00
5900	$ 2,041,142,800.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,041,142,800.00	$ 1,726,200.00
6000	$ 2,042,869,000.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,042,869,000.00	$ 1,726,200.00
NOTE:	The biggest revenue increase happens with an increase of 100 hours; after 100 hours, the revenue increases but to a lesser degree.

Sensitivity of Total_Revenue to Hours Available

5000 5100 5200 5300 5400 5500 5600 5700 5800 5900 6000 2009988625 2015045935 2019240100 2023254775 2026730 200 2029936000 2033141800 2036347600 2039416600 2041142800 2042869000

Hours Available ($C$11)

When you select an output from the dropdown list in cell $K$4, the chart will adapt to that output.

STS_2

Twoway analysis for Solver model in Data worksheet

Sensitivity of Total_Revenue to Time Required (Letter)

Sensitivity of Total_Revenue to Time Required (Phone Call)

Output and Time Required (Phone Call) value for chart

Output and Time Required (Letter) value for chart

Total_Revenue

Time Required (Phone Call) (cell $B$7) values along side, Time Required (Letter) (cell $B$8) values along top, output cell in corner

Output

Time Required (Phone Call) value

Output

Time Required (Letter) value

Total_Revenue