Excel Finance work

Jad Agil
2018dHersheyHomeworkValuationSpreadsheet4.xls

a

DCF

Hershey Food Case
($millions)
Year 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011
current assets 1,167.5 1,194.6 1,254.3 1,317.0 1,382.9 1,452.0 1,524.6 1,600.8 1,680.9 1,764.9 1,853.2
non-debt current liabilities 598.5 543.0 570.1 598.6 628.6 660.0 693.0 727.6 764.0 802.2 842.3
net operating working capital 569.0 651.6
Net PPE and other tangible assets 1,691.2 1,737.5 1,824.4 1,915.6 2,011.4 2,112.0 2,217.6 2,328.5 2,444.9 2,567.1 2,695.5
NOPAT 386.3 433.2 483.9 508.1 533.5 560.1 588.1 617.5 648.4 680.8
Change in net operating WC 82.6
Change in Net PPE and other tangible assets 46.3
FCF = NOPAT minus the net investment* in WC and PPE 257.4
WACC 7.59%
Assumed FCF growth rate beyond 2011; 4%
*net investment means net change

Sheet3