Woodbury University Winery Operation Forecast for Fiscal Year 2022 Essay
Description
Regression information is already completed in attached file. The rest of the instructions are in the attached file. Information needs to be based on projections of 2022 supposing that Covid will continue and there will be a new strand, etc. Just need the excel data (listed in instructions), I will do powerpoint presentation. These websites seem to have good info as well:https://sbe.sonoma.edu/wbj/view-articles/volume-4-…https://www.bevindustry.com/articles/93851-online-…
3 attachmentsSlide 1 of 3attachment_1attachment_1attachment_2attachment_2attachment_3attachment_3.slider-slide > img { width: 100%; display: block; }
.slider-slide > img:focus { margin: auto; }
Unformatted Attachment Preview
Winery Project
You started a winery in L.A. County in 2014. Your winery is a corporation. You are CEO, CFO and COO.
You sell your wine to wholesale distributors throughout the U.S. You have to present the next years
winery-operation forecast to shareholders on October 5 (your class presentation).
You have to do:
1. Read Ross et al. (Essentials of Corporate Finance, 10th edition) pp. 293-297, which is in Moodle. You
need to understand how to do scenario analysis and sensitivity analysis. If you have not taken FINA
607, you may not be familiar with some terms on these pages. For the purpose of your
understanding, interpret NPV as Net Income, ignore Cash Flow and IRR. All you need to do is
to learn how to do these analyses.
(a) Finish your base case that includes all results (the profit graph, income statement, DPS, total
return, cash distributions). Base case comes directly from your regression results and is
considered to be (interpreted as) the status quo case, i.e., the current COVID situation continues
in 2022.
(b) Read pp. 293-295 of Ross et al.
(c) Understand how worst case and best case are created. These worst and best cases
should be as sensible (realistic) as possible. YOU HAVE TO JUSTIFY YOUR WORST CASE AND
BEST CASE NUMBERS. You have to do some research so that your numbers are evidencebased.
(d) Prepare a table similar to the table at the top on p. 295 of Ross et al.
(e) Prepare a table similar to the second table on p. 295 of Ross et al. Exclude Cash Flow, Net
Present Value and IRR from your table. Include Net Income in your table. You may want to
include other things in your table that are important for your shareholders to know. (Remember
your presentation is for your shareholders.) I do not tell what are other things. You have to
think.
(f) Sensitivity analysis is optional. You can learn more if you decide to do.
2. Do the regression exactly as we did in the class together. You will have historical data that have one
number changed (and all other numbers remain the same). Although only one-number change and a
very small change, your regression results will change. Therefore, you have to re-estimate the
demand equation. This is your base case. You have to explain the results in your report.
Read (a) above.
3. In addition to your base case, you have to conduct the scenario analysis and/or the sensitivity
analysis. These analyses are to capture the magnitude of deviations in 2022 operation results from
your base case. These deviations arise from the assumption of more deadly new COVID variants in
2022. If these variants emerge, how does your base case change? The scenario and/or sensitivity
analysis answers this question. I will not give any instruction of how to do. You decide on your own.
Remember that you have to do this analysis (or analyses) to answer your shareholders concern
about your 2022 winery operations if COVID hits again in 2022. You have to do a little research on
the impact of COVID on the wine industry. The more evidence-based your analysis (analyses) is, the
better. The more realistic your analysis (analyses) is, the better. You have to show your references in
your report.
In conducting the scenario and/or sensitivity analysis, use the pro forma income statement. When
you explain the analysis results, you have to show a summary table or tables (in the main text) and
these pro forma income statements (in the appendix).
Read (b) (f) above.
4. Write a one-page report (see below for detail). Your grade is based on your report. Your project
report has to be complete and self-contained. You have to attach the regression results, graphs,
and dividend distributions (all in Excel spreadsheet) to your one-page report as appendices. Please
do not attach an Excel file. Please copy Excel results to Word file by Paste Special. Your project
report has be written by both project partners.
5. Prepare PPT slides for your presentation.
Requirements:
1. You will present your report on October 5 (last class). Both project partners have to speak. You have
to do the following.
Email your presentation (PowerPoint) and your report with appendices to me (before or) by 5
pm on October 5. After 5 pm on October 5, no changes/revisions are accepted.
Upload your presentation (PowerPoint) and your report with appendices to Moodle (before or)
by 5 pm on October 5?only one person (in the group) uploads. After 5 pm on October 5, no
changes/revisions are accepted.
The deadline is strictly on or before 5 pm on October 5. No report will be accepted after 5 pm
on October 5.
2. Your project grade is based on your report with appendices, not your presentation.
3. NO CHEATING. You are NOT allowed to work with another group. No collaboration between groups.
Please follow Woodbury academic honesty policy, Woodbury University views academic dishonesty
as one of the most serious offenses that a member of our community can commit (see our
syllabus).
Explain what is the
purpose of this
report
Explain what
methods you used
in Excel
Title
Introduction
..
Methodology
Findings
Conclusions
Demand Equation Estimation
1. Historical data of your winery
P = Price of a bottle of wine in dollars (standard 750 ml)
M = U.S. Median Household Income in dollars (see * )
Q = Quantity of bottles sold in a year
P Data ($)
15
17
19
21
23
25
26
27
2014
2015
2016
2017
2018
2019
2020
2021
2022
M Data ($)
51.000
52.000
59.000
61.000
66.000
68.000
71.000
76.000
77.500
Q Data
16.420
15.068
20.580
20.412
23.869
23.582
25.490
34.500
SUMMARY OUTPUT
Regression Statistics
Multiple R
R Square
Adjusted R Square
Standard Error
Observations
0,987515388
0,975186642
0,965261298
1127,648961
8
ANOVA
df
Regression
Residual
Total
SS
2
5
7
Coefficients
Intercept
X Variable 1
X Variable 2
-38953,16512
-2322,29253
1,772426446
MS
249873412
6357960,899
256231372,9
Standard Error
6039,124569
581,6134117
0,286427436
124936706
1271592,18
t Stat
-6,450134398
-3,992845563
6,188047031
Total fixed cost=FC/year=
Avg variable cost= AVC/bottle=
Q
137.076,70
12,08
Profit
1.000
2.000
3.000
4.000
5.000
6.000
7.000
8.000
9.000
10.000
11.000
12.000
13.000
14.000
15.000
16.000
17.000
18.000
19.000
-107211,1298
-78206,77748
-50063,64313
-22781,72673
3638,971726
29198,45224
53896,7148
77733,75942
100709,5861
122824,1948
144077,5856
164469,7585
184000,7134
202670,4503
220478,9693
237426,2704
253512,3535
268737,2187
283100,8659
1
0,9
0,8
0,7
0,6
0,5
0,4
0,3
0,2
0,1
0
0
0,1
20.000
21.000
22.000
23.000
24.000
25.000
26.000
27.000
28.000
29.000
30.000
31.000
32.000
33.000
34.000
35.000
36.000
37.000
38.000
39.000
40.000
296603,2952
309244,5065
321024,4999
331943,2754
342000,8329
351197,1724
359532,294
367006,1977
373618,8834
379370,3512
384260,6011
388289,6329
391457,4469
393764,0429
395209,421
395793,5811
395516,5232
394378,2475
392378,7537
389518,0421
385796,1124
0
Q=
P=
0,1
* I changed the actual data slightly
so that the demand equation can
be statistically better estimated
Actual M (2014) = $53,657 (Census)
Actual M (2019) = $68,703 (Census)
2020-2022 = My estimates
?
F
98,25218177
P-value
0,001332452
0,010397031
0,001607304
Collect historical data
Significance F
9,69871E-05
Lower 95%
-54477,22904
-3817,377401
1,036141281
Upper 95%
-23429,1012
-827,2076588
2,50871161
Lower 95.0%
-54477,22904
-3817,377401
1,036141281
77500
Profit Graph
0,1
0,2
0,3
0,4
0,5
0,6
0,7
0,8
0,9
0,1
0,2
0,3
35.000
395.793
0,4
0,5
0,6
0,7
0,8
0,9
Upper 95.0%
-23429,1012
-827,2076588
2,50871161
0,9
1
0,9
1
Purchase answer to see full
attachment
Explanation & Answer:
1 Page
Tags:
Microsoft Excel
global pandemic
COVID19
Regression Analysis
ANOVA
User generated content is uploaded by users for the purposes of learning and should be used following Studypool’s honor code & terms of service.
