What-if and Goal-seeking analysis

CSE4DSS – Decision Support Systems Individual Assignment, 2015 Due$Date: Monday"4th May,"9:30am, 2015 Assessment$Weight: 30% of"the"final"mark"for"the"subject Instructions • This"is"an"INDIVIDUAL assignment."You"are"not"permitted"to"collaborate"with"any"other"student," and"you"are"not"permitted"to"outsource"the"work"to"any"other"party." • This"assignment"consists of"three separate"problems."You"are"required"to"solve all"three problems. • Total"marks"available"for"the"assignment"is"80:"Problem 1"carries"20"marks;"Problem 2"and"3" carry 30"marks each. Plagiarism Plagiarism"is"the"submission"of"somebody"else’s"work"in"a"manner"that"gives"the"impression"that"the" work"is"your"own."Software"will"be"used"to"assist"in"the"detection"of"plagiarism."Students"are" referred"to"the"section"on"‘Academic"Misconduct’"in"the"subject’s"guideline"available"on"LMS. When"submitting"assignments"via"the"LMS,"the"following"announcement"will"appear. Lateness Policy Penalties"are"applied" to" late"assignments" (5%" of" total" possible"marks" for" the" task" is" deducted" per" day," accepted" up" to" 5" days" after" the" due" date" only)." An" assignment" submitted" more" than" five" working"days"after"the"due"date"will"not"be"accepted. Submission Procedure Assignments"are"to"be"submitted"electronically"using"the"link"on"the"subject’s"LMS"page."You"should" submit"only"one"single"document:"a"Microsoft"Word"format"file."This"file"should"contain"a"cover" sheet"containing"the"information described"in"Appendix"A"of"this"document,"followed"by"your" solutions"to"Problems"1"to"3. A"description"of"what"you"are"required"to"submit"for"each"problem"is" given"below,"at"the"end"of"each"problem"description. Once"again,"please"note"that"this"is"an" individual"assignment."You"are"not"permitted"to"collaborate"with"any"other"student,"and"you"are"not" permitted"to"outsource"the"work"to"any"other"party."Software"will"be"used"to"assist"in"the"detection" of"plagiarism. Problem 1: What-if and Goal-seeking analysis (20 marks) Rama is"developing"a"business"plan"and"an"application"for"financing"for"a"new"lawnVmowing business."She intends"to"run"the"service"for"4 years,"and"then"dissolve"it."She has"developed"the" following"information"on"various"aspects"of"the"business. Vehicle costs Rama plans"on"buying"three new"Toyota Utes for"the"business for"a"net"cost"of"$25,000"each."The" resale"value"for"a"Ute after"4"years"is"estimated"at"$10,000."The"following"describes"the"annual" operating"costs"for"each vehicle:" • Annual"insurance"and"registration"costs:"$2,000 • Maintenance and"repairs:"$1000"in"the"first"year,"and"increases"by"$400"in"each"subsequent" year." Lawn$mowers In"addition"to"vehicles,"Rama must"purchase"three"lawn"mowers"for"$1,500"each. They are"expected" to"last"4"years,"but"will"have"negligible"(zero)"resale"value."Maintaining"and"repairing"each lawn" mower"is"estimated"at"$300"per"year. Fuel$costs Both"the"vehicles"and"lawn"mowers"utilise"regular"unleaded"petrol."The"estimated"fuel"costs"are: • $10"per"job"for"round"trips"to"the"job"location • $4 per"job"for"operating"each"lawn"mower Labour Three"people"are"employed"at"rate"of"$22"per"hour"and"work"according"to"demand."Each"oneVperson" mowing"job"typically"requires"2.5"hours"of"labour"including"travel"time."Business"overheads"(office," phone,"etc.)"are"estimated"to"be 20%"of"the"lawnVmowing labour"costs."Rama will"be"the"manager," with"an"annual"salary"of"$55,000. Demand The"demand"for"service"is"expected"to"be"around"2000 jobs"per"year."The"fee"per"oneVperson"job"is $130. Rama’s"plan"is"to"borrow"the"$80,000"required"to"purchase"the"vehicles"and"other"equipment. Tasks: a) Based"on"the"above"information,"develop"a"model"using"an"Excel"spreadsheet"to"project"financial" statements"for"Rama’s"Lawn"Mowing. The"bank"usually"wishes"to"see"the"following"components" for"financial"performance"on"an"annual"basis"and"over"the"4Vyear"period:"total"revenue,"total" costs,"and"cash"flow."The"cash"flow"should"be"used"to"calculate"the"net"present"value"of"the"4V year"investment."(The"net"present"value"of"an"investment"is"the"value"of"the"investment"in" ‘today’s"money’)."The"builtVin"EXCEL"function"NPV"can"be"used"to"calculate"net"present"value." Use"a"discount"rate"of"12%. b) Now"perform"some"whatVif"analysis"to"see"what"happens"to"the"net"present"value"if: i) Fuel"costs"increase"by 20%. ii) Expected"demand"is"only"1500 jobs"per"year. c) Use"goal"seeking"to"find"how"many"jobs"per"year"would"be"required"for"Rama’s"company"to" break"even;"i.e.,"to"achieve"a"net"present"value"of"$0.00. What$to$submit: A"wordVprocessed"written"report"of"approximately"200"words"that"describes"how"you"solved"the" problem, and"presents"your"answers to"the"tasks above."Clearly"label"each"task"a)"to"c)."The"report" should"also"include the"following: a) Screen"captures of"your"spreadsheet,"with"and"without"formulas"showing (see"Appendix"B"for" directions on"how"to"display"formulas"in"Excel)"prior"to"the"whatVif"and"goal"search"exercises," showing"the"expected"NPV. b) Screen"captures of"your"spreadsheet (without formulas)"for"each"whatVif"analysis"i)"and"ii). c) A"screen"capture"of"your"spreadsheet (without"formulas)"for"the"goal"search"task."Include"the" Goal"Seek"popVup"window"with"appropriate"cell"references"in"your"screen"capture. Note:"Please"ensure"your"screen"captures"are"readable"and"show"the"row"and"column"headings"(see" Appendix"B). Marking$criteria: • Completeness"of"your"solution.""Have%all%questions%been%answered?%Have%all%tasks%been% performed? • Correctness"of"your"solution.""Has%your%solution%been%implemented%correctly?%Does%it%give the% correct%results?%Are%all%your%assumptions%correct? • Clarity"of"your"report.""Is%it%clear%how%you%arrived%at%your%solutions?% • Design"of"your"spreadsheet."Is%the%design,%presentation%and%formatting%of%the%spreadsheet% sufficiently%clear%such%that%the%model%it%presents%can%be%understood%by%a%person%with%a%basic% knowledge%of%Excel,%and%without%the%need%for%any%additional%information?%Is%your% spreadsheet%designed%in%such%a%way%as%to%allow%Rama to%easily%experiment%with%the%various% parameters?%Is%it%intuitive%and%easy%to%use? Marking$grid: Task Criteria Marks a) Spreadsheet"model"contains"all"required"problem"information 5 Spreadsheet"model"performs"correct"initial"calculations"(formulas) 5 Spreadsheet"model"has"clear"design,"presentation,"and"formatting" 3 Spreadsheet"model"allows"the"user"to"easily"experiment 2 b) i) Correct"solution"obtained 1 ii) Correct"solution"obtained 1 c) Goal"seek"window"utilises"correct"cell"references"and"value 3 Total 20 Problem 2: Optimization and Simulation (30 marks) Part$I Jonah"has"an"opportunity"to"import"and"sell"a"new"iPod"docking"hi"fi"system:"the"Überdock"1."He" plans"to"sell"the"units"at"a"profit"on"eBay."He"has"set"up"a"business"model"using"an Excel"spreadsheet," but"does"not"have"the"skills"to"create"formulas"or"to"use"the"solving capabilities."Jonah"asks"for"your" help"with"completing"the"model"and"using"it"to"help"him"plan"his"new"business."The"spreadsheet"he" gives"you: “Uberdock"– Part"I.xls”"(available"via"LMS); which"he"calculated"manually,"outlines"the" details"of"the"problem: Product$information The"Überdock"1"can"be"imported"in"boxes"of"24 at"a"price"of"$1,200.00"per"box."Jonah"has"family" connections"with"the"supplier"who"has"granted"him"a"twoVmonth"grace"period"for"paying"invoices." This"enables"him"to"have"substantial"initial"stock. Other$expenses To"import"the"goods,"Jona"will"need"to"pay"import"tax"at"a"rate"of"15.0% of"his"purchase"price."His" shipping"costs"are"$250.00"per"box."Each"eBay"sale"attracts"a"10%"commission"subtracted"from"the" selling"price."Jona"needs"to"consider"these"expenses"when"deciding"the"selling"price. Market$demand$and$share Jonah"has"observed"that"the"monthly"demand"for"similar"products"is"approximately"800."He"has"also" noticed"that"any"given"seller’s"market"share"is"approximated"by"the"equation: %"market"share"="120"– selling"price Thus,"for"example,"when"selling"the"product"for"$100"you"would"expect"to"sell"approximately"20%"of" the"800"(160)"units"per month"(providing"you"have"sufficient"stock). Total$cost$for$units$sold,$income and$profit Each"month"Jonah"will"sell"his"market"share"provided"he"has"sufficient"stock."His"profit"for"the"month" will"be"his"total"sales"(less"eBay"commission)"minus"how"much"he"spent"on"the"importing"the"units" sold. Part$I$Tasks: a) Using"the"spreadsheet"Jonah"provided"(Uberdock"– Part"I.xls),"without'changing'the'layout," implement"the"formulas"with"appropriate"cell"references"so"that"Jonah"can"perform"useful"whatV if"analysis."He"may"want"to"alter"any"combination"of:"product"information,"other"expenses,"sell" price,"and market"demand;"without"needing"to"manually"reVcalculate"other"components"of"the" model. Excel%hint for%this%task: • Utilise"the"INT"function"to"ensure"calculated"numbers"are"integers"where"appropriate. b) Using"solver,"find"the"selling"price"that will"maximise"the"profit in"any month"of"business," assuming"there"is"sufficient"stock. Was%it%necessary%to%add%constraints%to%the%solver?%Why%not? c) Jonah"would"now"like"for"you"to"help"him"determine"the"amount"of"stock"that"he"should" maintain,"so"that"he"rarely"misses"sales"opportunities"as"demand"fluctuates."To"achieve"this"aim," run"a"5Vyear"(60"month)"simulation"using"a"normally"distributed"demand"with"a"mean"of"800"and" a"standard"deviation"of"150."Use"the"optimal"selling"price"found"in"task b)."Summarise"your" findings"in"a"table"containing"the"mean,"standard"deviation,"minimum"and"maximum"of"the" number"of"units"sold."Estimate"a"reasonable"level"of"stock"to"maintain"from"your"results. Hint:"The"chance"of"obtaining"a"value"from"a"normal"distribution"two"standard"deviations"or" more"above"the"mean"approximately"0.025."Given%this%estimate,%how%many%boxes%should%Jonah% order%to%start%his%business? Part$II Jonah"has"utilised"your"spreadsheet"model"to"improve"his"business"plan."Because"shipping"of"goods" will"take"one"month,"Jonah"can"only"spend"$10,000"on"his"initial"stock,"after"which"he"can"build" toward"the"optimal"stock"level"you"recommended."He"has"also"decided"to"only"sell"the"product"for" six"months"before"trying"another."He"reVdesigns"his"spreadsheet"model"to"include"the"import"budget" and"costs,"and"extends"the"relevant"sections"across"6"months."Jonah"has"learnt"from"your"model"and" his"updated"model:"“Uberdock"– Part"II.xls”"(also"available"via"LMS);"contains"the"necessary"formulas. Part$II Tasks: d) Jonah"has"derived how"many"boxes"to"import"for"the"first"5"months."He"also"has"decided"on"a" selling"price"of"$97"(rounding"the"price"you"recommended)"for"months"2V6."He"would"like"your" help"to"use"Solver"to"find"the"optimal"selling"price"for"the"first"month (1)"and"the"number"of" boxes"to"order"in"the"last"month"(6)"so"as"to"maximise"the"total"profit"for"the"entire"selling" period"of"6"months. Note"that"you"can"only"import"whole"numbers"of"boxes. Also"make"sure"that" the"import"costs"stay"within"the"spending"budgets. Solver%hints%for%this%task: • It"is"possible"to"add"an"integer"(whole"number)"constraint"within"the"solver. • As"well"as"defining"ranges"(eg."$C$10:$F$10), the"“By"Changing"Variable"Cells”"field"can"list" multiple"cells"separated"by"commas"(eg."$C$10,$C$20). What%is%the%maximum%total%expected%profit? e) Finally,"Jonah"would"like"to"know"how"fluctuations"in"demand"affect"the"model."Use a"normally" distributed"demand"with"a"mean"of"800"and"a"standard"deviation"of"150:" NORMINV(RAND(),800,150);"for"each"month."Run"20"simulations"of"the"model."Summarise"the" mean,"standard"deviation,"minimum"and"maximum"for"total"profit"in"a"table. Discuss%your%results. Are%they%what%you%expected%to%find?%Why? What$to$submit: A"wordVprocessed"written"report"of"approximately"500"words"that"describes"how"you"solved"the" problem, and"presents"your"answers to"the"tasks"above."Clearly"label"each"task"a)"to"e)."The"report" should"also"include the"following: a) Screen"captures of"your"spreadsheet with formulas"(see"Appendix"B"for"directions"on"how"to" display"formulas"in"Excel)"prior"to"the"solver"and"simulation exercises. b) A"screen"capture"of"your"spreadsheet (without formulas)"for"the"solver task solution."Include"the" Solver popVup"window with"appropriate"cell"references in"your"screen"capture. c) A"partial"screen"capture"of"your"spreadsheet"(with formulas)"showing"the"formulas"for"the" normally"generated"demand,"the"summary"statistics"listed"in"your"table,"and"the"calculations used"for"your"estimates"of"stock"levels"and"boxes"to"order. You"do"not"need"to"show"all"60" simulation"results. d) A"screen"capture"of"your"spreadsheet (without formulas)"for"the"solver"task"solution."Include"the" Solver"popVup"window with"appropriate"cell"references"in"your"screen"capture. e) A"screen"capture"of"your"spreadsheet"(without formulas)"showing"the"last"simulation"run"of"your" model,"the"20"simulation"results,"and"the"summary"statistics"listed"in"your"table. Note:"Please"ensure"your"screen"captures"are"readable"and"show"the"row"and"column"headings (see" Appendix"B). Marking$criteria: • Completeness"of"your"solution.""Have%all%questions%been%answered?%Have%all%tasks%been% performed? • Correctness"of"your"solution.""Has%your%solution%been%implemented%correctly?%Does%it%give%the% correct%results?%Are%all%your%assumptions%correct? • Clarity"of"your"report.""Is%it%clear%how%you%arrived%at%your%solutions?% • Logic"of"the"modified"spreadsheet."Is%your%spreadsheet%designed%in%such%a%way%as%to%allow% Jonah to%easily%experiment%with%the%various%parameters?%Is%it%intuitive%and%easy%to%use? Marking$grid: Task Criteria Marks a) Spreadsheet"model"performs"correct"initial"calculations"(formulas) 5 Spreadsheet"model"allows"the"user"to"easily"experiment 3 Spreadsheet"model"uses"the"INT"function"where"appropriate 1 b) Solver"uses"correct"cell"references"to"obtain"the"correct"solution"to"the"problem 2 Reasonable"explanation"for"not"needing"constraints"in"solver 2 c) Normally"distributed"demand"correctly"generated 1 Appropriate"mean,"standard"deviation,"minimum"and"maximum"simulation" values"obtained 4 Appropriate"estimates"of"stock"level"and"initial"boxes"to"import"calculated 2 d) Solver"uses"correct"cell"references"and"constraints"to"obtain"the"correct"solution" to"the"problem 4 e) Appropriate"mean,"standard"deviation,"minimum"and"maximum"simulation" values"obtained 4 Reasonable"brief"discussion"of"results 2 Total 30 Problem 3: Mining a Medical Diagnosis dataset (30 marks) A"medical"research"centre"has"invited"you"to"build"a"classifier"for"diagnosing"diabetes based"on" historical"data."You"are"asked"to"use"the"WEKA"dataVmining"tool"to"compare"data"mining"algorithms and"recommend a"suitable"classifier"for"the"problem"domain. You"will"need"to"provide"a"report"of" your"findings,"including"justification"for"your"recommendation. Tasks: a) The"historical"data"for"diabetes"diagnosis"has"already"been"compiled"into"a"WEKA"data"file:" “diabetes.arff”"(available"via"LMS)."Load"this"file"into"WEKA"and"answer"the"following"questions" about"the"data: i) How%many%features%or%attributes%does%the%data%contain? ii) How%many%examples%does%the%data%contain? iii) What%is%the%name%of%the%attribute%that%describes%whether%or%not%the%example%is%diagnosed% with%diabetes? iv) How%many%examples%are%diagnosed%with%diabetes? b) Which%of%accuracy,%precision%or%recall%do%you%think%is%the%more%important%measure%of% performance%for%this%problem?%Why? c) Utilise"7VfoldVcrossVvalidation"to"compare"the"performance"of"the"following"classifiers: • J48"decision"tree • Multilayer"Perceptron"(MLP) • Naïve"Bayes Use"the"default"settings/architecture for"each"classifier. Present"the"following"results"for"each" classifier: i) The"confusion"matrix (using the"format"below:"note"that"WEKA"may"present this"differently) True"Class Positive Negative Predicted" Class Positive (TP) (FP) Negative (FN) (TN) ii) The"accuracy"measure iii) The"precision"measure iv) The"recall"measure d) Recommend"one"of"the"three"classifiers for"this"problem."Justify"your"answer. Given%your%results,%would%you%suggest%the%diagnosis%procedure%rely%solely%on%the%classifier? What$to$submit: A"wordVprocessed"written"report"of"approximately"300"words"that"describes"how"you"solved"the" problem, and"presents"your"answers to"the"tasks"above."Clearly"label"each"task"a)"to"d)."The"report" should"also"include the"following: a) A"screen"capture"of"the"WEKA"Preprocess"tab"showing"a"bar"graph"of"the"proportions"of"positive" and"negative"examples"for"the"diabetes"diagnosis. b) A"clearly"justified"answer"to"the"question. c) A"screen"capture"of"the"WEKA"Classify"tab"showing"the"correct"test"option"and"three"result" items:"one"for"each"classifier"(with"either"one"selected). As"well"as"confusion"tables"in"the" provided"format"and"calculated"measure"values"for"each"classifier. d) A"clearly"justified"recommendation"and"answer"to"the"question. Note: Please"ensure"your"screen"captures"are"readable"and"show"the"row"and"column"headings"(see" Appendix"B). Marking$criteria: • Completeness"of"your"solution.""Have%all%questions%been%answered?%Have%all%tasks%been% performed? • Correctness"of"your"solution.""Has%your%solution%been%implemented%correctly?%Does%it%give%the% correct%results?%Are%all%your%assumptions%correct? • Clarity"of"your"report.""Is%it%clear%how%you%arrived%at%your%solutions?% • Logic"of"the"modified"spreadsheet."Is%your%spreadsheet designed%in%such%a%way%as%to%allow% Jonah to%easily%experiment%with%the%various%parameters?%Is%it%intuitive%and%easy%to%use? Marking$grid: Task Criteria Marks a) i) Correct"number"of"attributes"identified"in"the"report 1 ii) Correct"number"of"examples"identified"in"the"report 1 iii) Correct"attribute"name"identified"in"the"report 1 iv) Correct"number"of"positive"examples"identified"in"the"report 1 Answers"to"i)"to"iv)"are"consistent"with"the"screen"capture 2 b) i) Appropriate"performance"measure"identified 1 ii) Reasonable"explanation"is"provided"in"the"report 1 c) Correct"test"option"and"all"three"classifiers"evident"from"screen"capture 4 i) Correct"confusion"matrix"provided"for"each"of"the"three"classifiers 6 ii) Correctly"calculated"accuracy"provided"for"each"of"the"three"classifiers 3 iii) Correctly"calculated"precision"provided"for"each"of"the"three"classifiers 3 iv) Correctly"calculated"recall"provided"for"each"of"the"three"classifiers 3 d) Appropriate"recommendation"given"result"values 1 Reasonable"justification"for"recommended"classifier 1 Reasonable"suggestion"for"classifier"use 1 Total 30 Appendix A: Assignment cover sheet CSE4DSS – Decision Support Systems 2015 Assignment Cover Sheet Surname: (Your surname) Given Name: (Your given name) Student ID: (Your student ID number) Lab Group: (Your lab session time ; e.g., Monday 11am – 1pm) Appendix B: Displaying Formulas in an Excel Spreadsheet • Select"Show$Formulas from"the"Formulas"tab" • Any formulas"contained"in"your"spreadsheet"will"now"be"visible"in"those"cells. • Ensure"that"any screen"captures"displaying"formulas"also"show"row"and"column"headings