DMD presentation-NOV8.ppt

MembersofGroup5 PresentationDate Nov 8th 2009 CaseAnalysisGentleLentilRestaurant BetheTopTeaminLingNan tocreatethemostefficient innovative highqualityDMDassignment 1 GentleLentilRestaurant Content BackgroundIntroduction 1 AdvancedAnalysisOfGLRestaurant CrystalBall 3 PreliminaryAnalysisOfGLRestaurant EXCEL 2 Q A 4 2 BackgroundIntroduction SanjayThomas Second yearMBAstudentinMITSloan Ihaveadream GentleLentilRestaurant Gotexcellentofferfromaconsultingfirm 3 BackgroundIntroduction Ifaccepttheoffer Sanjaycanget 80 000forthefirstyear 6 667permonth Ifopenarestaurant Sanjay smonthlysalarywillbeaffectedbythefactorsinbelowtable Table1 FactorsaffectonMonthlysalaryofrestaurant 4 BackgroundIntroduction AnUnusualPartnershipOpportunityfromSanjay saunt GuaranteeSanjayamonthlysalaryofatleast 3500 Table2 Earningdistributionunderapartnership Inordertopayoffhiseducationaldebtobligations Sanjayneedstoearnapproximately 5000permonthbeforetaxes 5 BackgroundIntroduction InordertohelpSanjaymakeabetterdecisionon whetheracceptofferfromtopconsultingfirm OropenGentleLentilhimself Orbuildpartnershipwithhisaunt WewilldofollowinganalysisLetwelcomeournextlecturerloudly ForeverLiu 6 PreliminaryAnalysis EXCEL GeneraloverviewSanjay sChoice ConsultingJob 80K year 6667 month deterministic GLrestaurant uncertaintyrisks Proprietorship Partnership 3 5Kminimumprofit monthbutkeeps90 ofprofitsover 9K Sanjay sminimumearnings 5000 month 7 Thedeterministicanduncertaindatainthecase DeterministicdataU Rent Equipment Utilities Insurance Othercosts 3995S Costofmeal 11Uncertain random data M Numberofmealssoldinamonth N u 3000 1000 L Laborcosts uniformbetween 5040and 6860 P price meal discretedistribution thetablebelow PreliminaryAnalysis EXCEL 8 PreliminaryAnalysis EXCEL P price meal discretedistributionPDFgiven 9 PreliminaryAnalysis EXCEL SimultaingGL sMonthlyEarnings X M numberofmealssold obeystheprobabilitydistributionN 3000 1000 P pricepermeal obeysthediscretePDFgiven L laborcost obeystheuniformdistributionintherange 5040 6860 ComputethemonthlyearningsX X P 11 M L 3995 10 PreliminaryAnalysis EXCEL TheinformationwewanttoknowaboutX TheexpectedvalueandSDofX E X SD X P X 5000 Sanjay sminimumearningsrequirement P X 6667 comparetoconsultingjob 11 PreliminaryAnalysis EXCEL ComputethemonthlyearningsXNOW X P 11 M L 3995 IntheEXCELspreadsheet weuseRandomnumbergeneratorstogenerateasequenceofdifferentnumberseachofwhichisindependentoftheother ThengeneraterandomvaluesofP L Maccordingtotheirrespectivedistributions Thenwewillrunitforn 1000 12 PreliminaryAnalysis EXCEL X P 11 M L 3995 13 PreliminaryAnalysis EXCEL Fromthespreadsheetweobtain Estimating P X 5000 59 1000 5 9 P X 6667 906 1000 90 6 14 AdvancedAnalysisOfGL CrystalBall SimulationofMonthlyEarningsatGLusingCrystalBall AninputrandomvariableismodeledinCrystalBallasan assumption cellForeachassumptioncell theusermustchoosethetypeofdistributionandtheparametersTheoutputquantityweareinterestedinismodeledas forecast cell Crystalballwillautomaticallycompute ThehistogramSamplemeanSamplestandarddeviation andAllsortsofotherveryusefulinformationforthecell 15 SampleN 10000 SetassumptionwithCrystalBall MonthlyEarnings f x P 11 M L 3995 AdvancedAnalysisOfGL CrystalBall 16 AdvancedAnalysisOfGL CrystalBall MonthlyEarningswithoutpartnershipf x P 11 M L 3995samplen 10000 E x 10878 61SD 8 664 58 17 AdvancedAnalysisOfGL CrystalBall SanjayThomasshouldearnAtleast 5 000 00permonth TheprobabilitythatSanjaywillearnlessthan 5 000 00inagivenmonthisP X 5000 27 21 18 AdvancedAnalysisOfGL CrystalBall TheprobabilitythatSanjaywillearnmorethan 6 667 00inconsultingisP X 6667 65 26 19 AdvancedAnalysisOfGL CrystalBall InsteadoflookingjustatmonthlyearningsatGentleLentil itwouldbebettertolookatannualearnings Someassumptionfortheannualmodel Thereare12distinctrandomvariablesM1 M2 M12forthe12distinctnumberofmealsservedineachmonth TherandomvariableP priceofmeal andL laborcost remainsthesameovertheentireyearThengeneraterandomvaluesofP L andM1 M2 M12accordingtotheirrespectivedistributions Calculatethemonthlyearnings formonthi Xi P 11 Mi L 3995 fori 1 2 12A annualearnings X1 X2 X12 20 AdvancedAnalysisOfGL CrystalBall Xi P 11 Mi L 3995 fori 1 2 12A annualearnings X1 X2 X12 21 AdvancedAnalysisOfGL CrystalBall AnnualearningswithoutpartnershipXi P 11 Mi L 3995 fori 1 2 12A annualearnings X1 X2 X12Samplen 10000 E A 131225 96SD 64094 14 22 AdvancedAnalysisOfGL CrystalBall SanjayThomasshouldearnAtleast 60 000 00peryear TheprobabilitythatSanjaywillearnlessthan 60 000isP X 60000 15 20 23 AdvancedAnalysisOfGL CrystalBall Consultingcompanyannualsalaryis 80 000 00TheprobabilitythatSanjaywillearnmorethan 80 000 00isP X 80000 74 55 24 AdvancedAnalysisOfGL CrystalBall SummaryTableofSomeRelevantEstimates 25 AdvancedAnalysisOfGL CrystalBall Considerthepartnership Monthlyprofitwithpartnership IF X 3500 3500 IF X 9000 X 9000 0 1 X 9000 Annualprofitwithpartnership Sum Jan Feb Dec 26 AdvancedAnalysisOfGL CrystalBall Considerthepartnership Estimateofmean 91910 68EstimateofSD 19721 18 27 AdvancedAnalysisOfGL CrystalBall SanjayThomasshouldearnAtleast 60 000 00peryear TheprobabilitythatSanjaywillearnlessthan 60 000isP X 60000 9 15 Considerthepartnership 28 AdvancedAnalysisOfGL CrystalBall Consultingcompanyannualsalaryis 80 000 00TheprobabilitythatSanjaywillearnmorethan 80 000 00isP X 80000 72 29 Considerthepartnership 29 AdvancedAnalysisOfGL CrystalBall SummaryTableofSomeRelevantEstimates 30 AdvancedAnalysisOfGL CrystalBall Comparison ProprietorshipversusPartnership AnnualprofitwithProprietorship AnnualprofitwithPartnership 31 AdvancedAnalysisOfGL CrystalBall Comparison Proprietorshipversus