Selected a business operations dataset from the internet or other sources which can be used for forecasting in the University Library.

Developed a minimum of three quantitative forecasts using Microsoft® Excel®.

Compared and contrasted each quantitative forecast developed.

Chose the one forecast determined to be the best for the firm and be prepared to explain why you chose this.

Evaluated the impact this forecast would have on the firm from a financial metrics standpoint.

Developed a report describing your Forecasting Project including details on all the assignment steps.

Business Analytics Assignment

Unit Learning Outcome (ULO)

ULO 2: Apply quantitative reasoning skills to analyse business performance.

This assignment assesses the ability to use the appropriate technique to analyse the data, correctly interpret the analysis output and draw appropriate conclusions.

ULO 4: Use contemporary data analysis tools to analyse business performance.

This assignment assesses proficiency in the use of data analysis tools within Microsoft Excel (one of the most widely used data analysis tools).

Graduate Learning Outcome (GLO)

GLO 1: Discipline-specific knowledge and capabilities: appropriate to the level of study related to a discipline or profession.

GLO 4: Critical thinking: evaluating information using critical and analytical thinking and judgement.

General Description / Requirements

The assignment requires that you analyse a data set, interpret and draw conclusions from your analysis, and then convey your conclusions in a written email.

Scenario

Supermart is one of Australia's leading supermarket chains. There are 150 stores in the chain. Originating from a family based chain of general stores, Supermart now has stores all over Australia, with the first one being established 30 years ago. In 2016, the company introduced an online channel for stores to enable customers, in selected suburbs, to make their purchase online. In terms of operation, individual store management has wide-ranging powers about day-to-day operations of their stores. However, broad company planning and direction take place in the company Head Office in Melbourne. Included in the Head Office is the Research and Analysis Department.

Having compared the company's position in the market and against the competitors, the General Manager – Grace Wong has asked you, Stephen Hennigsson, to conduct an exploratory and descriptive analysis to gain a better understanding of Supermart's performance.

Email from the General Manager
To: Stephen Hennigsson

From: Grace Wong

Subject: Analysis of the Store data

Dear Stephen,

The board is very keen to see how we have performed over the last financial year, in order to make sure we are keeping pace with our main competitors. As a result, can you investigate the following?

1. Can you provide me with an overall summary of Gross Profit?

2. The board wants to revisit the issue of whether it should now be compulsory for our stores to be open every day of the week, including Sunday, to aggressively compete with our major competitors. Can you provide a gross profit comparison for our stores that open on Sunday and those that do not?

3. As you know, our latest initiative has been the creation of our store online channel. I know many of our stores have included the online channel as part of their business operations. I have heard that Mall stores are leading the way when it comes to setting up online stores. Is there any evidence of this?

4. Wastage (unsold stock) is expensive to the organisation, especially if maintained at high levels.

a. Can you provide me with an overall breakdown of wastage?
b. I would also be interested in how the States compare when it comes to high levels of wastage in their stores?

5. I would like to see whether factors listed below provide any explanation in the variation of Sales between stores. If so, can you also indicate which factor is the most important?

a. Advertising expenses
b. Number of staff
c. Number of car spaces
d. Number of trading hours

Finally, I would also like an interactive Dashboard for me to explore performances of our stores. I am particularly interested in exploring our Sales and Gross Profit performance from different dimensions. I will leave the design details to you.

I look forward to your response and the Dashboard.

Sincerely,
Grace Wong General Manager

Assignment instructions

The assignment consists of three parts:

Part 1: Data Analysis
When conducting the analysis, you will apply techniques from descriptive analytics and visualisations, and hence will use various tables, graphs and summary measures. Sometimes we refer to this type of analysis as “exploratory analysis”. When exploring data, we often produce more results than we eventually use in the report, but by investigating the data from a number of angles, we can develop a much better ‘feel' for the data: a deeper understanding of the data.

In order to prepare a reply to the General Manager, you will need to examine and analyse the dataset thoroughly. The General Manager has asked a number of questions. The following guidelines for each question should be considered carefully:

1. A summary of Gross Profit
Here you should only analyse “Gross Profit” by itself. The importance of other variables is considered in other questions. You should derive summary measures and check for unusual data. In addition, there may be well suitable tables and graphs that will illustrate, further and more clearly, other important features of the variable.

2. Gross Profit by Sundays
Investigate any particular relationship between the variables “Gross Profit” and “Sundays”.

