综合ag bm 407实验室部分crmlab.pdfVIP

AgBm407

Lab#6

Part1CRM

1.Fillintheappropriatevalues.Ideallyyoushouldrefertotheotherworksheets,forexample:

Inworksheet‘crm’startincellc2andenteran=thenrefertosheet‘income’andselectc2fromthat

sheet,ultimatelywewantthec2referenceinthecrmworksheettobe‘=income!C2’.(THEFACTTHAT

WEARETALKINGABOUTC2INBOTHSHEETSISJUSTACOINCIDENCE.)

RepeatforC3.

ForC4wewillneedtoaddupalloftheotherrevenuesourcesdepictedontheincomestatement.

Inc4enter=income!D14-C2-C3

2.CalculateGrossrevenueinC5;=SUM(C2:C4)

3.Referringtotheincomestatementobtaintheappropriatevaluesfortotaloperatingexpensesand

totalinterestexpenses.

Inc6enter=income!D36

Inc7enter=income!D40

4.InC8calculateNFICO;=C5-C6-C7

5.C9ortotalnonfarmincomecomesfromthestatementofcashflows,againtrytorefertoC25ofthat

sheet.

=cashflows!C25

6.Depreciation,C10,expensescomefromtheincomestatement.

7.Incometax(C11)comesfromcellD7ofthestatementofcashflows

8.Familywithdrawals(C12)comesfromD26ofthestatementofcashflows.

{Notethe‘-‘signsintheheadingsforIncometaxandfamilywithdrawals}

9.CalculatethereservecapacityorRC;inC13enter=C8+C9+C10-C11-C12.

10.InC14determinetheprincipalpaymentsforthecomingperiod.Inthiamplewewillusethe

currentportionofourtermdebtfromourbalancesheet.WEALSONEEDTOADDINNEXTYEARS

SCHEDULEDINTRESTPAYMENTS.WEDONOTHAVETHATINFOHANDYSOWEESTIMATETHISVALUE

BYTAKING5%OFTHEPRINCIPALPAYMENTSWEARESCHEDULEDTOPAY.

=0.05*C14

11.FinallycalculatetheCRM;inc16enter=C14-C15.

12.Calculateeachofthesensitivitymeasures

{Notethatsensitivitytononfarmincomeisdifferentthantheothers}

Inc19=C9/C16

Inc20=C16/C2

Inc21=C16/C3

Inc22=C16/C6

Inc23=C16/C7

13.Interpretthesensitivitymeasuressummarizingtheattractivenessof