Page 1 of 4 CVPModelingprojectThepurposeofthisprojectistogiveyouexperiencecreatingamultiproductprofitabilityanalysisthatcanbeusedtodeterminetheeffectsofchangingbusinessconditionsontheclient'sfinancialposition.YourgoalwillbetouseExcelinsuchawaythatanychangestotheassumptionswillcorrectlyripplethroughtheentireprofitabilityanalysis.Ifexecutedproperly,theclientshouldbeabletousethisspreadsheetoverandover,usingdifferent"whatif"assumptions.BusinessDescriptionAftertakingbusinessclasses,Jake,anaviddog-lover,decidedtostartsellinguniquepetsuppliesattradeshows.Hehastwoproducts:Product1:"Launch-it"-atennisballthrowerthatwillsellfor$12.Product2:"Treat-time"-anautomatictreatdispenserthatreleasesatreatwhenthedogplaceshispawonthepedal.Thetreatdispenserwillsellfor$32.Costs:Jakehashiredanemployeetoworkthetradeshowbooths.Theworkcontractis$1,200permonthplusacommissionequalto10%ofrevenue.Jakewillalsospend$800permonthontrade-showentryfees.JakeispurchasingtheproductsfromasupplierinMexico.Launch-itscost$1each;Treat-timescost$7each.ShippingandhandlingontheLaunch-itswillcost$2each;ShippingandhandlingontheTreat-times,whichareheavier,willcost$8each.TheshippingandhandlingcostswillbepaidbyJake,notthecustomer.AssumeJakeexpectstosell200Launch-itsand100Treat-timesduringhisfirstmonthofoperations(June).Jake'sfinancialgoalistoearnanoperatingincomeof$8,000permonth.Hebelievesvolumemaygrowatarateof5%amonth.DirectionsYouhavebeenhiredbyJaketobuildaCVPmodelthatwillhelphimunderstandtheimpactofbusinessconditionsonhisoperatingincome.(See"StartingFile"worksheet.)Inyourmodel,alloftheoriginalassumptionswillbelistedinoneareaofthespreadsheet(bluebox).Allothercalculationsinthemodelwillreferencetheassumptions(bluebox)suchthatifanyassumptionchanges,theeffectwillripplethroughtheentiremodel.Toaccomplishthisgoal,youwilluseFORMULAs,ratherthannumbers,ineveryothercellintheworksheet.Inotherwords,theonlyplaceyouwilltypenumbersistheblueassumptionsbox.FORMATTINGconventionstousethroughoutproject:-RoundallUNITStothenearestwholeunit.Usethe"decreasedecimals"buttononyourtoolbarratherthantheRoundingfunction.-ShowallMONETARYamountsasdollarsandcents.Roundtothenearestcent.($x.xx).Usethe"decreasedecimals"buttonratherthantheroundingfunction.-Showallpercentagesas%,notasdecimals.(x%,not.xx)-Rightjustifyallcells(numbersshouldbetotherightsideofthecell,notinthemiddleorleft)1)Completetheassumptions(bluebox)basedonthedataaboutJake'sbusiness.Identifyandlistallvariablecostsseparatelyandallfixedcostsseparatelybeforefindingthetotalforeachtypeofcost.2)CompletetheProductAnalysis(yellowboxes)assumingJakeONLYsellseitherProduct#1(Launch-its)ORProduct#2(Treat-times).Checkfigures:B/EProduct#1=256units;B/EProduct#2=145units3)CompletetheproformaCMIncomeStatementforthemonthofJune(greenbox).HINT:Onproductlineincomestatementssuchasthis,thefixedcostsareonlylistedinthetotalcolumn.Makesureyoualsoshowthetotalsforallotherlineitems.Finally,calculatetheoverallWACM%forthecompany.Checkfigure:Operatingincome=$940WACM%=53%4)Calculatetheweightedaveragecontributionmargin(WACM)perunit(inorangebox).Checkfigure:WACM/unit=$9.85)UsetheWACM/unittocalculatetheTOTALnumberofunitsneededtobreakeven(TOTALcolumninthefirstgraybox).THEN,calculatethenumberofEACHtypeofproductneededtobreakeven.Finally,calculatethesalesrevenueassociatedwiththisvolumeforEACHproduct,andthenthesalesrevenuetobreakevenintotal.Checkfigures:B/EProduct#1=136;B/EProduct#2=686)UsetheWACM/unittocalculatethetotalnumberofunitsneededtoachieveJake'stargetprofit(TOTALcolumninthesecondgraybox).THEN,calculatethenumberofEACHtypeofproductneededtoachievethetargetprofit.Finally,calculatesalesrevenueassociatedwiththisvolumeforEACHproduct,andthenthesalesrevenueintotal.Checkfigures:NumberofunitsofProduct#1=680;NumberofunitsofProduct#2=3407)CalculatetheMOSusingJunesalesastheexpectedsales(purplebox).CalculatetheMOSintermsofsalesrevenueandasapercentage.Alsocalculatethecurrentoperatingleveragefactor(roundtothenearest2decimalplaces)anduseittodeterminetheexpectedpercentagechangeinoperatingincomestemmingfromanexpectedchangeinsalesvolume.Checkfigures:MOS%=32%;Operatingleveragefactor=3.138)Changenameofworksheetto"OriginalAssumptions".9)Makesureyouhavecleanedupyourworksheetusingtheformattingconventionslistedabove.10)Gotothe"Advisingclient"worksheetandfollowthedirectionsfoundthere.11)Checktomakesureyouhavedoneeverythingonthegradingrubric.Uploadyourfiletocanvaswhenyouarefinished. CVPModelASSUMPTIONSProduct#1Launch-itJake'sPetSuppliesProduct#1:Launch-itUnitCMProFormaContributionMarginIncomeStatementSalespriceperunitCM%ForthemonthendingJune30Variablecostsperunit:Breakevenpoint:-inunitsProduct#1Product#2Total-insalesrevenueSalesrevenueLess:variableexpensesTotalvariablecostperunitTargetprofitvolume:Contributionmargin-inunitsless:fixedexpensesMonthlyvolume-insalesrevenueOperatingIncomeProduct#2:Treat-timeWACM%SalespriceperunitProduct#2Treat-timeVariablecostsperunit:UnitCMCM%CalculationofWeightedaverageCMperunitBreakevenpoint:Product#1Product#2Total-inunitsCM/unitTotalvariablecostperunit-insalesrevenueSalesmix(#ofunitssoldofeach)ContibutionmarginMonthlyvolumeTargetprofitvolume:-inunitsWACM/unitFixedcostspermonth:-insalesrevenueTotalfixedcostspermonthMultiproductBreakevenpoint:Product#1Product#2Total-inunitsTargetprofitpermonthSalesrevenueatbreakevenExpectedchangeinvolume(%)MultiproductTargetprofitpoint:Product#1Product#2Total-inunitsSalesrevenueattargetprofitMarginofSafety(in$)MarginofSafety%OperatingLeverageFactorExpected%changeinoperatingincome(%) NEWORIGINALChangeOperatingincomeWACMpercentageMOS%OperatingincomeWACM/unitUnitstoearntargetprofitOperatingincomeOperatingleveragefactorExpected%changeinopincOnceyouhavebuiltthemodel,useittoanswerJake'squestionsabouthisbusiness.Treateachsituationasaseparatescenario.Allcomparisonsshouldbemadetotheoriginalassumptions.1.Saveacopyofyouroriginalmodeltoanewspreadsheetcalled"suppliercostincrease".Saythesupplierisexpectedtoincreasethecostoftheproductsby20%.Whatisthenewoperatingincome?WhatisthenewWACM%?WhatisthenewMOS%?Brieflyexplainyourfindingstotheclient.2.Saveacopyofyouroriginalmodeltoanewspreadsheetcalled"newsalesmix".Saythemonthlysalesvolumeisnowexpectedtobe175"Treat-times"and125"Launch-its"(sametotalunits,butadifferentsalesmix).Whatisthenewoperatingincome?WhatisthenewWACM/unit?Giventhissalesmix,howmanyunits(intotal)willJakeneedtoselltoearnhistargetprofit?Brieflyexplainyourfindingstotheclient.3.Saveacopyofyouroriginalmodeltoanewspreadsheetcalled"alternativecontract".SayJake'semployeewantedtonegotiateadifferentworkcontract:$1,500permonthplus5%ofrevenue.Givenhisoriginalsalesvolumeandmix,howwouldthiscontracthavechangedJake'soperatingincome?Whatisthenewoperatingleveragefactor?Whatisthenewexpectedpercentagechangeinoperatingincomeifvolumeincreasesasexpectedinthefuture?Brieflyexplainyourfindingstotheclient.Brief explanation:Brief explanation:Brief explanation:EXCELHINT:Tocopyacellfromadifferentworksheet,put=inthecellwhereyouwantthenumbertogo,andthengobacktotheoriginalworksheet,putyourcursoronthecelltoselectit,andthenpressenter.EXCELHINT:Tocopyanentireworksheet,rightclickontheworksheettabatthebottomofthescreenand choose. AdvisingClientworksheets(totalthree)Suppliercostincrease(greenboxes)-Worksheet2Correctcomparisonfigures5Explanation5NewSalesmix(yellowboxes)-Worksheet3Correctcomparisonfigures5Explanation5Alternativecontract(purpleboxes)-Worksheet4Correctcomparisonfigures5Explanation5AllWorksheetsAllworksheetsproperlylabeled5Total1000