MATH 1053 - Quantitative Methods for Business - Orange Wines in the Adelaide Hills - Mathematics Assignment Help

Assignment Task:

Orange Wines (OW!) is a winery in the Adelaide Hills near Woodside and has been in your family for many generations.  You have recently moved back home after spending 5 years working in a major city, learning a lot about business operations and putting your Quants knowledge to brilliant use.  

Your return signals that you are taking over the family winery business (although you’re not scared of getting your hands and boots dirty on the block when needed) and you have big dreams about where and how you’d like Orange Wines to grow.  

First on your list is to create a cellar door at the winery, which you plan to call Cellar Door, with a bright orange door as the feature entrance.  However, you need to check whether the financial data support your dream of becoming a must-visit cellar door on every tourist itinerary and wine tour in the region.  You have a good friend who happens to be an award-winning architect (and quite likes to drink wine) and will happily design the best layout for Cellar Door in exchange for a few cases of the premium 2020 Noble Rascal vintage you’re about to release.

Speaking of the vintage, it turns out the Adelaide Hills is the only place in the world that can produce the grapes needed for the Noble Rascal 2020 vintage so to capitalise on that exclusivity, you intend to charge $5 for premium wine tastings at Cellar Door while offering standard tastings for free.

In case you haven’t noticed, it’s already 2020 and just before you release the vintage you’ve seen from reports that consumer spending has decreased considerably, forcing other long-standing businesses to close down.  You are keen to understand how reductions in consumer spending could potentially impact Cellar Door as it prepares for its opening.  Understanding the revenue streams for Cellar Door is also important to help you decide how to use your future “future funds” money so you need a quick analysis of that information too – perhaps while you’re eating a doughnut?

(a)By filling in the Excel worksheet Appendix 1 uses Excel to calculate the relative percentage change of Net Profit for Orange Wines for each year from 2016 to 2019, i.e. from 2016 to 2017, etc.  Table 1 is given to you in the worksheet so you don’t have to type it out yourself. (MATH1053)

(b)Using Excel, calculate the Asset to the Turnover ratio for Orange Wines using Sales and Total Assets  for the years 2017-2019.  Table 2 is given to you in the worksheet so you don’t have to type it out yourself.
Enter your calculations in the Excel spreadsheet as explained below.

(c)Using Excel, create sparklines for the Relative Percentage Change table using the relative percentage values you calculated in (a) for the years 2016-2019 (there should be 3 values in each sparkline).  
What is a sparkline I hear you ask? A sparkline is a tiny graph that appears in text like this – exciting!!! ? You can customize them to change the line color and individual marker colors as well – I know how good is that?!? 

(d)Finally, you have an upcoming meeting with a brilliant architect friend of yours to discuss the design for Cellar Door.  You’re hoping he’ll design a warm and inviting space that encourages customers and spending ?  You plan to offer paid premium wine tastings to take advantage of the growing preference for premium wines both locally and overseas.  The latest Wine Australia report has provided data for premium wine tastings by layout in cellar doors and whether each cellar door charged for the premium tastings.

It is now a very fast 10 months later (time flies from Appendix 1 to Appendix 2) and to coincide with the grand opening of Cellar Door, complete with an orange front door of course, Orange Wines would like to prepare a press release for customers promoting the Cellar Door experience.

Orange Wines estimates an initial investment of $95,000 is required for vintage (picking the grapes and producing the wine) and equipment to ensure the release of their 2020 Noble Rascal (a Pinot Noir), to be made available at Cellar Door for 12 months.  They know it’s a huge drawcard to Cellar Door as the staff handpicked, foot stomped, basket pressed and racked the grapes before maturing them in oak, blending and bottling the wine, to present it to you at Cellar Door – so it’s definitely worth trying!  

As it happens this pretty spot in the Adelaide Hills is the only location where this drop is available.  Orange Wines charges a $5 premium wine tasting fee per person and expects 950 paying customers per month.  In addition, they estimate that on average 50% of customers will purchase a bottle of the cellar door for $32 per bottle.  

