Assessment exercise for Assignment 1
Assessment exercise 1
1100 words equivalent (EXCLUDING TABLES AND CHARTS)
Due date: 20th, March, 2017.
*(Grade 100%)*
Question 1 (Emily & Emma)
• Compound and simple interest
• Show information given and relevant calculations
Question 2 (Funky Town Furniture)
• Economic value of a no-interest sale
• Show information given and relevant calculations for Funky Town Furniture
• Show information given and relevant calculations for Far Out Furniture
• Interpret results
Question 3 (Jim & Jane)
• Funding an annuity
• Obtain appropriate Excel output
• Write a summary of findings
The textbook:
• University of South Australia, 2013, Quantitative Methods for Business, Pearson. ISBN: 978-1-4860-1166-7.
• The textbook is the primary reference however some review exercises are taken from Croucher, John S. 2010, Introductory Mathematics and Statistics for Business, 5th Edition, and McGraw Hill.
IMPORTANT NOTE: Make sure you show calculations step by step with formulas: NO MARKS given if there is no showing of calculations and formulas.

QUESTION 1
1) Emily and Emma were twin sisters who were each left a will inheritance of $2,000. Emily invested her money for 3 years, at a simple interest rate of 7.8%. However, Emma invested her money in an account for 3 years, at an annual interest rate of 8% compounded quarterly. At the end of 3 years, they both agreed to pool their respective accumulated amounts into one account for another 2 years. The annual interest rate of this account is 5% compounded semi-annually.
a) How much accumulated principal did each twin have after 3 years? (Hint: Round final answer to nearest cents)
b) What was the accumulated principal of their pooled money at the end of the 5-year period? (Hint: Round final answer to nearest cents)
c) The twins’ older brother Brian suggested an alternative investment strategy. Brian thinks they should immediately pool their money and invest the pooled amount in an account for 5 years, which pays interest at a rate of 6.6% per annum, compounded monthly. Emily is arguing that 6.6% p.a. is a lower interest rate compared to what they are currently offered. However, Emma recalled their previous studies advocating on compound interest account. Write a short paragraph (5-6 sentences) advising the twin sisters their investment outcomes comparing both options.
QUESTION 2
2) For its “One-Year No-Interest Sale”, Funky Town Furniture advertises that, on any purchase over $388 the customer’s deposit needs to cover 10% of the list price. The balance of the purchase price is then paid in 12 equal monthly payments with no interest charges. Money is worth 7.8% per year compounded monthly to Funky Town because it can use any extra cash to pay down the balance on its operating loan on which interest is charged at 7.8% per year compounded monthly.
(a) What cash amount should Funky Town be willing to accept (instead of the no-interest plan) on an item ticketed at $8,000?
(b) Far Out Furniture offers the same no-interest plan but requires a 15% deposit and an establishment fee of $50 payable immediately. Far Out also charges an account keeping fee of $3.33 per month due with each payment. What cash amount should Far Out be willing to accept with its no-interest plan on an item ticketed at $8,000? Assume that for Far Out Furniture, money is worth 8.4% per year compounded monthly.
(c) Which shop offers a better deal for the customer? Explain briefly (2-3 sentences).
QUESTION 3
3) Jim and Jane would like to add an outdoor entertainment area to their property. It has been determined that they will need $30,000 for this purpose. The couple is considering taking a 3-year personal loan for that amount. Their local bank charges 6% per year compounding quarterly and requires quarterly repayments
a) Prepare an EXCEL spreadsheet that shows the end-of-the quarter balance in Jim and Jane’s loan account over the next 3 years. In addition, use your amortisation schedule to calculate the total interest and the total amount paid over the life of the loan. Attach a copy your spreadsheet into your assignment submission.
EXCEL Instructions:
Your spreadsheet set-up could look something like this (you will need to use Excel fill in the missing):

b) A wealthy family friend nicknamed Pacino had offered to help Jim and Jane and has made an alternative offer for the couple to obtain the $30,000 loan. Pacino has proposed the following loan terms over a 12 month period. He claims this is a ‘cheaper and simple’ with everything paid off at the end of the renovation.
Pacino will make two equal deposits of $15,000 in their bank account in the first two months of the loan period. However, from months 3-12, he will withdraw $3,200 from Jim and Jane’s bank account each month, until the end of the loan period. Pacino claims he is entitled to $2,000 in interest, which will be paid to him under his proposed conditions.
Jim and Jane would like to know the interest rate Pacino is charging them and they would also like to verify the total amount of interest ($2,000) he claims he is entitled to.
To answer this question, follow all EXCEL instructions below – including the instructions given in the two diagrams. Also show your calculation for the total interest Jim and Jane paid under this arrangement.
EXCEL Instructions: you will need to set up a new Amortisation schedule, like the one shown below, for the 12-month loan. For full marks provide: (i) a copy of your Excel spreadsheet that looks similar to the one below and (ii) your calculation for the total interest to verify Pacino’s $2,000 claim.
1. Set up the amortisation schedule: Set up your amortisation schedule as shown below. Ensure you extend it to show all 12 months. Read the instructions in the boxes for more information.
2. Use Goal Seek to calculate the correct interest rate: Now use GoalSeek (Data ? Data Tools ? What-If Analysis in EXCEL 2007, Excel 2010 and EXCEL 2013 or Tools ? GoalSeek in EXCEL 2003) to find the Interest Rate (APR) that will balance the final entry in the spreadsheet to 0.
To run Goal Seek, place any value in the Interest Rate cell (B1). When run Goal Seek, Excel will try different interest rates until the value in E18 equals 0. It doesn’t matter what interest rate you start with, as Excel will tell you the correct answer ?
Provide a screenshot when using Goal Seek, similar to the one provided below. Also provide an ‘after’ shot of the full spreadsheet, showing the correct answers. Remember to show the total interest calculation as well!

c) Jim and Jane is seeking your advice in making their financial decision. Write a short summary (8-10 sentences, double spaced, at least 2cm margins, 12pt Times New Roman font or equivalent) for the couple explaining your findings.
Assessment exercise 2
• 1100 words equivalent (EXCLUDING TABLES AND CHARTS)
INSTRUCTIONS AND WHAT TO COVER
Question 1 (Travis Removalist)
• Break-even analysis
• Find break-even point algebraically
• Illustrate with an appropriate Excel graph
• Perform sensitivity analysis
Question 2 (The French Club)
• Linear programming using Excel Solver
• Show mathematical set up for the problem
• Obtain appropriate Excel output/
• Interpret Solver output
• Perform Sensitivity Analysis
• Prepare a report on findings
Question 3 (Flavour & Texture of chips)
• Probability and independence
• Obtain appropriate diagram
• Show appropriate probability calculations
• Check for statistical independence
IMPORTANT NOTE: Make sure you show calculations step by step with formulas: NO MARKS given if there is no showing of calculations and formulas.

QUESTION 1
1) Travis is considering the start-up of a removals service in his hometown. He purchased a used second-hand truck for $24,000. He will need to hire a helper to assist with loading and unloading. After conferring with another operator of a similar service in another town of a similar size, Travis feels he can charge $80 per delivery and that each delivery will require an average round-trip driving distance of 30km. He has estimated the following expenses:
Truck insurance and registration $2,760 per year
Diesel $60 every 300km
General truck cleaning $20 every 1000 km
Helper’s wages $2000 per month
Travis’s wages $2500 per month
Tyres $750 every 75,000 km
Mobile Cost (on a 24-month contract) $70 per month
Other truck repairs and maintenance $500 per 10,000 km
Ignore all taxes and depreciation for the following questions.
(a) If Travis charges $80 per delivery, what is the break-even in deliveries per month?
Find the break-even point algebraically and by using an EXCEL graph. Attach the printout or copy your EXCEL graph into your assignment submission.
EXCEL Instructions: Create a column called Number of Deliveries and in that column enter values from 0 to 100 in increments of 10. Then create two more columns, one for Total Cost and another for Total Revenue. Enter appropriate formulae in EXCEL to obtain the total cost and total revenue corresponding to each value in the Number of Deliveries column. Highlight the resulting three sets of numbers and go to the Insert tab (or Chart menu) to obtain an appropriate diagram.
Make sure that your graph has been labelled appropriately (i.e. title, axis labels, legend).
(b) Travis is forecasting that he will make 85 deliveries per month. Based on your graph from part (a), will there be a net profit or net loss from the start-up at this volume of deliveries? How do you know? Calculate this net profit or loss amount.
(c) The industry standard recommends driver to make 75 deliveries per month as a start-up for a sustainable business in long-run. At what unit price would Travis be just breaking even with the recommended number of deliveries per month?
(d) If the average distance per delivery turns out to be 25 km, how does this impact Travis?
Assuming the fixed costs and the amount charged per delivery remains as in (a), explain in a short paragraph (4-5 sentences) whether the number of deliveries required to break-even will increase or decrease. Do not re-calculate the break-even quantity, x, for this question however you may quote the break-even formula to aid your explanation.
QUESTION 2
2) The French Club is a restaurant specialising in ratatouille and beef bourguignon. Prevailing prices in the local market are $18 for a ratatouille and $24 for a beef bourguignon.
The executive chef is reviewing the operation of the kitchen. The restaurant has 2 kitchen assistants that spend 12 hours per service preparing all ingredients for these dishes. The kitchen staffs will need 3 minutes to prepare each ratatouille and 6 minutes for each beef bourguignon. These ingredients will then go to any of the chefs hired by the restaurant. There are a total of 15 chef hours available per service. From experience, each ratatouille will take 5 minutes to cook and 4 minutes for each beef bourguignon. Before the plates are served to diners, it will also need be plated and served. The executive chef has allocated 10 hours for this purpose. Each ratatouille will take 4 minutes to plate and 2 minutes for each beef bourguignon.
Use linear programming to find the maximum possible revenue per service.
Task 1
Construct a mathematical model for this problem. In doing so, consider the following:
a) What are the decision variables for this problem?
b) Using decision variables identified in part (a), formulate the objective function for this problem. Is the quantity of interest to be maximised or minimised?
c) What constraints are relevant to this problem? Using the decision variables from part (a), formulate those constraints.
Task 2
Use Excel Solver to obtain a solution to the mathematical problem from Task 1. Your submission should include:
• your Excel spreadsheet
• the Sensitivity Report
• the Answer Report
Task 3
Use your Excel output to answer the following questions:
a) Describe the linear programming solution to the executive chef of The French Club in terms of:
• The optimum numbers of ratatouille and beef bourguignon to prepare each service.
• The maximum revenue per service.
• Whether all the hours allocated for kitchen assistants will be fully utilised.
• Whether all the time allowed for chefs will be fully utilised.
• Whether all the hours budgeted for plating staff will be fully used.
Which of the Solver reports helps you answer these questions?
b) What is the maximum profit per service if each chef draws a salary of $30 per hour, kitchen assistant earns $25 per hour, the plating chefs are paid a fixed combined salary of $250 per service and fixed overhead (including salary of executive chef and other expenses) averages $1,000 per service. Which Solver reports allows you to answer this question?
c) The executive chef is hoping to improve the popularity of the beef bourguignon among diners by reducing the price to $22 per serve. Would the solution obtained in Task 2 still be optimal? Which of the EXCEL reports helps you answer this question? Justify your answer carefully. How would the solution and The French Clubs’ profit change, if at all?
d) In preparation of the upcoming festival, it is decided that one group of employees, either kitchen assistants, chefs, or plating staffs is going to have an additional 3 hours of workload. The group that will do so will be the one that would increase the total revenue per service the most. Use the appropriate Solver report to make that choice. Assume no change to in the original estimates of prices per unit. Make the proposed change to the work hours available and use Solver to generate a new Answer Report. How did the solution change?

Task 4
Write a report outlining the solution and discussing your findings from Task 3 (at most two pages, double-spaced, at least 2cm margins, 12pt Times New Roman font or equivalent).
Here are a few points to consider while working through this assignment question:
1) The first step is always to work out the mathematical set up for the problem. This means identifying decision variables, formulating the objective function and then formulating constraints. At this stage, we are not trying to solve the problem or work out interactions among constraints. We simply list all conditions that must be satisfied.
When you complete Task 1, you should have two decision variables, the objective function written in terms of those decision variables, and five constraints, also written in terms of decision variables (some using both decision variables, others just one of them).
2) The second step is to find a solution. Task 2 tells you specifically to use Excel Solver to find this solution. The key here is to translate all mathematical expressions from Task 1 into Excel format. The final step is interpreting the solution that has been found, which is Task 3.
3) The report in Task 4 is a summary of the results from linear programming and sensitivity analysis in Tasks 2 and 3.

QUESTION 3
3) A simple random sample of 180 university students was interviewed and asked about their favourite potato chips. The preferences of these respondents are summarised below.
Flavour Texture
Thinly Cut Crinkle Cut Maxi Cut Total
Original 15 12 11 38
BBQ 13 13 17 43
Sour Cream & Onion 14 16 9 39
Salt and Vinegar 18 23 19 60
Total 60 64 56 180
a) Use Excel to obtain a 100% stacked column chart for the data from the table. What does your chart suggest between the relationship between flavour and texture?
b) Use the contingency table to calculate the following probabilities; include an appropriate probability statement for each case:
i. What is the probability that a respondent prefers Salt & Vinegar flavoured chips?
ii. What is the probability that a respondent prefers Original flavoured chips and Thinly Cut?
iii. What is the probability that a respondent prefers BBQ flavoured chips or Crinkle Cut?
iv. Suppose that the texture is Thinly Cut. What then is the probability that the flavour is BBQ?
v. Suppose that the flavour is Sour Cream & Onion. What then is the probability that the texture is Maxi Cut?
c) Are the events BBQ flavour and Texture independent or dependent? How do you know? Show all calculations that support your answer.

Assessment exercise 3
• 1100 words equivalent (EXCLUDING TABLES AND CHARTS)
INSTRUCTIONS AND WHAT TO COVER
Question 1 (Swimsuits)
• Describing distributions
• Obtain appropriate displays using Excel
• Comment on distribution shape
• Select appropriate summary measures
• Prepare a report on findings
Question 2 (Distance and Cost)
• Simple linear regression
• Obtain appropriate Excel output
• Interpret results
• Obtain and assess prediction
IMPORTANT NOTE: Make sure you show calculations step by step with formulas: NO MARKS given if there is no showing of calculations and formulas.

QUESTION 1
1) In preparation of an international swimming event in 100m backstroke, Michael Phelps tested two different types of swimsuits from his major sponsor.
Samples of 20 swimming times were recorded below using the X-Glide and the X-Fly: X-Glide 100m backstroke (seconds)
52.52 50.78 50.93 51.02 51.48 51.60 51.75 52.97 53.93 55.45
50.53 50.80 50.97 51.05 51.48 51.60 52.85 53.10 54.15 56.32
X-Fly 100m backstroke (seconds)
50.08 50.52 50.60 50.65 51.10 51.53 51.92 53.30 53.75 52.23
50.10 50.58 50.60 50.72 51.48 51.65 52.10 53.75 54.02 57.55
a) Use EXCEL to obtain a histogram for each swimsuit (X-Glide and X-Fly).
EXCEL Instructions: Go to the Assignments page on the course web site and download the data file for this question, called Time.xls.
b) Use EXCEL to obtain Descriptive Statistics, Quartile 1 (Q1) and Quartile 3 (Q3) for each swimsuit.
c) Based on the histograms in (a), briefly describe the shape (symmetry, modality and outliers) of the data for each swimsuit.
Instructions for identifying outliers:
Whether an observation is an outlier is a matter of judgement. One rule commonly used for identifying outliers is the so-called 1.5 × IQR rule. An observation is suspected to be an outlier if it lies more than 1.5 × IQR below the first quartile Q1 or above the third quartile Q3.
Apply this rule to the data from each swimsuit. Identify suspected outliers (if any) by their exact value(s).
d) Nominate appropriate measures of centrality and dispersion for the distribution of swimming times for each swimsuit. Give reason(s) for your choice. For each distribution, give and interpret the values of the summary measures you have chosen.
e) On the basis of your results of (a)-(d), are there any differences between the two swimsuits? Write a report based on your findings to Michael in preparation for his upcoming event. (At most one page, double-spaced, at least 2cm margins, 12pt Times New Roman or equivalent).

QUESTION 2
2) Does the cost of a flight depend on the distance? A local airline is investigating the relationship between the distances (in km) from Adelaide to major cities in Australia versus the ticket price. A sample of the 20 most popular destinations was examined.
Distance 332 1550 4981 3122 4694 690 1669 4789 2813 1836
Cost 179 179 397 274 359 99 317 443 404 334
Distance 653 1162 2134 232 251 2099 1453 1560 3894 3456
Cost 135 171 267 129 128 219 169 286 329 299
a) Identify what should be the dependent and the independent variable for this scenario. Then use Excel to produce an appropriate scatter diagram that contains the trendline, least squares model and the coefficient of determination R2. In addition:
• Comment on the relationship between Distance (km) and Cost ($), based on the scatter diagram.
• Comment on the value for R2. Include an interpretation of the meaning of R2 in the context of the data.

b) Interpret the meaning of the slope in this problem. In addition, interpret the intercept and explain if it is meaningful in the context of this problem?
c) Use the model described in the scatterplot to predict ticket price if the distance from Adelaide to Sydney is 1162km. Explain whether this prediction likely to be accurate?
d) Based on the regression line in the scatterplot, would you expect the model to over- or under-predict the ticket price in part (c)?
Note! Take particular care in interpreting the value of R-squared and in assessing accuracy of prediction.
Assessment exercise 4
• 1100 word equivalent, excluding tables and charts
INSTRUCTIONS AND WHAT TO COVER
Question 1 (Orange Juice)
• Normal probability calculations
• Show appropriate calculations
• Illustrate with diagrams
Question 2 (Jason's Swimming Event)
• Normal probability calculations
• Show appropriate calculations
• Illustrate with diagrams
Question 2 (Smith’s Original chips)
• Confidence intervals
• Construct and interpret confidence interval
• Discuss assumptions and quality of data
• Apply Central Limit Theorem
IMPORTANT NOTE: Make sure you show calculations step by step with formulas: NO MARKS given if there is no showing of calculations and formulas.

QUESTION 1
1) Working with the normal distribution
An orange juice producer buys all his oranges from a large orange grove. The amount of juice squeezed from each orange is approximately Normally distributed with a mean of 145mL and a standard deviation of 15mL.
a) Find the probability that a randomly selected orange will contain less than 135mL?
b) Find the probability that a randomly selected orange will contain more than 153mL?
c) Find the probability that a randomly selected orange will contain between 140mL and 160mL?

d) 80% of oranges will contain less than how many millilitres of juice?
e) 95% of oranges will contain at least how many millilitres of juice?
QUESTION 2
2) In international swimming the mean time for the men’s 100m freestyle is 50.46 sec with a standard deviation of 0.6 sec. For the 200m freestyle, the mean time is 110.4 sec with a standard deviation of 1.4 sec. Jason’s best time for the 100m is 48.76 sec and for the 200m is 108.43 sec. If he can only enter one of these events in the competition, which one should he enter?
Hint! Perform relevant probabilities calculations to answer this question.
Note! For full marks, include an appropriate probability statement for each question and illustrate each calculation with an appropriate diagram.
Attached file are the diagram templates that you can copy and paste into your assignment.

QUESTION 3
3) Students investigating the packaging of potato chips purchased bags of Smith’s Original flavoured potato chips from their local convenience store. These bags are marked with a net weight of 28.6 grams. They carefully weighted the content of each bag, recording the following weights (in grams):
28.88 29.07 28.18 28.29 29.17 28.36 28.71 28.93 29.27 28.48
28.80 29.05 28.54 27.96 28.74 28.65 29.48 28.74 28.98 29.31
28.79 29.03 28.91 28.10 28.26 29.26 28.57 28.40 28.63 28.68
a) Use EXCEL to find the sample mean. Suppose that the population standard deviation for the content of potato chips is known to be 0.40 grams. Construct a 95% confidence interval for the population mean weight in such bags of chips. Does the population weight of potato chips have to be Normally distributed here? Explain.
b) Provide a brief comment on the company’s stated net weight of 28.6 grams.
c) What sampling method was used? Was it a good choice? Explain.
d) If the true average weight of potato chips is 28.6 grams as stated by the manufacturer, what is the probability of a mean weight exceeding 28.8 grams for a sample of 30 bags? Assume that the population standard deviation is 0.50 grams. Use a diagram!