1. Develop and calculate the NPV on an Excel Spreadsheet for the “Option 2 Plan” for the Corkford Brewery acquisition while considering the constraints and operating conditions laid out in the case as
SCM 801
To Brew or Not to Brew: The Corkford Brewery Acquisition
Assignment
IMPORTANT!!! You cannot utilize any research on the Internet to seek answers for this assignment. I am looking for your analysis only--these assignments are not research ones and do not require any information other than information given in the course lessons.
I. Assignment Objectives:
Utilize NPV as a tool to compare various operating options if they acquire the brewery. In the competitive environment that exists today, firms need to constantly evaluate their supply chains and determine methods to be more responsive and efficient.
Evaluate and quantify the options you develop.
Develop a Professional PowerPoint slide presentation that engages your audience to accept your proposal.
II. Assignment Overview:
MacKinnon Industries (MI) is considering acquiring Corkford Brewery. One of the plans is given in part “V. Example of Option 1 Plan” and is shown in that example below. It will be referred to as “The Option 1 Plan.” This plan continues to brew and sell 4 different beers, Lager, Amber Ale, Lime, and Premium Light.
Major inflows and outflows of dollars are given on the spreadsheet below and in the case itself.
Assume for all three options that we will calculate NPV at using a discount rate of 10% for 5 years starting at year 0 and proceeding to years 1, 2, 3 and 4. Year 0 will have a factor of 1.00.
Based on the assignment questions you are to develop two alternative plans:
An Option 2 Plan: Just Amber Ale and Corkford Lime with a $2 million asset sale (an inflow of money) in year 2.
An Option 3 Plan Similar to the Option 1 Plan but also including a contract brewing component.
You are to develop a five to seven PowerPoint slide deck to be presented to the Board of Directors (BOD) from MI. You are to assume that you are working for a consulting firm and the Board of Directors at MI will evaluate your team’s report. You are in competition with all the other teams in the class to obtain the BOD’s approval and be selected to implement the plan.
Please consider professionalism and efficiency in your communication.
Your calculations are required to be included (Excel files are expected for at least the Option 2 Plan and the Option 3 Plan.)
Since there is not much space—use it wisely! You need to employ professional language, grammar, appropriate structure, etc.
Although you can utilize Excel you cannot use its NPV function. It needs to be utilized in a specific way and many times it creates more problems than it solves at this stage of understanding of NPV.
Your responses to the MI assignment questions are to be organized into a professional PowerPoint presentation as described above. Prepare the presentation in the "Normal" view, utilizing a portrait format to make liberal use of the “Notes” feature on all slides. PowerPoint slides, by necessity, must be brief. Therefore, use the notes feature to justify or more fully explain the bullet point content of a slide.
Do not leave the instructor guessing on the meaning of a particular slide. You might think of the notes as containing material you would verbalize when presenting your slides. Please, do not write volumes in the notes section. Be concise but clear on the meaning of each slide. All your extra information about a slide must fit on that 8.5 by 11 portrait version of the “Notes” page for that slide. Also do not re-write the question on the notes slide. Please just write Q1, Q2, Q3, etc. at the top of the notes section.
The presentation should begin with a brief introduction giving some background on the case and stating the purpose (Objectives/Goals Type Slide) and organization of the presentation (Agenda Type Slide). The introduction should then be followed by your response to each of the assignment questions. There may be multiple sets of questions associated with this case and some of the questions contain multiple parts or sub-questions. Please be certain to address each part of each question set. Also, please be sure that you make it clear to the reader that you have addressed each part of each question. This information should be very clear in the "Notes” section and will have to appear on the slide portion of the presentation.
The final slide should be a summary slide which explains the next action steps in the process that should occur. Please do not say items like “email me if you want more information’…. I am looking for concrete steps.
When you are asked to make a choice or take a position in answering a question, be certain to present the argument or justification for your choice. In support of your position, it is often useful to draw on the literature that you have read for the course.
Note: Arguments can be used to positively affirm a particular position as well as to indicate that alternatives are not worthy of support. In other words, sometimes you can support a position by effectively arguing against other alternatives.
Please place citations on the slides as needed that illustrate the problems/questions/goals/objectives, etc. you are trying to obtain.
Consequently, when you submit information and the report, it needs to be compiled in a professional, well-analyzed, and thoughtful manner. Additionally, please make sure all items are professionally formatted.
Note: See the Course Schedule for due dates for this assignment
III. Assignment Formatting:
Your submittal should be organized into a professional PowerPoint presentation of approximately 5 - 7 slides. Is the presentation concise and succinct?
All extra information about a slide must fit on that 8.5 by 11 portrait version of the “Notes” page for a slide.
The report must be formatted for single sided printing (no double-sided reports). Please preview print before you submit to make sure it is formatted correctly.
Do not use any pdf type files in your submittals---PPT or Excel only. Excel should be formatted to print on a single page. PPT should be formatted to print the slide and notes section.
The presentation must be formatted for single sided printing (no double-sided reports). Please preview print before you submit to make sure it is formatted correctly.
Please read the attached grading rubric for additional information that will be considered when the assignment is graded.
Use bullets on the slides…no long sentences or paragraphs.
Does the PowerPoint deck contain an agenda slide and objectives slide? (Not counted as part of the total}
Does the PowerPoint deck have a summary slide? (Not counted as part of the total)
Please use proper citations, when necessary, on the appropriate slides. Do not plagiarize.
Please include and use the enclosed cover page or similar page transcribed to PPT.
Points will be deducted for not following formatting directions and not answering all assignment questions.
All Excel worksheets (tabs) must fit on one page and be in a landscape mode. One spreadsheet/tab per page.
Any appendices type slides must be numbered and have a title. You cannot have more than one appendix per slide. You can only use one side of the paper.
All work must be completed in a professional manner.
Please note: We do not have pre-grading of any of the assignments.
You will have a least two documents downloaded to the drop box:
The actual document in PPT type file extension. These will include use of the ‘notes’ portion. No pdf file extensions.
An Excel Workbook showing your financial calculations on two of the tabs. Additional tabs for the sensitivity analysis for all three options should be included. Please include a copy of the given Option 1 Plan on the first tab. No pdf file extensions.
Option 2 Excel Plan
Option 3 Excel Plan
Plus, three files (tabs) showing the sensitivity analysis for all three options utilizing the 10% change. No pdf file extensions
Please Note: The Option 1 Plan will be provided in the Excel Spreadsheet (including formulas) attached with the assignment document. Please utilize its layout for part b above.
IV. Helpful Hints:
A. General constraints and inputs:
The costs, volumes, etc. are given on the Excel Spreadsheet for Option 1. Other relevant (similar) information can be found in the case.
Please utilize the Excel Spreadsheet given in this assignment drop box.
Copy the formulas as needed from the tab labeled Option 1 for the tabs listed as Option 2 and Option 3.
Obviously, some formulas and some calculations might need to be changed for Option 2 and 3.
Depreciation = $200,000/year (this was not in the original case developed by the author).
Discount Rate = 10% (this was not in the original case developed by the author).
Yr. 0 = use 1.0 for the NPV factor
Yr. 1 = use correct NPV factor
Yr. 2 = use correct NPV factor
Yr. 3 = use correct NPV factor
Yr. 4 = use correct NPV factor
Units are important. Please verify you are using the correct ones.
Assume Canadian dollars are equivalent to US dollars. I know this is not true, but for this academic exercise it has no impact.
Fixed costs are generally the same across all three options. See Option 1 for details.
Option 3 has an additional fixed administrative cost of $200,000.
Direct Labor calculation for all three options:
13 workers * $18.50/hr. * 30 weeks * 35 hrs./ wk. = $252,525
$18.50/hr. (i.e. the average rate of pay)
We will not consider the cider option is this assignment.
We will not consider any increase or decrease in volumes, costs, etc. over the period of time we are using.
B. Guidance on specific plans:
Option 1:
Brew and sell only these four products: Lager, Amber Ale, Lime, and Premium Light
Option 2:
Brew and sell only two products: Amber Ale and Lime
$2 million asset sale in Year 2
Option 3:
Brew and sell original four products: Lager, Amber Ale, Lime, and Premium Light plus…..
Contract brew for an additional 10000 hectoliters (hl)
Because the total volume of beer brewed exceeds 5000hl, the Federal excise tax is increased for each beer brewed in this option. See Exhibit 5.
No BRI fee for this option…firm requesting contract brewing will pay this fee.
V. Example of the “Option 1 Plan” Excel Spreadsheet:
This Excel Spreadsheet shows the “Option 1” that MI is considering.
The actual working copy of the spreadsheet is attached to the drop box.
Please Scroll Down to See the Assignment Question
VI. Assignment Questions: Please answer the following questions in your slides: Create a seven-slide professional business PowerPoint presentation, which communicates succinct and concise analysis and recommendations (Remember: some supervisors will not read beyond the headline of each slide). One slide should be dedicated to each question below and one slide should be dedicated to an overall synthesis and recommendation. The ‘notes’ section should be utilized to provide further detail and context to the slides. Note the slides above do not include the agenda, objective nor the summary slide.
Develop and calculate the NPV on an Excel Spreadsheet for the “Option 2 Plan” for the Corkford Brewery acquisition while considering the constraints and operating conditions laid out in the case as well as in the ‘Helpful Hints’ section of this document. Interpret the drivers of the NPV and the implications to the broader business decision. (40 points)
List all the underlying costs and volumes with this plan.
For example, the variable costs for Amber Ale is $122.063 per unit
Develop the actual Excel Spreadsheet showing your results.
Please use the format provided in the example of the Option 1 Plan listed above (Part V. Example of “Corkford Brewery “Option 1 Plan”). Your team’s Excel Spreadsheets should look like this example.
Include the synthesis of your findings in the team’s PowerPoint Presentation from the Excel spreadsheet depicting your work.
Develop and calculate the NPV on an Excel Spreadsheet for the “Option 3 Plan” for the Corkford Brewery acquisition while considering the constraints and operating conditions laid out in the case as well as in the ‘Helpful Hints’ section of this document. Interpret the drivers of the NPV and the implications to the broader business decision. (40 points)
List all the underlying costs and volumes with this plan.
For example, the variable costs for Amber Ale is $122.063 per unit
Develop the actual Excel Spreadsheet showing your results.
Please use the format provided in the example of the Option 1 Plan listed above (Part V. Example of “Corkford Brewery “Option 1 Plan”). Your team’s Excel Spreadsheets should look like this example.
Include the synthesis of your findings in the team’s PowerPoint Presentation from the Excel spreadsheet depicting your work.
Add a summary table that compares all three plans and shows all major cost categories. (20 points)
Show the summary table.
Which plan would you recommend? Explain!
Let us consider a form of sensitivity analysis for this problem. Chose a variable (interest rate and time period cannot be used) and explore the impact on your plans. This question has two parts: (20 points)
For a + and -10% change in a variable (across all three plans) how does it impact your recommendation?
Does this sensitivity analysis change your thoughts on your recommendation? Explain!