3. Online Channel by Location
Investigate any particular relationship between the variables “Online channel” and “Location”.

4. Wastage; Wastage by State
Summarise the variable “Wastage”. Also, investigate any particular relationship between the variables “Wastage' and “State”

5. Variation in Sales with other variables
Investigate any particular relationship between the variables “Sales” and other variables (a pair at a time).

The analysis section you submit should be no more than 8 pages of computer output (ie. output that you have copied into a Word document from Microsoft Excel). When you conduct your analysis, you will produce much more than this initially, but you should trim it down to only show the most relevant results in your maximum of 8 pages. Where possible, it is always useful to produce both numerical and graphical statistical summaries as sometimes, something is revealed in one that is not obvious in the other. Within the Word document, your analysis should be presented in the same sequence (and contain that same numbered sequence) that the questions have been asked by Grace Wong (the General Manager), and be clearly labelled and grouped around each question. Poorly presented, unorganised analysis or excessive output (more than 8 pages) will be penalised.

It is usual, when writing any professional document, to annotate any tabular or graphical output professionally. For example, TABLE 1: Summary statistics for the measure Gross Profit.

Save your computer analysis frequently.

Part 2: Email

You are required to reply by email, detailing essential information and conclusions from your data analysis. You are allowed no more than 2 pages to convey your written conclusions.
Keep the English simple and the explanations succinct. Avoid the use of technical statistical jargon. Your reader will not necessarily understand even simple statistical terms, thus your task is to convert your analysis into plain, simple, easy to understand language.
Further IMPORTANT instructions:
– The email is to be written as a stand-alone document (assume that the Grace Wong will only read your email). Thus, you should not have any references in the email to your analysis, nor should you include any charts and tables within it.
– Use an email format for your reply. That means the email heading (eg. To:, From:, Subject:) should be included, the recipient should be addressed at the beginning and the signature or name of the sender should be included at the end.
– When composing your reply, make sure that you actually answer the questions asked. Cite (state) the summary statistics of importance without referring to your analysis section.
– Sequentially number your answers in both your email and your analysis (1, 2 … 5) to match the General Manager's email.
– Include a simple introduction at the start of the email and a summary/conclusion at the end.
– Marks will be deducted for the use of technical terms, irrelevant material, poor presentation / organisation / formatting and emails that are over two pages long.

When you have completed the email, it is a useful exercise to leave it for a day, return to it and re-read as if you knew nothing about the analysis. Does it flow easily? Does it make sense? Can someone without prior knowledge follow your written conclusions? Often on re-reading, you become aware that you have made some points in a clumsy manner, and you find that you can re-phrase them much more clearly.

Part 3: Interactive Dashboard

The minimum requirement is a neat, functional, interactive dashboard. Following questions will guide you designing an interactive dashboard.
1. What are the most appropriate visualisations for the dashboard?

2. What about choices of colour?

3. How can I use hierarchy and dimension?

4. How can I make the dashboard interactive?

Only Part 3 needed

Case Study- Hunter Safety Lab

Questions-

1. Outline Hunter Safety Lab's business model.

2. Discuss the strengths and weaknesses of the market research.

3. Imagine yourself as an investor. Would you invest in Hunter Safety Lab based on the result of their market validation? Provide reasons for your decision.

Quantitative methods for business

The future of streaming TV is here! Gone are the days when viewers had to watch scheduled television and record clashing programs to watch later, now the viewer decides what they want to watch and when. Paid TV now offers ‘libraries' of programs which a viewer can access and binge watch to their heart's content, according to their own schedule.

AUSflix! is one such paid TV service and has been running successfully for over two years. Each time a viewer accesses the AUSflix! television guide, they are asked the AUSflix!-questionTM:

What do you want to watch now?
If the viewer knows what they want to watch, they can choose their preferred program. AUSflix! will also recommend programs the viewer might like based on past watching patterns, current popular titles and other marketing factors.

You have been working at AUSflix! for the past 9 months (and as a AUSflix!-perkTM you have free access to AUSflix!!). The company is supportive of their employees and promotes a casual working environment. You're there because you are not scared of numbers, you know how to analyse the finance and statistics behind AUSflix! to inform important decisions including Human Resources recruitment, marketing, new financial ventures and anything else that's needed. You also know how to explain financial analyses in everyday language so even the Chief Executive Officer (CEO) can understand it (how amazing!).

