analyze the data

Anonymous
timer Asked: Oct 29th, 2018
account_balance_wallet $25

Question Description

1/I want you to analyze a full Excel file in three pages.

2/ In first sheet in excel, there is red cell ( Key word), Can you please write in every red cell from you About the assumption for example ( I take this assumption because I think will grow in future Or based on revenue something like that)

.

Unformatted Attachment Preview

Dunkin Hist. Hist. Assumptions 2015 2016 Income Statement Assumptions Sales growth - % Cost of sales - % of sales - excl depreciation Selling and administrative expense - % sales - excl amortization Impairment and other - $ Other operating expenses - $ Interest income - % return on cash & equivalents Interest expense - % rate paid on long-term debt Nonoperating income, net - $ Income taxes - % income before taxes Basic WASO Diluted WASO Depreciation - % PY net PP&E Amortization $ Operating income (21,638) 48% 96 97 2,21% 17,93% 30% 395,4 42,7 0,58 (1,1) (1,195) 38% 92 93 45,244 319,567 42,537 414,714 19,90% $ $ 405,2 $ 45,9 $ 0,42 Dividends pre share growth rate 14,48% Balance Sheet Accounts Assumptions receivable, net - % sales Inventories - % COGS Prepaids and other current assets - % sales CapEx Inv & advances affiliates - $ Goodwill - $ Other long-term assets - $ Accounts payable - % COGS Accrued liabilities - % COGS Long-term debt (inc. current portion) - $ repayment from debt note Long term income taxes Deferred income taxes - $ rethink Other long term liabilities - % sales Common stock - $ Additional paid in capital - $ Treasury stock - $ Accum other income - $ Interest rate on debt Repayment of long- term debt 0 10,28% 74,08% 3,47% 0 114,738 888,27 1441,352 8,53% 35,17% 90,48 473,96 1837,824 0,00% 0,0910 807,49 (1,06) (23,984) 5% 25 $ $ Hist. Hist. Hist. Hist. Hist. Hist. 2017 2018 2019 2020 2021 2022 in key word, Write in re 3,81% 15,96% 30% 386,3 $ 43,0 $ 3,31 (1,10) (6,605) -3% 91 92,23 12% 41,419 447,002 0,00% 15,90% 30% 395,5 $ 42,7 $ 5% (1,10) (1,195) 30% 93 94 10% 42,00 400 1,00% 15,92% 30% 395,4 $ 42,7 $ 5% (1,10) (6,605) 30% 93 94 13% 42,00 400 1,50% 15,95% 30% 395,4 $ 42,7 $ 5% (1,10) (1,195) 30% 93 94 13% 42,00 400 2,00% 15,90% 30% 395,4 $ 42,7 $ 5% (1,10) (6,605) 30% 93 94 13% 42,00 400 2,00% 15,91% 30% 395,5 42,7 5% (1,10) (1,195) 30% 93 94 13% 42,00 400 7,49% 10% 10% 10% 10% 10% 11,91% 102,99% 3,80% 0 140,615 888,31 1422,621 11,88% 42,25% 11,92% 103% 3,81% 7% 140,640 888,31 1422,621 11,88% 42,25% 11,93% 103% 3,82% 7% 140,640 888,31 1422,621 11,88% 41,00% 11,92% 103% 3,83% 7% 140,650 888,31 1422,621 11,88% 41,00% 11,94% 103% 3,83% 7% 140,700 888,31 1422,621 11,88% 41,00% 11,93% 103% 3,85% 7% 140,690 888,31 1422,621 11,88% 41,00% 94,78 326,41 94,78 326,41 94,78 326,41 94,78 326,41 94,78 326,41 94,78 326,41 0,00% 0,0900 724,11 (1,06) (9,690) 5,50% 754,375 0,00% 0,090 724,11 (1,06) (9,690) 6% 500 0,00% 0,090 724,11 (1,06) (9,690) 5,50% 500 0,00% 0,090 724,11 (1,06) (9,690) 5,50% 500 0,00% 0,090 724,11 (1,06) (9,690) 5,00% 500 0,00% 0,090 724,11 (1,06) (9,690) 5,00% 500 in key word, Write in red cell why did take this assumption? Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Key Word Dunkin Hist. Hist. Hist. Hist. Hist. Revenues Assumption 2013 2014 2015 2016 2017 US Operations Revenues growth rate 563,331 594,736 5,6% 638,2 7,3% 2,7% 5,4% International Revenues growth rate 138,65 139,44 140,05 141,92 135,25 156,9 0,6% 0,4% 1,3% -4,7% 16,0% 14,5 32,7 31,5 34,1 22,5% 124,9% -3,63% 8,42% 16,0% 748,7 810,9 828,9 2,2% 860,5 3,8% 1.005,1 16,8% Other Revenues growth rate Total Revenues growth rate 11,9 713,8 Revenue growth scenario 655,5 691,1 Proj. 2018 808,6 17,0% 39,6 1 Overall growth rate Base case Upside case Downside case 0,0% 0,0% 1,0% -2,0% Proj. Proj. Proj. Proj. 2019 2020 2021 2022 873,3 8,0% 166,3 943,1 8,0% 176,3 1.018,6 8,0% 186,9 1.100,1 8,0% 198,1 6,0% 6,0% 6,0% 6,0% 42,0 44,5 47,2 50,0 6,0% 6,0% 6,0% 6,0% 1.081,6 7,6% 1.163,9 7,6% 1.252,6 7,6% 1.348,2 7,6% 1,0% 1,0% 2,0% -1,0% 1,5% 1,5% 2,5% 0,0% 2,0% 2,0% 3,0% 1,0% 2,0% 2,0% 3,0% 2,0% DUNKKIN Hist Hist Hist Income statement 2015 2016 2017 Revenues Cost of sales - excluding D&A on PP&E 810,9 161,4 828,9 148,6 860,5 137,3 Gross profit 649,5 680,3 723,2 45,2 242,4 45,2 407,18 42,5 246,8 42,5 433,5 41,4 248,3 41,4 474,8 Amortization 242,4 20,6 24,7 246,8 20,5 22,1 248,3 20,1 21,3 EBIT 361,9 390,9 433,4 Impairment and other Other operating expenses/(income) - p. 43 405,2 45,9 451,1 395,4 42,7 438,1 386,3 43,0 429,3 319,567 0,424 -96,765 -21,638 414,714 0,582 -100,852 -1,195 447,002 3,313 -104,423 -6,605 201,5880 313,249 339,287 Net income 96,359 105,229 117,673 195,576 -11,622 350,909 Dividends paid -100,516 -109,703 -117,003 96 97 1,10 1,08 1,05 92 93 2,14 2,11 1,20 91 92 3,86 3,80 1,29 Other information D&A total from CFS D&A on PP&E Amortization EBITDA Selling and administrative expense - excl amortization Depreciation Operating profit as reported Operating income Interest income Interest expense Nonoperating income(expense) Income before taxes Income taxes Net earning per share Basic WASO Diluted WASO Basic earnings per common share Diluted earnings per common share Proj Proj Proj Proj 2018 2019 2020 2021 860,50 136,8 869,11 138,4 882,14 140,7 899,79 143,1 723,7 730,7 741,4 756,7 58,9 248,3 42,000 458,4 69,6 248,3 42,000 454,8 73,9 248,3 42,000 461,2 77,8 248,3 42,000 472,6 258,2 16,9 42,0 260,7 27,6 42,0 264,6 31,9 42,0 269,9 35,8 42,0 406,63 400,41 402,87 408,98 395,5 42,67 438,1 395,4 42,68 438,1 395,4 42,70 438,1 395,4 42,69 438,1 400 51 (182) (1,195) 400 54 (139) (6,605) 400 57 (112) (1,195) 400 62 (77) (6,605) 267,6 307,6 344,2 378,5 80,3 187,30 92,3 215,29 103,3 240,95 113,6 264,96 -131,7387187 -144,9125906 -159,4038497 -175,3442346 93 94 2,01 1,99 1,42 93 94 2,31 2,29 1,56 93 94 2,59 2,56 1,71 93 94 2,85 2,82 1,89 Proj 2022 917,78 146,0 771,8 81,3 248,3 42,000 484,1 275,3 39,3 42,0 415,09 395,5 42,66 438,1 400 67 (52) (1,195) 413,9 124,2 289,75 -192,8786581 93 94 3,12 3,08 2,07 1000 DUNKKIN Hist Hist Proj Proj Balance sheet 2016 2017 2018 2019 Assets Cash & equivalents Accounts receivable, net Inventories Prepaids and other current assets Assets held for sale 361,4 85,2 110,1 28,7 20,9 606,4 1018,317 1072,6082 1147,726 102,524 102,572 103,684 141,42 140,92 142,53 32,695 32,785 33,200 21,879 21,879 21,879 169,005 212,33957 245,5728 140,615 140,640 140,640 888,308 888,308 888,308 1422,621 1380,621 1338,621 Total non-current assets 176,7 114,7 888,3 1.441,4 2.621,0 TOTAL ASSETS 3.227,4 Total current assets Net P,P&E Inv & advances affiliates Goodwill Other long-term assets 1.316,8 1.370,8 1.449,0 2.620,5 2.621,9 2.613,1 3.937,4 3.992,7 4.062,2 Liabilities & Equity Financing gap Accounts payable Accrued liabilities Liabilities held for sale Total current liabilities 12,7 52,3 359,2 424,2 16,307 58,014 397,6 471,9 0 16,248 57,806 397,6 471,6 0 16,432 56,728 397,6 470,7 Long-term debt (inc. current portion) Deferred income taxes Long term income taxes Other long term liabilities 2.402,0 474,0 90,5 224,0 3.036 326,407 94,783 Total non-current liabilities 2.966,4 3.457,0 3.457,0 3.457,0 3.390,640 3.928,9 3.928,7 3.927,8 Total liabilities Common stock Additional paid in capital Retained earnings Treasury stock Accum other income 0,1 807,5 (945,8) (1,1) (24,0) Total equity (163,3) TOTAL LIABILITIES & EQUITY Does balance sheet balance? 3.036 326,407 94,783 3.036 326,407 94,783 0,090 0,090 724,114 724,114 -705,007 -649,4471 -1,06 (1,06) -9,69 (9,69) 8,4 64,0 0,090 724,114 -579,072 (1,06) (9,69) 134,4 3.227,4 3.937,4 3.992,7 4.062,2 0,0 0,0 0,0 0,0 Proj Proj Proj 2020 2021 2022 1238,214 1338,437 1447,973 105,151 107,434 109,491 144,94 147,39 150,46 33,786 34,462 35,335 21,879 21,879 21,879 1.544,0 1.649,6 1.765,1 275,3984 302,5816 327,4906 140,650 140,700 140,690 888,308 888,308 888,308 1296,621 1254,621 1212,621 2.601,0 2.586,2 2.569,1 4.144,9 4.235,8 4.334,2 0 16,709 57,688 397,6 472,0 0 16,990 58,657 397,6 473,2 0 17,341 59,868 397,6 474,8 3.036 326,407 94,783 3.036 326,407 94,783 3.036 326,407 94,783 3.457,0 3.457,0 3.457,0 3.929,0 3.930,3 3.931,8 0,090 724,114 -497,523 (1,06) (9,69) 215,9 0,090 724,114 -407,908 (1,06) (9,69) 305,5 0,090 724,114 -311,033 (1,06) (9,69) 402,4 4.144,9 4.235,8 4.334,2 0,0 0,0 0,0 Dunkin Calcs Hist. 2015 Hist. 2016 Hist. 2017 Proj. 2018 Proj. 2019 PP&E PP&E - beg CAPEX Depreciation PP&E - end Other long term assets - beg Additions to other long term assets Amortization Other long term assets - end 121,6400 124,3334 129,0752 20,556 20,458 20,084 176,662 169,005 169,005 60,23507 16,9005 212,33957 212,33957 60,8374 27,6041441 245,57285 1422,621 1422,621 0 42 1380,621 1380,621 0 42 1338,621 Reconciliation of RE RE - beg Net income Dividends RE - end -705,007 -649,4471177 187,30 215,29 131,7387187 144,9125906 -705,007 -649,45 -579,07 Proj. 2020 Proj. 2021 245,5728466 275,3983586 61,7500 62,9850 31,92447006 35,80178661 275,39836 302,58155 1338,621 0 42 1296,621 1296,621 0 42 1254,621 Proj. 2022 302,5815536 64,2447 39,33560197 327,49063 1254,621 0 42 1212,621 -579,0724146 -497,522849 -407,9081015 240,95 264,96 289,75 159,4038497 175,3442346 192,8786581 -497,52 -407,91 -311,03 Dunkin debt Revolver Ending balance Interest rate on debt Interest expense - use beginning balance Hist. 2015 Hist. 2016 Hist. 2017 Proj. 2018 0 0 6% 0 Long term debt Beginning balance Repayment Ending balance 3035,857 500 3035,857 2535,857 Interest rate on debt Interest expense - use beginning balance 6% 182,15 Total interest expense - using beginning balance 182,15 Proj. 2019 0 6% 0 Proj. 2020 0 6% 0 Proj. 2021 0 5% 0 Proj. 2022 0 5% 0 2535,857 2035,857 1535,857 1035,857 500 500 500 500 2035,857 1535,857 1035,857 535,857 6% 139,47 6% 111,97 5% 76,79 5% 51,79 139,47 111,97 76,79 51,79 Denny's Hist. Hist. Hist. Proj. Proj. Proj. Cash flow 2015 2016 2017 2018 2019 2020 Net income Depreciation Amortization Accounts receivable, net Inventories Prepaids and other current assets Assets held for sale Accounts payable Accrued liabilities Liabilities held for sale Deferred income taxes Long term income taxes Other long term liabilities Operating cash flows CAPEX Inv & advances affiliates Goodwill Investing cash flows Long-term debt (inc. current portion) Common stock Additional paid in capital Treasury stock Accum other income Dividends Financing cash flows Change in cash Cash - beg Change in cash Cash - end 187,30 215,29 240,95 16,9005 27,60414 31,92447 42 42 42 -0,04772 -1,11263 -1,46705 0,495751 -1,60211 -2,4105 -0,09009 -0,41476 -0,58621 0 0 0 -0,05859 0,183127 0,277901 -0,20843 -1,07728 0,959428 0 0 0 0 0 0 0 0 0 0 0 0 246,29 280,87 311,65 -60,2351 -0,025 0 -60,2601 -60,8374 0 0 -60,8374 -61,75 -0,01 0 -61,76 0 0 0 0 0 0 0 0 0 -131,739 -131,739 -144,913 -144,913 -159,404 -159,404 54,29 75,12 90,49 1018,317 1072,608 1147,726 54,29 75,12 90,49 1018,317 1072,608 1147,726 1238,214 Proj. Proj. 2021 2022 264,96 289,75 35,80179 39,3356 42 42 -2,28299 -2,05691 -2,44961 -3,07148 -0,67572 -0,87279 0 0 0,28076 0,350703 0,969298 1,210769 0 0 0 0 0 0 0 0 338,60 366,65 -62,985 -0,05 0 -63,035 -64,2447 0,01 0 -64,2347 0 0 0 0 0 0 -175,344 -175,344 -192,879 -192,879 100,22 109,54 1238,214 1338,437 100,22 109,54 1338,437 1447,973 DUNKKIN Hist. Hist. Hist. Ratio 2015 2016 2017 Growth Sales growth Sales CAGR 2015 - 2017 EBITDA growth Net income growth 2% 4% 8% 86% 11% 79% 53% 47% 52% 24% -120% 50% 50% 55% 41% 4154% 2.041 5,54 (4,30) 2.018 6,39 (4,55) 37,5 270,4 31,1 276,7 (245,6) 15905% 19% 21% 0,02% 6,08 301,3 (124,7) (0,4) 43,5 375,9 43,3 376,1 (332,7) 20232% 24% 20% 0,02% 6,43 302,8 (133,8) (0,4) 361,9 48% 188,9 45,2 0,2 390,9 38% 244,1 42,5 0,2 234,3 286,8 433,4 -3% 448,3 41,4 0,2 (43,3) 446,6 2922% Profitability Operating margin EBIT margin EBITDA margin Net income margin Return on equity - ROE total 56% 45% 50% 13% Leverage & Coverage Net debt Gross debt / EBITDA EBITDA / interest expense (4,21) Asset management Receivable days Inventory days Payable days Cash conversion cycle OWC OWC - another approach OWC to sales Net PPE /Sales CAPEX/Sales CAPEX/Depreciation Gross PP&E Accumulated depreciation Accumulated depreciation/Gross PP&E 0% 5,92 Free cash flow EBIT Tax rate NOPAT D&A CAPEX New investment OWC FCF Proj Proj Proj Proj Proj 2018 2019 2020 2021 2022 0% 1% 1% 2% 2% -6% -47% -2% 15% 1% 12% 2% 10% 1% 9% 51% 47% 53% 22% 293% 50% 46% 52% 25% 160% 50% 46% 52% 27% 112% 49% 45% 53% 29% 87% 48% 45% 53% 32% 72% 1.963 6,62 (2,52) 1.888 6,68 (3,26) 1.798 6,58 (4,12) 1.697 6,42 (6,15) 1.588 6,27 (9,35) 43,5 376,0 43,3 376,1 (332,8) 20223% 24% 25% 0,01% 3,56 302,8 (133,8) (0,4) 43,5 376,0 43,3 376,2 (332,8) 20625% 24% 28% 0,01% 2,20 302,8 (133,8) (0,4) 43,5 376,0 43,3 376,1 (332,8) 20948% 24% 31% 0,01% 1,93 302,8 (133,8) (0,4) 43,6 376,0 43,3 376,3 (332,9) 21364% 24% 34% 0,01% 1,76 302,8 (133,8) (0,4) 43,5 376,1 43,3 376,3 (332,9) 21808% 24% 36% 0,01% 1,63 302,8 (133,8) (0,4) 406,6 30% 284,6 58,9 0,1 0,1 343,7 400,4 30% 280,3 69,6 0,1 (4,0) 345,9 402,9 30% 282,0 73,9 0,1 (3,2) 352,8 409,0 30% 286,3 77,8 0,1 (4,2) 360,0 415,1 30% 290,6 81,3 0,1 (4,4) 367,5 ...
Purchase answer to see full attachment

Tutor Answer

TutorBenkipp
School: Rice University

...

flag Report DMCA
Review

Anonymous
Thanks, good work

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors