Chloe Jants Coffee Shop
Keaton Bolonda
Roosevelt Campbell
Olivia Canby
Presentation Outline
Project Overview/Background -- Keaton Bolonda
Financial Overview --
Additional Factors -Summary/Recommendations --
Best Management Practices -- Keaton Bolonda
Project Overview
-Ms. Jants has entertained the idea of opening up her own coffee for some time but she
is ready to next steps
Project Overview...continued
Financial Overview
Additional Factors
Additional Factors...continued
Initial Summary and Recommendations
BPM Standards
BMPC4-2
BPM Standards
BMPC 4-1
CHAPTER 1
CREATING SPREADSHEET MODELS
Chapter
Review
Class #1
Chapter 1 - Creating Spreadsheet Model
Business Case #1 - Terra Cotta Brew and Coffee Shop (a)
Overview of Functions,
Business
Business
Skills,
eText
Grader
Analysis
Case
and Terms Pages 45 - 47
Project
(Memo)
Class #1
Class #1
Class #2
Class #2
Class #3
Class #3
Functions
Team(s)
Present
Case
Class #3
(a) Grader Project and Business Analysis Questions (Memo) must be submitted prior to the beginning of class
shown on the above chart . No work will b e accepted after deadline. Plan accordingly.
IF
PMT
SUM
VLOOKUP
Skills & Terms
2-D Line Chart
Absolute Cell Reference
Autofill
Comment
Data Validation
Drop-Down List
Fill Colors
Number Format
Named Range
Various Chart Styles, etc.
Other Lecture Topics
Inputs
Decision Variables
Outputs
Uncertain Variables
Class #3
Business Analysis Memo
Business Case #1
Terra Cotta Brew and Coffee Shop
Question 1 - What is the purpose of a spreadsheet model and how can a
spreadsheet model help the owners of Terra Cotta Brew Coffee Shop make
decisions about the renovation?
a) Purpose- Spreadsheet models fall into two basic categories: Descriptive and
Prescriptive. Descriptive models organize existing data; often into tables.
Tables provide a useful format for presenting data as they have multiple
statistical and organizational features (e.g., sum, average, counting, filtering,
etc.). Prescriptive models incorporate data that will help the user make
decisions about a future event. The model can also be organized to determine
which data is the most sensitive to change (i.e., Sensitivity analysis) or to
simulate a future event (i.e., Scenario analysis).
Question 1 (Continued) - What is the purpose of a spreadsheet model and
how can a spreadsheet model help the owners of Terra Cotta Brew Coffee
Shop make decisions about the renovation?
b) Decision Making- The Terra Cotta spreadsheet incorporates Descriptive and
Prescriptive characteristics.
✓Descriptive: The “Sales Data” tab presents the coffee shop’s monthly sales history;
a history that is further divided into six sales categories.
✓Prescriptive: In the “Analysis” tab, the owners will draw-upon the sales history, in
combination with known facts regarding the planned renovations (i.e., interest
rates, renovation costs, deposits, renovation time horizon, etc.), to create a
scenario analysis model that will aid them in determining if they should proceed
with the construction.
Question 2 - Which variable did you indicate as your key Inputs, Decision
Variables, Uncertain Variables, and Outputs?
a) Key Inputs:
✓ Revenue History
✓ Renovation Costs
✓ Length of Construction
b) Decision Variables:
✓ Investment Amount
✓ Construction Option (e.g.,
Expedite, Non-Expedited)
✓ Annual Percent Rate (APR)
for the loan
c) Uncertain Variables:
✓ Estimated Growth Rate
d) Outputs:
✓ Additional Profits
Question 3 - Describe how you incorporated each of the following design
principles into the spreadsheet model.
a) Accuracy:
✓ Use of Range Names in formulas
✓ Double-Checked inputs
b) Clarity:
✓ Color-coded cell for Inputs,
Decisions Variables and
Uncertain Variables
✓ 2-D Line Chart
c) Flexibility:
✓ Use of cell references in
formulas (static vs. dynamic)
d) Efficiency
✓
Use of Data Validation and
drop-down lists
e) Documentation:
✓
Use of Comments function
Question 4 - Assuming the investment amount is $137,000 and the
estimated growth rates are accurate, should the owners go with a monthly
or quarterly payment plan?
a) The spreadsheet model calculated four possible loan-repayment scenarios:
1.
2.
3.
4.
Expedited with Monthly Payments = $392,069
Expedited with Quarterly Payments = $393,741
Non-Expedited with Monthly Payments = $371,866
Non-Expedited with Quarterly Payments = $373,667
b) If the owners are basing their decision solely on the total cost of the loan they
should move forward with Non-Expedited Renovations and a bank loan with
monthly payments. The total cost would be $371,866.
Question 5 - Assuming the Expedited Renovations can be completed in
three weeks, versus 13 weeks for Non-Expedited, provide the dollar
amounts for the following:
a) Average Weekly Profits for 2016:
✓ $150,075.45 / 52 weeks = $2,886.07 per week
b) Lost revenue during Expedited Renovations:
✓ 3 * $2,886.07 = $8,658.20
c) Lost revenue during Non-Expedited Renovations?
✓ 13 * $2,886.07 = $37,518.91
d) Opportunity Cost for Non-Expedited Renovations?
✓ $37,518.91 - $8,658.20 = $28,860.71
Question 6 – Looking at Year 1 of the Data Analysis tab (column H), is the
stated profit of $163,957.43 represent an accurate estimate for Year 1?
Explain your response.
a) Accurate (Yes/No)?
✓ No
b) Why?
✓ The estimate does not include the time the coffee shop will be closed.
✓ If the owners opt for Expedited Renovations, profits for Year 1 would be
$163,957.43 – ($2,886.20 * 3) = $155,299.23
✓ If the owners opt for Non-Expedited Renovations, profits for Year 1 would be
$163,957.43 – ($2,886.20 * 13) = $125,638.52…. A difference of $29,660.71
General Guidelines
Regarding
Team Presentations
How Team Presentations Will Be Evaluated
• The Team
– Did everyone participate?
– Were basic questions answered?
– Did presentation go beyond the minimum requirements?
• Presentation and Spreadsheet Organization
– Did it flow… was it logical?
– Did the analysis include Excel Functions and Skills that were not part of the Grader Project?
• Use of Critical Thinking
– The U.S. National Council for Excellence in Critical Thinking definition critical thinking:
• The "intellectually disciplined process of actively and skillfully conceptualizing, applying, analyzing,
synthesizing, or evaluating information gathered from, or generated by, observation, experience, reflection,
reasoning, or communication, as a guide to belief and action.“
– In other words…………. Did the group address additional Questions? Did they dig deeper or challenge data?
– Analyze data as if you were Benjamin Disraeli
• Use of BPM Standards
– Designed to improve the Use, Understanding and Sharing of spreadsheets
– State where they were used during your presentation
BPM Standards
(see Content of Blackboard)
I) Title Page
Terra Cotta Brew &
Coffee Shop
Evaluation of Business Options
Team _____ - 4/4/18
II) Presentation Outline
Presentation Outline
•
•
•
•
•
Project Overview and Background………. Name of Student
Financial Review and Summary………… Name of Student
Additional Factors and Revised Summary………… Name of Student
Additional Excel Functions, Skills and Tools…….. Name of Student
Best Management Practices………. Name of Student
III) Project Overview and Background
Project Overview and Background
• Terra Cotta has chosen a contractor to renovate the coffee shop. The estimates fall into two categories:
✓ Expedited - $475,000 to complete the project a few weeks (i.e., 3 weeks)
✓ Non-Expedited - $450,000 to complete the project in 3 months (i.e., 13 weeks)
• Coffee shop will need to be closed during the renovations
• Internal Funding:
✓ Owner wants to limit cash-on-hand funding (deposit) to between $90,000 to $150,000.
✓ Owner’s indicated they were planning on investing $137,000 but would be open to your
recommendation(s).
• Gross Revenues in 2016 were $750,357
• Owner has estimated expenses to be 80% of revenue. Net Income was calculated to be $150,075
• External Bank Funding: From $300,000 (at 7.3%) to $360,000 (at 5.0%) at increments of $10,000
Project Overview and Background…continued
• After meeting with the owners of Terra Cotta the team was convinced its’ management is better at
brewing coffee than understanding finances.
• The Case will required the team to answer questions regarding:
✓ How much capital to invest
✓ How much to finance for the project (i.e., bank loan)
✓ To determine if the projected revenues were sufficient to pay for the cost of the loan in 7 years.
• In addition, our team developed several scenarios and considerations that were not part of our original
scope of work.
• In General:
✓ The Model will allow what-if analysis to estimate certain impacts on profits over 7 years (a time horizon
indicated by the owners of Terra Cotta).
✓ Trying to determine the trade-off between amount financed and profits earned.
IV) Financial Review and Considerations
Financial Review
• Renovation Options: Expedited ($475,000), Non-Expedited ($450,000) or
Status Quo ($0)
• Available Funding: $90,000 to $150,000.
• Interest Rate on Bank Loan: 5.0% to 7.3%.
• Monthly Payments and Total Interest will vary depending on Deposit and
timing of renovations
Original Grader Project Submission
Initial Summary & Recommendations
• Based solely on the Grader Project findings/analysis:
✓ We would recommend that the owners move forward with Non-Expedited renovations.
✓ Owners’ 7-year financial criteria had been met (i.e., Net Revenues exceeded Cost of Loan)
➢ Cost of the Non-Expedited renovations were $371,866 vs. $372,564 in bank payments.
➢ The $137,000 deposit fell within the owner’s parameters.
V) Additional Factors and Revised Summary
Additional Financial Factors to Consider
✓ Projecting future growth rates is always risky (aka; “Crystal Ball Gazing”)
✓ Terra Cotta’s projections only exceeded their financial criteria by $1,198.95
over seven years
• If their projection in year-3 ends up being 8.1% vs. 8.3%, the criteria won’t be
met.
• “Hope” is not a business strategy.
✓ The owner was not able to take advantage of the different interest rates with the
Expedited renovation option.
Additional Financial Factors to Consider (Continued)
✓ The Grader Project did not take into account the Opportunity Cost of the two
options.
✓ While the Non-Expedited approach met the owners’ financial criteria, our
team found additional information that negated, or at the very least questioned,
the original recommendation.
✓ In short, our team found several fatal flaws in the assumptions and base
calculations.
Consider all 14 Investment and Payment Options
(Note deposit increases by $10,000 per scenario)
Non-Expedited Renovations ($450,000)
Investment Amount
Amount Needed to
Finance
Total Cost with Monthly
Payments
Total Cost with
Quarterly Payments
$
90,000.00 $
100,000.00 $
110,000.00 $
120,000.00 $
130,000.00 $
140,000.00 $
150,000.00
$
360,000.00 $
350,000.00 $
340,000.00 $
330,000.00 $
320,000.00 $
310,000.00 $
300,000.00
Monthly Payments
$
($407,618.65)
($401,124.41)
($389,663.71)
($382,789.47)
($375,670.05)
($368,302.29)
($358,974.81)
($409,106.81)
($402,713.76)
($391,207.65)
($384,421.86)
($377,382.21)
($370,085.59)
($360,772.67)
6,794 $
6,685 $
6,494 $
6,380 $
6,261 $
6,138 $
5,983
Expedited Renovations ($475,000)
Investment Amount
Amount Needed
to Finance
Total Cost with
Monthly Payments
Total Cost with
Quarterly Payments
$
90,000.00 $
100,000.00 $
110,000.00 $
120,000.00 $
130,000.00 $
140,000.00 $
150,000.00
$
385,000.00 $
375,000.00 $
365,000.00 $
355,000.00 $
345,000.00 $
335,000.00 $
325,000.00
Monthly Payments
$
($435,925.50)
($424,602.76)
($413,280.02)
($406,854.75)
($395,394.06)
($388,589.31)
($381,539.89)
($437,517.00)
($426,152.92)
($414,788.84)
($408,466.81)
($396,960.70)
($390,246.43)
($383,278.81)
7,265 $
7,077 $
6,888 $
6,781 $
6,590 $
6,476 $
6,359
Monthly Payment Comparison Based on Deposits:
Expedited vs. Non-Expedited
$7,500
Difference
of $435
$7,300
Monthly Payments
Most Expensive
“Non-Expedited”
Least Expensive
“Expedited”
$7,100
$6,900
$90k
$122k
$6,700
$6,500
$6,300
$122k
$150k
$6,100
$5,900
$5,700
$90,000 $100,000 $110,000 $120,000 $130,000 $140,000 $150,000
Investment Options
Non-Expedited
Expedited
In Additional To the General Findings
✓ The model’s general appearance and formatting made it difficult to read.
✓ The model had limited functionality.
✓ Basic BPM Standards were not met.
How the General Appearance was Improved
Numbers should have
applicable formatting
Highlights for Uncertain, Decision
and Input variable are too dark
Center “No”
in cell.
Q26 indicates cell
B10 is an Uncertain
Variable
Use indents and centering for ease of reading.
Also………..
Colors in chart a very similar to
highlights. (Confusion?)
Owner Deposit Limitations:
$90,000 to $150,000
designated for the deposit
limit their ability to accept
the Expedited renovation.
APR
7.3%
7.0%
6.5%
6.0%
5.5%
5.0%
Bank Quote
• The funds Terra Cotta
Amount to
Finance
$300,000.00
$310,000.00
$320,000.00
$330,000.00
$340,000.00
$360,000.00
$300,000
$310,000
$320,000
$330,000
$340,000
$360,000
Renovation Options
$450,000
$475,000
$150,000
$175,000
$140,000
$165,000
$130,000
$155,000
$120,000
$145,000
$110,000
$135,000
$90,000
$115,000
Additional Factors That Should Be Considered
• Opportunity Cost of being closed for 13 weeks versus 3 weeks.
•
•
•
•
(Net Income / 52 weeks ) * Weeks Required for Renovation
Instead of $372,564.20 of Net Income …..
$372,5654 - $28,860.66 = $343,703.54 (criteria shortfall)
However, Expedited ends up paying for itself ($28,861 vs. $25,000)
Profit by Year(s)
Profits; Year 1
Profits; Year 2 - 7
Total Profit
Stagnate
Expedited Non-Exped. Exped. Diff
$150,075
$141,417
$112,557
$28,861
$900,453 $1,259,135 $1,259,135
$0
$1,050,528 $1,400,552 $1,371,692
$28,861
Completion
Expedited
Non-Expedited
Difference
Weeks
3
13
10
Opportunity Cost
Expedited
$8,658.20
Non-Expedited $37,518.86
Difference
$28,860.66
Comparing the Most Viable Options
Category
Duration (Wks.)
Cost of Renovation
Deposit*
Bank Loan
Interest Rate
Total Interest Pmts.
Added Revenue
Total Revenue (7 Yrs.)
7-Year Out-of Pocket
Renovations
Total Interest Pmts.
Added Revenue
Total
Expedited
$
$
$
$
$
$
3
475,000
150,000
325,000
6.50%
56,540
2,670
372,564
Expedited
$ 475,000
$
56,540
$
-
Non-Expedited
13
$
450,000
$
137,000
$
313,000
7.00%
$
58,867
$
$
372,564
Difference
-10
$
25,000
$
13,000
$
12,000
-0.50%
$
(2,327)
$
2,670
$
-
Non-Expedited
$
450,000
$
58,867
$
(2,670)
Difference
$
25,000
$
(2,327)
$
(2,670)
$
20,003
Benefit?
(Exp. - Non.Exp.)
Yes
No
No
Yes
Yes
Yes
Yes
Neutral
* Net Present Value over 5 years (i.e., time value of money) has not been calculated.
Upon further review and analysis, the recommendation would be different.
Comparing the Options
Instead of a proposing a Non-Expedited renovation with a $137,000 deposit, it would be in the
owners’ best interest to proceed with an Expedited renovation with a $150,000 deposit.
Profit by Year(s)
Profits; Year 1
Profits; Year 2 - 7
Total Profit
Stagnate
Expedited Non-Exped. Exped. Diff
$150,075
$141,417
$112,557
$28,861
$900,453 $1,259,135 $1,259,135
$0
$1,050,528 $1,400,552 $1,371,692
$28,861
Relative to Terra Cotta’s profits over 7
years, Expedited renovations provides
$28,861 of additional profits.
Total Renovation Costs
Deposit
Loan Costs; Mthly Pmts.
Total Renovation Costs
Stagnate
Relative to Total Cost, Non-Expedited
approach with a $150,000 deposit is the
least expensive.
Summary
7-Yr. Change in Cash
Stagnate
Expedited Non-Exped. Exped. Diff
$1,050,528
$869,012
$862,717
$6,296
Expedited Non-Exped. Exped. Diff
$0
$150,000
$150,000
$0
$0
$381,540
$358,975
$22,565
$0
$531,540
$508,975
$22,565
However, if remaining “stagnate” is not
an option, the Expedited renovations
provides the best option (i.e., a positive
change in cash of $6,296).
VI) Additional Excel Functions, Skills and
Tools
Project Overview and Background…continued
• Our presentation will include the following Excel function and skills that were not part of the
Grader Project:
✓
✓
✓
✓
Amortization Schedule
Tables
Conditional Formatting
Chart Tools; Change Colors
Improvements in Functionality
Comparative Amortization Schedules
Non-Expedited and $137,000 Down Payment
Loan
Interest
Term
Payments
$
313,000
7.0%
5.00
$6,197.78
Month
1
2
3
4
5
6
7
8
9
10
11
12
53
54
55
56
57
58
59
60
Total Interest Paid
Total Principal Paid
Total Payments
$
$
$
58,867
313,000
371,867
Loan
Interest
Term
Payments
Beg. Bal.
$ 313,000
$ 308,628
$ 304,231
$ 299,808
$ 295,359
$ 290,884
$ 286,383
$ 281,856
$ 277,302
$ 272,722
$ 268,115
$ 263,481
$
$
$
$
$
$
$
$
$
$
$
$
Interest
1,826
1,800
1,775
1,749
1,723
1,697
1,671
1,644
1,618
1,591
1,564
1,537
Principal
$4,371.94
$4,397.44
$4,423.10
$4,448.90
$4,474.85
$4,500.95
$4,527.21
$4,553.62
$4,580.18
$4,606.90
$4,633.77
$4,660.80
$
$
$
$
$
$
$
$
$
$
$
$
Subtotal
6,197.78
6,197.78
6,197.78
6,197.78
6,197.78
6,197.78
6,197.78
6,197.78
6,197.78
6,197.78
6,197.78
6,197.78
$
$
$
$
$
$
$
$
$
$
$
$
End. Bal.
308,628
304,231
299,808
295,359
290,884
286,383
281,856
277,302
272,722
268,115
263,481
258,820
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
282
247
213
178
143
107
72
36
$5,915.99
$5,950.50
$5,985.21
$6,020.13
$6,055.24
$6,090.57
$6,126.10
$6,161.83
$
$
$
$
$
$
$
$
6,197.78
6,197.78
6,197.78
6,197.78
6,197.78
6,197.78
6,197.78
6,197.78
$
$
$
$
$
$
$
$
42,390
36,439
30,454
24,434
18,378
12,288
6,162
0
48,306
42,390
36,439
30,454
24,434
18,378
12,288
6,162
Expedited and $150,000 Down Payment
$
325,000
6.5%
5.00
$6,359.00
Month
1
2
3
4
5
6
7
8
9
10
11
12
53
54
55
56
57
58
59
60
Total Interest Paid
Total Principal Paid
Total Payments
$
$
$
56,540
325,000
381,540
Beg. Bal.
$ 325,000
$ 320,401
$ 315,778
$ 311,129
$ 306,456
$ 301,757
$ 297,032
$ 292,282
$ 287,506
$ 282,705
$ 277,877
$ 273,023
$
$
$
$
$
$
$
$
$
$
$
$
Interest
1,760
1,736
1,710
1,685
1,660
1,635
1,609
1,583
1,557
1,531
1,505
1,479
Principal
$4,598.58
$4,623.49
$4,648.53
$4,673.71
$4,699.03
$4,724.48
$4,750.07
$4,775.80
$4,801.67
$4,827.68
$4,853.83
$4,880.12
$
$
$
$
$
$
$
$
$
$
$
$
Subtotal
6,359.00
6,359.00
6,359.00
6,359.00
6,359.00
6,359.00
6,359.00
6,359.00
6,359.00
6,359.00
6,359.00
6,359.00
$
$
$
$
$
$
$
$
$
$
$
$
End. Bal.
320,401
315,778
311,129
306,456
301,757
297,032
292,282
287,506
282,705
277,877
273,023
268,143
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
269
236
203
169
136
102
68
34
$6,090.04
$6,123.03
$6,156.19
$6,189.54
$6,223.07
$6,256.77
$6,290.66
$6,324.74
$
$
$
$
$
$
$
$
6,359.00
6,359.00
6,359.00
6,359.00
6,359.00
6,359.00
6,359.00
6,359.00
$
$
$
$
$
$
$
$
43,564
37,441
31,285
25,095
18,872
12,615
6,325
0
49,654
43,564
37,441
31,285
25,095
18,872
12,615
6,325
VII) Best Management Practices
(“BPM Standards”)
Use of BPM Standards
Formats & Styles:
2. Cell Data Alignment (BPMC 4-2) – This Standards recommends the formatting of data
to be consistent and that the numbers themselves are in alignment within various ranges.
• Before: Difficult to read large numbers without commas. Also, money is displayed in 2 and 3
decimals.
2016 Revenue
2016 Costs
2016 Profits
750377.26
600301.808
150075.452
• After: Formatted for Currency with commas and no decimals. Also toned-down the fill color.
2016 Revenue
2016 Costs
2016 Profits
$750,377
$600,302
$150,075
Use of BPM Standards
Formats & Styles:
3. Use of Purpose-Based Styles (BPMC 4-1): This Standard recommends the incorporation
of consistent “Styles” to indicate the purpose of the cells.
•
Inputs =
•
Decision Variables =
•
Uncontrolled Variables =
2016Revenue
Revenue
2016
2016Costs
Costs
2016
2016Profits
Profits
2016
RenovationCosts
Costs
Renovation
Investment
InvestmentAmount
Amount
Amount
AmountNeeded
Needed
totoFinance
Finance
APR
APRfor
forLoan
Loan
Term
Term
$750,377
750377.26
$600,302
600301.808
$150,075
150075.452
$450,000
$450,000
$137,000
$150,000
313000
$300,000
0.07
7.3%
55 years
years
Expedite
Expedite
Project?
Project? No
No
Chloe's Coffee Shop
Loan Analysis - Option 1
Loan Amount
Interest Rate
Term of Loan (years)
Payments per Year
Monthly Payment (end of period)
Monthly Payment (beginning of period)
$
218,000.00
7.20%
10
12
$2,553.69
$2,538.46
Loan Analysis - Option 2
Loan Amount
Monthly Payment
Term of Loan (years)
Payments per Year
Interest Rate (end of period)
Interest Rate (beginning of period)
$218,000.00
$2,200.00
10
12
3.93%
4.00%
Loan Analysis - Option 3
Loan Amount
Interest Rate
Monthly Payment (end of period)
Total Number of Payments (end of period)
Term of loan in years
$218,000.00
7.20%
$2,200.00
150.91
12.58
Loan Analysis - Option 4
Loan Amount
Interest Rate
Term of Loan (years)
Payments per Year
Quarterly Payments
$218,000.00
7.2%
10
4
$7,692.29
Quarter Principal Payments
1
$3,768.29
2
$3,836.12
3
$3,905.17
4
$3,975.47
5
$4,047.02
6
$4,119.87
$3,924.00
$3,856.17
$3,787.12
$3,716.83
$3,645.27
$3,572.42
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
Totals
$4,194.03
$4,269.52
$4,346.37
$4,424.61
$4,504.25
$4,585.33
$4,667.86
$4,751.88
$4,837.42
$4,924.49
$5,013.13
$5,103.37
$5,195.23
$5,288.74
$5,383.94
$5,480.85
$5,579.51
$5,679.94
$5,782.18
$5,886.26
$5,992.21
$6,100.07
$6,209.87
$6,321.65
$6,435.44
$6,551.28
$6,669.20
$6,789.24
$6,911.45
$7,035.86
$7,162.50
$7,291.43
$7,422.67
$7,556.28
$218,000.00
$3,498.26
$3,422.77
$3,345.92
$3,267.69
$3,188.04
$3,106.97
$3,024.43
$2,940.41
$2,854.88
$2,767.80
$2,679.16
$2,588.92
$2,497.06
$2,403.55
$2,308.35
$2,211.44
$2,112.79
$2,012.36
$1,910.12
$1,806.04
$1,700.08
$1,592.22
$1,482.42
$1,370.65
$1,256.86
$1,141.02
$1,023.10
$903.05
$780.84
$656.44
$529.79
$400.87
$269.62
$136.01
$89,691.74
Chloe's Coffee Shop
Discount Rate
Total Initial Investment
3.50%
-$218,000.00
Cash Flows
Year 0 -$218,000.00
Year 1 $10,000.00
Year 2 $12,300.00
Year 3 $15,100.00
Year 4 $18,600.00
Year 5 $22,800.00
Year 6 $28,100.00
Year 7 $34,600.00
Year 8 $42,500.00
Year 9 $52,400.00
Year 10 $64,400.00
Net Present Value
Internal Rate of Return
$18,600.69
4.75%
Chloe's Coffee Shop
Cost of Asset
Salvage Value
Useful Life
$24,000.00
$9,000.00
5
Straight Line Depreciation
End of Year
1
2
3
4
5
Depreciation
expense for
year
$3,000.00
$3,000.00
$3,000.00
$3,000.00
$3,000.00
Accumulated
depreciation at
end of year
$3,000.00
$6,000.00
$9,000.00
$12,000.00
$15,000.00
Book value
at end of
year
$21,000.00
$18,000.00
$15,000.00
$12,000.00
$9,000.00
Declining-Balance Depreciation
End of Year
1
2
3
4
5
Depreciation
expense for
year
$3,511.58
$3,511.58
$2,886.52
$2,372.72
$1,950.38
Accumulated
depreciation at
end of year
$3,511.58
$7,023.17
$9,909.69
$12,282.41
$14,232.79
Book value
at end of
year
$20,488.42
$16,976.83
$14,090.31
$11,717.59
$9,767.21
Number of Customers
Mean
Standard Error
Median
Mode
Standard Deviation
Sample Variance
Kurtosis
Skewness
Range
Minimum
Maximum
Sum
Count
158.6451613
3.703107787
156
178
20.61803157
425.1032258
-0.824359363
-0.276802332
70
122
192
4918
31
Number of Custome
143
155
132
178
166
154
123
187
156
153
153
156
152
152
178
128
146
173
125
173
165
128
192
163
176
187
167
188
165
182
122
Number of Customers
143
155
132
178
166
154
123
187
156
153
153
156
152
152
178
128
146
173
125
173
165
128
192
163
176
187
167
188
165
182
122
Chloe's Coffee Shop
Day of the Number of Moving
Month Customers Average
143
155
132
178
166
154
123
187
156
153
153
156
152
152
178
128
146
173
125
173
165
128
192
163
176
187
167
188
165
182
122
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
150.1429
156.4286
156.5714
159.5714
156
154.5714
154.2857
158.4286
157.1429
153.1429
152.1429
155
150.5714
153.5714
155.4286
148.2857
157.4286
159.8571
160.2857
169.1429
168.2857
171.5714
176.8571
175.4286
169.5714
Mean
Median
Mode
Min
Max
Range
Variance
Standard Deviation
158.65
156
178
250
122
192
150
70
425.10
20.62
200
Value
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
100
50
0
1
3
5
7
9
Moving Average
Actual
Forecast
9 11 13 15 17 19 21 23 25 27 29 31
Data Point
s
Chloe's Coffee Shop
Month
January
February
March
April
May
June
July
August
September
October
November
December
Total Customers
7,689
7,572
6,589
5,672
4,889
4,324
5,638
6,583
7,203
7,763
7,878
8,112
Average
Standard Deviation
Desired Minimum Quantity
Probability
6,659
1265.58649
7,100
36.38%
Standard Deviation
4
3.9
3.8
3.7
3.6
3.5
3.4
3.3
3.2
3.1
3
2.9
2.8
2.7
2.6
2.5
2.4
2.3
2.2
2.1
2
1.9
1.8
1.7
1.6
1.5
1.4
1.3
1.2
1.1
1
0.9
0.8
0.7
0.6
Quantity
46,217
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
33,695
41,807
41,807
49,685
65,137
72,709
72,709
Probablilty
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0.5
0.4
0.3
0.2
0.1
0
-0.1
-0.2
-0.3
-0.4
-0.5
-0.6
-0.7
-0.8
-0.9
-1
-1.1
-1.2
-1.3
-1.4
-1.5
-1.6
-1.7
-1.8
-1.9
-2
-2.1
-2.2
-2.3
-2.4
-2.5
-2.6
-2.7
-2.8
-2.9
-3
-3.1
-3.2
-3.3
-3.4
-3.5
-3.6
-3.7
-3.8
-3.9
-4
72,709
79,912
79,912
79,912
79,912
93,084
66,740
66,740
66,740
66,740
66,740
66,740
61,068
55,430
55,430
55,430
55,430
55,430
50,541
50,541
50,541
50,541
50,541
46,217
46,217
46,217
46,217
46,217
46,217
46,217
46,217
46,217
46,217
46,217
46,217
46,217
46,217
46,217
46,217
46,217
46,217
46,217
46,217
46,217
46,217
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
lower limit
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
upper limit
11722
11595
11469
11342
11215
11089
10962
10836
10709
10583
10456
10330
10203
10076
9950
9823
9697
9570
9444
9317
9191
9064
8937
8811
8684
8558
8431
8305
8178
8051
7925
7798
7672
7545
7419
6659
6659
6659
6659
6659
6659
6533
6406
6280
6153
6027
5900
5773
5647
5520
5394
5267
5141
5014
4888
4761
4634
4508
4381
4255
4128
4002
3875
3748
3622
3495
3369
3242
3116
2989
2863
2736
2609
2483
2356
2230
2103
1977
1850
1724
1597
7292
7166
7039
6912
6786
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
6659
Monthly Customer Distribution
Monthly Customer Distribution
0%
Probability
0%
0%
0%
0%
0%
0%
20,000
40,000
60,000
Total Monthly Customers
80,000
100,000
Chloe's Coffee Shop
25
65
18
32
34
63
25
22
45
47
15
19
16
25
65
72
41
48
36
37
25
26
28
29
33
58
56
Visits per month
29
3
29
7
6
22
25
31
27
15
19
23
2
13
5
8
24
16
28
5
30
22
7
18
14
11
8
Covariance
Correlation
-63.1667
-0.3982
Age-Visits Correlation
35
30
Visits per Month
Age
25
20
15
10
5
0
0
10
20
30
40
Age
50
60
60
70
80
Purchase answer to see full
attachment