AUSflix! is in a growth period and have asked you to investigate. They want to expand their audience base and hire more marketers. AUSflix! also needs to understand the profitability of building its own TV production studio. A further need AUSflix! has from you at the moment is to analyse the competitiveness of AUSflix!'s pricing scheme against increasing costs of sourced TV content.

Question 1

To grow the audience base for AUSflix!, a recent marketing strategy was to introduce an AUSflix! kiosk at a local major shopping centre, branded The AUSke, to promote AUSflix! and encourage more people to sign up. The CEO needs you to review the success (or otherwise) of The AUSke before these kiosks are rolled out across major shopping centres. The most recent financial data (summarised below) will help you determine whether The AUSke was a marketing and financial success.

  The AUSke Income Statement (financial year ending June 30 2016)

2016                          2015                          2014  

2013  

Sales

(less)   Expenses $'000s 948

(637) $'000s 746

(498) $'000s 692

(440) $'000s 642

(395)

Net Profit

311

248

252

247

Table 1: Excerpts of financial statements from The AUSke in $'000s AUD The AUSke Statement of Assets and   Liabilities (as at June 2016)

2016                                   2015                                  2014

 

$'000s

$'000s

$'000s

Total Assets

751

656

709

Total Liabilities

432

394

496

Total   Equity

319

262

213

Table 2: Excerpts of assets information from The AUSke in

(a) An important indicator capturing whether The AUSke has been a successful marketing promotion is the relative percentage change of the Net Profit of The AUSke. Using the relevant information from Table 1, calculate the relative percentage change of net profits for The AUSke between each financial year from 2013 to 2016, i.e. from 2013 to 2014, etc.

(b) Repeat the calculations in (a) for Sales by The AUSke.

(c) Provide a brief statement to explain what the numbers mean for parts a) and b)

(d) In addition to the relative profitability and sales through The AUSke, a second important indicator is the Debt/Equity ratio (also called the D/E ratio, that is the Total Liability to Total Equity ratio) which tells you whether AUSflix! is becoming more (more debt) or less (less debt) risky in the eyes of investors. Using the relevant information from Tables 1 and 2 above, calculate the D/E ratio for each year from 2014 to 2016.

(e) Provide a brief statement to explain what the numbers mean for part d)

(f) If the roll out of The AUSke goes ahead, sales people will need to be hired to work at these kiosks. The Human Resources manager would like your advice before the job ads are posted online to recruit sales people for The AUSke. To encourage applicants, AUSflix! will pay a base salary plus sales commission. The Australian Award Rate for 2017 is $38,000 per annum which would form the base salary. However, the Human Resources manager wants to publish a salary of $56,000 per annum which is the base salary plus commission. She wants to double-check sales targets with you to make sure this salary is financially achievable. The AUSke will implement a sales commission model in which a salesperson would receive 70% of the 10% commissions on sales. In order to advertise an annual salary of $56,000, calculate the target sales for the year, rounded to the nearest dollar, required to achieve this desired salary.

Additional information: if the sales targets are above $200,000 then AUSflix! will consider The AUSke viable to produce the published salary. Further information you may find useful in terms of the competitiveness of the job advertisement is that other jobs of a similar nature typically publish a salary between $45,000-50,000 per annum and do not offer commission.

Question 2

Provide, in your own words, written responses to the following:

(a) Explain the difference between ‘Simple Interest' and ‘Compound Interest'. As part of your explanation supply examples in support of your comments.

(b) Explain what an ‘Annuity' is and how it relates to ‘Compound Interest'. As part of your explanation supply examples in support of your comments.

(c) Explain the difference between “Semi-fixed” and “Semi-variable” costs. As part of your explanation supply examples in support of your comments.

(d) The structure of Linear Programming comprises four main components. Explain what each of these components are.

Note: Though you are asked to answer the above questions in “your own words”, the ideas for your responses are likely to have come from somewhere, therefore, you will need to provide references for each of the above questions. Please revisit your learning on how to provide Harvard References

Question 3

AUSflix! has had a few business successes recently and find they have $350,000 funds available for further development. One option is to invest the money in AUSflix!'s business account which is a common practice in companies. This is a low-risk option that also attracts a low interest rate.

However the CEO wants AUSflix! to do something more amazing than just investing the money in the bank (which does sound kind of boring to be honest). He's a huge fan of Game of Thrones and is jealous that HBO produces its own made-for-TV productions. For this reason, he would like to see AUSflix! build its own studio for in-house production of TV shows to build AUSflix!'s presence in the market within the next couple of years. This is a higher-risk option that will mean a higher rate that recognises this risk will be applied.

