Question Details

(Solved) Starting Point for the Model: Assumptions: Current assets % of Sales Current Liabilities % of Sales Net working Capt % of Sales Working capital...


Please open the attached Excel valuation model starting point for Chipotle. 

The first section of the spreadsheet has restaurant data – this dissect Chipotles growth into organic growth coming from greater sales in existing restaurants from grow from opening new restaurants – this is important because new restaurants cost approximately $800,000 to open -- we will use this later.

The second section has hard coded actual historical financial data – these numbers drive the remaining analysis.

The third section is a common sized income statement – please review the green cells for past relationships and trends.

The fourth section is the model.

In the model, I have left the yellow projection numbers blank – so the model answer is bogus right now. You will need to:

  1. Review the 2016 Financial Statement (which were attached to Week 4 discussion) and the historical relationships in the common sized income statement (the third section).
  2. Use your best judgment of Chipotle’s competitive position and the future to adjust history to arrive at your projected expense percentages in the model – the yellow cells. (note these numbers should be entered as a percentage – Enter .04 for 4% )
  3. Load your projections into the yellow section – the model will calculate the intrinsic value per share of stock.
  4. Spend some time with the model so that you understand the flow of the model. Please click on the cells within the model to review this model from top to bottom to understand the dynamics of the model. 


The following items have been given to you in the model this week -- but will need to be forecasted in future weeks:

1.      Sales growth rate (a percentage) in number of store and in same store sales growth: (this same store sales growth number can be negative if you want)  


2.      Weighted average cost of capital (a percentage)– remember that this must be greater than the growth rate in the continuing period


3.      Growth rate (a percentage) – this should level off over time and be low during the continuing growth period.

Starting Point for the Model:
Assumptions:
Current assets % of Sales
Current Liabilities % of Sales
Net working Capt % of Sales
Working capital balance 2015 2015 Year 1 Year 2 Year 3 Year 4 $
$
$ 814,647
279,942
534,705 18.1%
6.2%
13% 18.0%
6.2%
11.8% 17.0%
6.2%
10.8% 16.0%
6.2%
9.8% 15.0%
6.2%
8.8% $ 534,705 585,186 $
50,481 $ 647,489 $
62,303 $ 710,261 $
62,772 $ 770,974 $
60,713 $ $
$ 110,474
252,590 2.5%
5.6% 2.5%
5.6% 2.5%
5.6% 2.5%
5.6% $
$ Incremental invest in working capital
Depreciation as a % of sales
Capital investment as a % of sales 2.5%
5.6% Continuing
15.0%
6.2%
8.8%
849,999
79,025
2.5%
5.6% Weighted average cost of capital
Growth rate in the continuing value period 7.75%
5.0% 2015
Avg. number of stores
Growth rate in stores
Number of new stores 2015 1,687 Year 1 Year 2 Year 3 Year 4 1,906
13.00% 2,154
13.00% 2,434
13.00% 2,751
13.00% 219 248 280 316 2,435 $ 2,606 $
7.00% $ 4,108,269 $ 4,967,308 $ 6,005,972 $ 7,261,821 $ 8,780,268 $ 9,680,245 Food, bev,pack
Labor
Occupancy cost
Other operating
Gen and Admin
Depreciation/amort
Preopening cost
Loss on disposal
Total operating costs
Operating income $
$
$
$
$
$
$
$
$
$ 1,503,835
1,045,726
262,412
514,963
250,214
130,368
16,922
13,194
3,737,634
370,635 33.4%
23.2%
5.8%
11.4%
5.6%
2.9%
0.4%
0.3%
83.0%
17.0% $ 0.0%
4,967,308 $ 0.0%
6,005,972 $ 0.0%
7,261,821 $ 0.0%
8,780,268 $ 0.0%
9,680,245 Pretax income
Provision for taxes
Net income $
$
$ 769,867
294,265
475,602 17.0% $
38.2% 4,967,308 6,005,972 7,261,821 Depreciation
Incr. Invest. in Working Capital
Incr. Invest. In leasehold improve
Free cash flows from operations
Continuing value
Total Free Cash Flows
Present value 8,780,268 $ 9,680,245 $ 38.2%
5,424,200 $ 38.2%
5,980,181 $
$ $
(50,481) $ $
(62,303) $ $
(62,772) $ $
(60,713) $ (79,025) $
$ (278,745) $
2,739,436 $ (337,030) $
3,310,986 $ (407,503) $
4,015,872 $ (492,712) $
4,870,775 $ (543,215)
5,357,941 $ 2,739,436 3,310,986 4,015,872 $ $
$ 194,834,211
199,704,987 260,652 (from the balance sheet) 29,893 Share under option
Total shares 31,980 Intrinsic value per share $ 38.2%
4,486,147 $ 157,186,414 Shares outstanding -- net of treasury 2,087 (from the footnotes) $ 4,915.15 Numbers are in thousands except the intrinsic value per share number 160 38.2%
3,710,319 $ $686,484 (from the balance sheet)
$157,447,066
$ 3,352
5.00% 38.2%
3,068,662 $ $ $ 209 $156,760,582 Add Excess Cash
Enterprise value
Less:
Deferred rent
Equity value $ 195 3,192 $
7.00% 138 $ 182 2,983 $
7.00% 2,888
5.00% Sales per store (in thousands)
Growth in sales per store
Increase in sales per store
Total sales 170 2,788 $
7.00% Continuing This number will recalculate after you fill in the assumptions in the yellow field above (D103 to H110)

 


Solution details:
STATUS
Answered
QUALITY
Approved
ANSWER RATING

This question was answered on: Sep 05, 2019

PRICE: $15

Solution~000200162357.zip (25.37 KB)

Buy this answer for only: $18

This attachment is locked

We have a ready expert answer for this paper which you can use for in-depth understanding, research editing or paraphrasing. You can buy it or order for a fresh, original and plagiarism-free solution (Deadline assured. Flexible pricing. TurnItIn Report provided)

Pay using PayPal (No PayPal account Required) or your credit card . All your purchases are securely protected by .
SiteLock

About this Question

STATUS

Answered

QUALITY

Approved

DATE ANSWERED

Sep 05, 2019

EXPERT

Tutor

ANSWER RATING

GET INSTANT HELP/h4>

We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.

You can also use these solutions:

  • As a reference for in-depth understanding of the subject.
  • As a source of ideas / reasoning for your own research (if properly referenced)
  • For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).
This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student.

NEW ASSIGNMENT HELP?

Order New Solution. Quick Turnaround

Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

WE GUARANTEE, THAT YOUR PAPER WILL BE WRITTEN FROM SCRATCH AND WITHIN A DEADLINE.

Order Now