RECENT ASSIGNMENT

Assessment exercise 1
• 1100 words equivalent (EXCLUDING TABLES AND CHARTS)
• Due date: 20/03/2017
INSTRUCTIONS AND WHAT TO COVER
Question 1 (Antiques Dealer)
• Proportion and percentages
• Show information given and relevant calculations
Question 2 (Partnership of Doug, Sam, & Bob)
• Show relevant calculations
Question 3 (Daniel & Samuel)
• Compound interest and simple interest
• Show information given and relevant calculations
Question 4 (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 5 (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) An antique dealer charges the following commission (based on the selling price on any items sold on behalf of the client:
• 5.0% on the first $50,000
• 3.0% on the next $50,000
• 2.0% on the amount exceeding $100,000
a) Calculate the commission that will be charged on an item that sell for $80,000.
b) If the dealer wants to receive a commission of $5,200, for how much should the item sell?
QUESTION 2
2) A partnership agreement states that half of the annual profit be distributed in proportion to each partner’s investment in the partnership, and that the other half be distributed in proportion to the total number of hours that each partner worked in the business during the year.
How should the most recent profit of $88,740 be allocated if the amounts invested by Doug, Bob and Sam are $82,500, $45,000, and $22,500, and their hours of work for the year were 427, 1709, and 1424, respectively? (Round final answer to nearest cents)
QUESTION 3
3) Daniel and Samuel were twin brothers who were each left a will inheritance of $2,000. Daniel invested his money for 3 years, at a simple interest rate of 7.8%. However, Samuel invested his money in an account for 3 years, at an annual interest rate of 8% compounded semi-annually. 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 quarterly.
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 4.8% per annum, compounded monthly. Daniel is arguing that 4.8% p.a. is a much lower interest rate compared to what they are currently offered. However, Samuel recalled their previous studies advocating on compound interest account. Write a short paragraph (5-6 sentences) advising the twin brothers their investment outcomes comparing both options.
QUESTION 4
4) 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 9% 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 9% 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 $5,000?
b. Far Out Furniture offers the same no-interest plan but requires a 15% deposit and an establishment fee of $18 payable immediately. Far Out also charges an account keeping fee of $2.83 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 $5,000? Assume that for Far Out Furniture, money is worth 9.6% per year compounded monthly.
c. Which shop offers a better deal for the customer? Explain briefly (2-3 sentences).
QUESTION 5
5) 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
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). Refer to Topic 3 in the EXCEL Supplement for further instructions on entering formulae and graphing in EXCEL.
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
• Profit line (Profit = 40)
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. Instructions for doing so can be found under Topic 5 in the Excel booklet, as well as in the Linear Programming supplement. In addition, the Lecture notes page in this website gives you access to Excel spreadsheets used to generate Excel output shown in lecture slides for Week 5. It may be worthwhile examining them before attempting Task 2.
3. The final step is interpreting the solution that has been found, which is Task 3.
4. The report in Task 4 is a summary of the results from linear programming and sensitivity analysis in Tasks 2 and 3.

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 (Temperature vs Beer Sales)
• Simple linear regression
• Obtain appropriate Excel output
• Interpret results
• Obtain and assess prediction
Question 3 (Meal & Preferred Beverage)
• 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. 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. Use the bins given in Time.xls.
b) Use EXCEL to obtain Descriptive Statistics, Quartile 1 (Q1) and Quartile 3 (Q3) for each swimsuit.
EXCEL Instructions: Refer to Topic 7 in the Excel Booklet for instructions on how to obtain Descriptive Statistics and the two quartiles.
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. To help determine how many beers to stock for the Ashes Cricket Test against England, the bar manager at the MCG wanted to know how the weather affects beer sales. Accordingly, he took a sample of 10 cricket matches and recorded the number of beer sold and the temperature in the middle of the day.
Temperature °C Beer Sales
25.0 6789
23.2 6210
28.5 8455
33.4 13432
26.2 8432
37.5 16453
28.0 9234
31.3 11237
27.8 9154
29.1 9265
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 Temperature (0C) and Beer Sales, 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 regression line equation obtained in part (a) to predict the number of beer sales if the temperature is forecasted to be 350C. In addition, explain briefly whether this prediction is likely to be accurate.
Note! Take particular care in interpreting the value of R-squared and in assessing accuracy of prediction. For full marks, follow the process outlined in Week 8 lecture notes.

Question 3
3. Does the type of meal influence the type of beverage ordered? At a restaurant, the orders of 340 dining customers were analysed. The resulting data are summarised in the contingency table below:
Meal Preferred Beverage
Coffee/Tea Soft Drink Alcohol Total
Breakfast 95 25 18 138
Lunch 19 41 20 80
Dinner 40 27 55 122
Total 154 93 93 340
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 type of meal and preferred beverage?
EXCEL Instructions: Refer to Topic 6 in the Excel Booklet for instructions on how to obtain 100% stacked column chart.
b) Use the contingency table to calculate the following probabilities; include an appropriate probability statement for each case:
i. The probability that the preferred beverage is Coffee/Tea.
ii. The probability that the meal type is Dinner and preferred beverage is Alcohol.
iii. The probability that the meal type is Breakfast or preferred beverage is Coffee/Tea.
iv. Given the meal type is Lunch, what is the probability that the preferred beverage is Soft Drink?
v. Given that the preferred beverage is Coffee/Tea, what is the probability that the chosen meal is Dinner?
c) Are ‘Breakfast’ and ‘Preferred Beverage’ independent or dependent? How do you know? Show all calculations that support your answer

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 (Smith’s Original chips)
• Confidence intervals
• Construct and interpret confidence interval
• Discuss assumptions and quality of data
• Apply Central Limit Theorem
Question 3 (Store takings)
• Hypothesis testing
• Show relevant steps and interpret results
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 135mL and a standard deviation of 12mL.
a) Find the probability that a randomly selected orange will contain less than 120mL?
b) Find the probability that a randomly selected orange will contain more than 160mL?
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?
Note! For full marks, include an appropriate probability statement for each question and illustrate each calculation with an appropriate diagram.
*Refer to the Assignments page on the course web site for a file with diagram templates that you can copy and paste into your assignment*
Question 2
2. 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.3 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.79 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.3 grams.
c) What sampling method was used? Was it a good choice? Explain.
d) If the true average weight of potato chips is 28.3 grams as stated by the manufacturer, what is the probability of a mean weight exceeding 28.5 grams for a sample of 30 bags? Assume that the population standard deviation is 0.40 grams. Use a diagram!
Question 3
3. In the week before Christmas it was hoped that the mean takings of a shop’s branches would be $40,000. However, 40 randomly sampled branches has mean takings of only $37,000 with a standard deviation of $6,000. Does this suggest that the mean takings of all branches was significantly different from target figure? Test at a = 0.05.