There's just two problems. The first is he isn't sure whether this is a financially viable project and if investing in a boring bank account really is better. The second is that he wants the first AUSflix! TV show to have the non-catchy title Game of Q. You're not sure if he'll let you pick a better name for the TV show, but you do know he will pay attention to your comparison of the two financial options by performing the analyses below over a short-term planning horizon of two years.

Include your spreadsheet results here in this appendix for parts (a) and (b). Cropped screenshots work well – just make sure we can see the row numbers and column labels (A,B,C, etc)

(a) Investigate the first option of investing the money in a bank account. AUSflix! has a bank account that pays 4.5% interest per annum compounded monthly. Calculate the accumulated balance after 2 years.

(b) The second option is to build an in-house production studio. All of the $350,000 would be spent immediately to construct the studio. There will be ongoing monthly costs of $3,900 for studio security and $34,000 for salaries. The TV studio is expected to increase profits by $36,900 per month as a result of market confidence and $44,000 per month from paid sponsorship. In addition, it is estimated AUSflix! will save $120,000 per quarter as they will source less content from other studios, although quarterly payments towards consumables such as electricity and water are expected to total $58,000. At the end of 2 years, a payment of $20,000 will be made to keep the producer on retainer, for the possibility of future joint projects. As it is unclear whether the studio will be operational after the 2-year planning horizon, it will be assumed there are no recoverable amounts at the end of the 2 years.

AUSflix! will have to work with a cost of capital of 10% per year compounded monthly as this is a riskier venture. Use EXCEL to calculate the net present value of the proposed TV studio. Assume that increases in profits are realised at the end of each month.

For full marks show:

1. The full spreadsheet with all completed entries. Also show how you entered cash inflow and cash outflow for Month 1 and the first quarterly payment – if you wish, you can type these two calculations in Word or submit your Excel file.

2. The NPV calculation (you may use either the (1) Excel function ‘NPV', (2) prepare a discount table yourself in Excel or (3) perform the calculations manually. If you use Excel you must detail all cell references).

3. All explainations what you are doing, why you are doing it and the steps involved in providing a final solution. Ensure your answer is not just a set of calculations as 25% of the marks for this question are set aside for your explanation.

Question 4

Until now AUSflix! has depended on content sourced by other studios and is now facing the consequences of its own success. Movie and TV studios have raised the price AUSflix! has to pay to acquire the rights to their content. Even with their own TV studio to produce in-house content, AUSflix! still needs to review its pricing scheme for subscribers to absorb these increased costs and secure their financial viability.

AUSflix! charges subscribers $12.49 per month. To ensure the best subscriber experience, a team at AUSflix! needs to edit and reformat content for compatibility at an estimated cost at $45,000 per month. AUSflix! uses a server in London to deliver streaming videos to its subscribers and the facility runs at a cost of £116 per month. There is also a team of software engineers testing and maintaining all systems and infrastructures costing AUSflix! $56,000 per month. Other fixed costs are budgeted at $20,000 per month.

Other costs incurred by AUSflix! includes $0.02 per GB (GigaByte) of bandwidth to deliver content. In addition, the external studios making the content are paid $0.15 every time a subscriber watches its production. A typical AUSflix! subscriber will watch 30 programs, streaming 65GB of film and TV shows per month. Each subscriber will also cost AUSflix! $2.14 per month to maintain their membership.

Note: to convert from GBP (£) to AUD, use a conversion rate of 1.62.

(a) Using the above information, calculate:
(i) The break-even number of subscribers per month for AUSflix!.
(ii) The monthly revenue at break-even.
Show all working out including the modelling and solution steps.

(b) How many subscribers are required to obtain a profit of $100,000 per month? What is the monthly revenue for this number of subscribers? Show all working out including the modelling and solution steps.

(c) The marketing department is forecasting that if the subscription price is reduced by 10% AUSflix! will attract 35% more subscribers than the current number when monthly profit is $100,000. However, as they don't have a degree in analysing data they need you to make sure they aren't making bad recommendations. Calculate the new break-even number of subscribers, the new revenue and thus the new profit for these changes. Show all working out including the modelling and solution steps.

(d) What effect does the change in part (c) have on the contribution margin? Explain the effect of this change on the expected break-even number in part (a). Support your justification with calculation of both contribution margins.

(e) Provide a (one) chart detailing the necessary information to show the break-even points contained in your responses to parts (a) and (c) of this question