Spreadsheets for Business Questions And Answers

Spreadsheets for Business Questions
And Answers
Assumetthattyoutaretbuyingtatcartfort$23,500twithtat$3,000tdowntpayment,tandtyoutaretborro
wingtthetresttfromtatbanktatt6.0%tannualtinteresttcompoundedtmonthly.tYourtmonthlytpaym
enttist$370.tWritetantExceltformulattotdeterminetthetnumbertoftyearstittwillttaketyouttotpaytofftt
histloan.t-tCORRECTttANSWER-=NPER(.06/12,-370,23500-3000)/12

Assumetthattyoutaretinvestingt$6000tintatsavingstplanttodaytandtwilltmaketadditionaltcontrib
utionstoft$200tpertquarter.tThetplantpayst5%tinteresttpertyeartcompoundedtquarterly.tWritet
antExceltformulattotdeterminethowtmuchtyourtsavingstwilltbetworthtintfivetyears.t-
tCORRECTttANSWER-=FV(.05/4,4*5,-200,-6000)




Assumetthattyouthavetbeentlefttantinheritancetandtwouldtlikettotsavetparttoftitttowardstthetpur
chasetoftatcartupontgraduation,twhichtistthreetyearstfromtnow.tWritetantExceltformulattotdete
rminetthetamounttoftmoneytyoutneedttotinvesttnowttothavet$15,000tattthetendtoftthetthree-
yeartperiod.tAssumetthattyoutwilltplacetthistmoneytintatCDtthattpayst3%tinteresttcompounde
dtquarterlytandtthattyoutwilltbetmakingtnotadditionaltdepositstintotthistaccount.t-
tCORRECTttANSWER-=PV(.03/4,3*4,0,15000)




Comparetbreak-eventanalysistandtsensitivitytanalysis.t-tCORRECTttANSWER-Break-
eventanalysististattypetoftwhat-
iftanalysistthattconcentratestontantactivitytattortaroundtthetpointtattwhichtthetexpensestoftcrea
tingtandtsellingtatproducttaretequalttotthetrevenuetittproduces,tortwheretthetprofittist$0.tSensit
ivitytanalysististanotherttypetoftwhat-
iftanalysistthattattemptsttotexaminethowtsensitivetthetresultstoftantanalysistarettotchangestintt
hetassumptions.tFortexample,tatsensitivitytanalysistasks,thowtmuchtwilltprojectedtprofitstch
angetiftthetcosttoftgoodstsoldtpertunittchangestbyt$1?tByt$2?

Considertat$100,000tmortgagetatt5%tannualtinteresttcompoundedtmonthly,ttotbetpaidtbackt
overtthetnextt15tyears.tThetloantwillthavetat$5,000tballoontpaymenttduetattthetendtoftthetloan
.tWritetantExceltformulattotdeterminetthetpaymenttthattmusttbetmadeteachtmonthtontthistloan
.t-tCORRECTttANSWER-=PMT(.05/12,15*12,100000,-5000)

DescribethowtthetSUMPRODUCTtfunctiontworks.t-tCORRECTttANSWER-
ThetSUMPRODUCTtfunctiontsumstatseriestoftproductst(thetresulttoftatmultiplicationttask)tint
rangestoftidenticaltsizes,tcalledtarraystthattaretarrangedtintthetsametorientationttoteachtother
tintatworksheet.

, DescribetthetstepstfortsavingtatSolvertmodel.tWhattistthetadvantagetoftsavingtatSolvertmodel
?t-tCORRECTttANSWER-
1.tTotsavetatSolvertmodel,tclicktthetSolvertbuttontintthetAnalysistgrouptontthetDatattabtontthet
Ribbon.tThetSolvertParameterstdialogtboxtopens.
2.tClicktthetLoad/SavetbuttontintthetSolvertParameterstdialogtbox.tThetLoad/SavetModeltdia
logtboxtopens.
3.tSelecttantemptytrangetintthetworksheettwithtthetnumbertoftcellstlistedtintthetdialogtbox.
4.tClicktthetSavetbuttonttotclosetthetLoad/SavetModeltdialogtboxtandttotsavetthetmodeltintthet
worksheet.
5.tClicktthetClosetbuttonttotclosetthetSolvertParameterstdialogtbox.
SavingtatSolvertmodeltistdifferenttfromtsavingtatSolvertscenario,twhichtsavestonlytthetresultt
oftatSolvertmodel.tSavingtatSolvertmodeltsavestthetSolvertparameterstthattweretusedtintthet
Solvertmodeltsotyoutcantloadtthemtlatertiftnecessary.

DescribetthetstepstyoutwouldttakettotimporttXMLtdatatastantXMLttabletintExcel.t-
tCORRECTttANSWER-1.tOpentthetworksheettintotwhichtyoutwantttotimporttthetdata.

2.tSelecttthetfirsttblanktcelltwheretyoutwanttthetimportedtdatattotappear,tclicktthetFromtOthert
SourcestbuttontintthetGettExternaltDatatgrouptontthetDatattabtontthetRibbon,tandtthentselectt
FromtXMLtDatatImport.tThetSelecttDatatSourcetdialogtboxtopens.
3.tBrowsettotthetfoldertthattcontainstthetXMLtdocumenttyoutwantttotimport.
4.tClicktthetXMLtdocument,tandtthentclicktthetOpentbutton.tIftthetXMLtdocumenttdoestnottref
erttotatschema,tatmessagetboxtopensttottelltyoutthattExceltwilltcreatetatschematbasedtontthet
XMLtsourcetdata.
5.tClicktthetOKtbuttonttotclosetthetmessagetbox.tThetImporttDatatdialogtboxtopens.
6.tClicktthetoptiontbuttonttotimporttthetdatatintthetexistingtworksheettortintatnewtworksheet,ta
ndtthentclicktthetOKtbuttonttotimporttthetdata.

Exceltmatchestthetlookup_valuet"tom"twithtthetentryt"TOM"tintatlookupttablet-
tCORRECTttANSWER-True.




Explainthowtyoutmusttvarytthetsetuptoftattwo-variabletdatattablettotruntatsimulation.t-
tCORRECTttANSWER-Whentsettingtuptattwo-

variabletdatattable,tittmusttbetstructuredtsotthattthettwotsetstoftinputtvaluestaretperpendiculart
toteachtother,tandtthettable'stoutputtformulatistlocatedtattthetintersectiontoftthettwotsetstoftinp
uttvariables.tWhentusingtattwo-
variabletdatattablettotruntatsimulation,tonetsettoftinputtvaluestintthetdatattable'ststructuretmus
ttbetatsequencetoftnumberstthattrepresentstthetnumbertoftiterationstoftthetsimulation.tOnlyto
netspottremainstintthettable'ststructuretfortatdefinedtsettoftinputtvalues.

ExplaintthetdifferencetbetweentthetlookupttabletintcellstA1:E2toftthetGradestworksheettandtt
hetlookupttabletintcellstA5:E6tintthetsametworksheet.t-tCORRECTttANSWER-
ThetlookupttabletintA1:E2toftthetGradestworksheettistsortedtintdescendingtorder,tandtthetloo
kupttabletintcellstA5:E6tistsortedtintascendingtorder,twhichtallowstyouttotusetthetHLOOKUPtf
unctiontwithtatTRUEtlookupttype.