To increase patronage and promote the region, Orange Wines will also invite a popular local chef to offer a dining experience with meals paired with the 2020 Noble Rascal.  There are 4 dining sessions running at the end of each quarter for 12 customers at each dining session.  The dining package is expected to bring a cash inflow of $180 per customer and each event is expected to be sold out.

(a)Use EXCEL to calculate the net present value of the proposed 2020 Noble Rascal release.  Assume that increases in profits are realised at the end of each month.  The cost of capital for Orange Wines is 4.73% per year compounded monthly.  
To complete this question use the worksheet Appendix 2 NPV.
Enter your calculations in this worksheet where indicated. (MATH1053)

(b)To gauge the viability of the Cellar Door press release, you need to know how long it will take for Orange Wines to completely recoup the initial outlay of $95,000.  You can’t be bothered doing more calculations (who can blame you?) so you produce a rough visualization of your analysis that will give you the upper limit on the time it will take to completely recoup the initial outlay made by Orange Wines.  Include the graph here and the infographic where indicated.

(c) As a sign the future is looking bright for Orange Wines, they just received the fantastic news their 2019 Noble Rogue vintage has scooped the pool at the recent Wine Industry Awards (Best Vintage 2019, Critic’s Choice and Best Oxymoronic Wine Name) with combined prize money of $50,000.
Rather than spend the prize money, Orange Wines have decided to invest it as part of their future funds.  The prize money is invested at an interest rate of 2.5% per annum compounded monthly for a 3 year period.  Orange Wines have also committed to investing regular deposits of $5,000 in a second investment account that attracts 2.5% per annum compounded quarterly.  
Orange Wines wants to combine the maturity of the two accounts in 3 years’ time.  If  they have at least $100,000 they will use it to grow the cellar door experience with an upgraded outdoor area and interactive wine tasting tours.

Cellar Door is about to open its orange doors to the public!  You are doing some last-minute number-crunching as your original estimates about operating costs need to be updated due to changes in consumer spending. You also have a target profit in mind — $25,000 each month — to put towards the Cellar Door future fund for expansion and investment.  You need to know whether the changing financial climate (economic downturn) will affect the future fund as well.

You revisit the monthly sources of income to Cellar Door which includes the sales of a case of wine at $145 per case.  From the research, you have assumed each customer will purchase one case.  You have further assumed each customer will spend $55 at the Cellar Door.  While the Cellar Door offers standard (free) and premium $5 wine tastings to each customer, you now adjust your estimate and assume only half the total monthly customers will pay for a premium wine tasting.  Finally, you assume each customer will purchase one item of merchandise.  All merchandise is priced at $25 and includes wine glass sets and complementary products such as olive oil and chocolate made to pair with Orange Wine’s vintage.

(a) Follow the Excel instructions below to produce a doughnut chart of the sources of income to the Cellar Door per customer
(b) You need to calculate break-even and other relevant information, however, you want to produce two estimates: one based on the original income per customer (best-case scenario) and a more conservative income (worst-case scenario).  The worst-case scenario is a 15% reduction of the income you calculated in part (a).  Business often produces a sensitivity analysis by calculating several estimates to allow for best and worst-case scenarios, which is what you’re doing here, even though we all know Cellar Door will be a raging success 
  Using the relevant information calculate:
1.The break-even number of customers using the original income stream from part (a).
2.The break-even number of customers using the conservative income estimate.  All costs are unchanged in this scenario.
3.The income from serving the break-even number of customers using the original and conservative income streams respectively.  Use a rounded break-even volume (x value) in your calculations.
(c)What is the impact on the contribution margin when making the income stream conservative? Explain the effect of this change on the break-even number in part (b) including showing the calculation of the two contribution margins.  Do not show any other calculations.
(d)In Excel, produce a single break-even graph of the two-income estimates (original and conservative) and include it here – you will also include a copy in the infographic where requested.  
(e)One last thing you need to check is the viability of your target profit of $25,000 each month.  You want to use this profit to put towards the Cellar Door future fund to use for future expansion and investment.  Assuming the most conservative income stream to understand the worst-case scenario, and the fixed and variable costs as above, calculate the number of customers Cellar Door needs to realize this profit each month.  

This MATH1053: Mathematics Assignment has been solved by our Mathematics Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.