Delete this page before submission MATH 1053 - Quantitative Methods for Business Assignment 1 (SP5 2016) DUE: Friday 16th September by 12:00 noon (not midnight!) Submission Instructions • Assignment submission is via Gradebook. If you have problems submitting, keep proof of your submission attempt and contact me directly at [email protected] • Once submitted, do not edit any electronic files of your work. Assignment Instructions • This assignment covers Weeks 1-5 and counts for 15% of your overall grade: Assignment component Mark Business report 50 Appendix 70 Presentation 10 TOTAL 130 • Assignments submitted late, without an extension being granted, will attract a penalty of 10 marks per each working day or any part thereof beyond the due date and time. Please refer to the Course Outline for the course policy regarding extensions. • An incorrectly or partially filled cover sheet will result in lost marks and delays in the marking and return of your assignment. • Not deleting the pages/text as requested will result in a loss of presentation marks. • Your assignment submission should be typed and observe the page limits. Hand-written answers will be disregarded. Delete this page before submission Delete this page before submission Delete this page before submission Delete this page before submission QMag a multi-platform magazine with an eye on issue¬¬¬s that matter to you from business, design and tech through to fashion, travel and social media QMag is a popular online magazine that has been running successfully for the past 3 years. The magazine covers a range of articles including celebrity interviews, travel, thought pieces, a spotlight on young entrepreneurs, as well as typical magazine content such as mens' and womens' health and fashion and beauty guides. Given QMag's success as an online magazine, the CEO is interested to produce a print edition of the magazine to widen readership and expand QMag's footprint in the highly competitive arena of magazine publishing. The CEO would like to have a monthly edition of QMag that rivals other publications on newsagency and supermarket shelves. Your official title at QMag is Resident Financial Maths Genius however despite the lightheartedness of your job title, you're there because you are brilliant with numbers. Your CEO has recently asked you to investigate and report on the viability of QMag expanding into print and you'll have to determine whether printing QMag is a financially sound venture as well as the details of the business loan QMag will need to take, in order to customise the printing facilities. To widen the potential readership of QMag and encourage sales, the CEO would like to offer two methods of purchase: subscription-based sales as well as traditional newsagency and supermarket sales. You have been asked to work out the subscription prices based on whether readers sign up for a 12- or 24-month subscription. You have also been asked to evaluate the number of magazines you need to sell in order to pay back production costs of the magazine, plus the rent of supermarket shelves. Finally, running a print magazine requires income! Online advertising is different to print advertising, so an optimal model to maximise advertising revenue for the printed magazine is also required. Just because your CEO is smart, it doesn't mean they know financial maths. Also, the CEO is really scared of the letter x. So while your analysis can show off exactly why you are the Resident Financial Maths Genius, your report will need to be in plain, everyday language with no x in sight! Delete this page before submission UNIVERSITY OF SOUTH AUSTRALIA Assignment Cover Sheet – Internal An Assignment cover sheet needs to be included with each assignment. Please complete all details clearly. When submitting the assignment online, please ensure this cover sheet is included at the start of your document. (Not as a separate attachment.) Please check your Course Information Booklet or contact your School Office for assignment submission locations. Name: Student ID Email: Course code and title: MATH 1053 – Quantitative Methods for Business School: Info. Tech. & Mathematical Sciences Program Code: Course Coordinator: Dr Belinda Chiera Tutor: Day, Time, Location of Tutorial: Assignment number: 1 Due date: by 12 noon (not midnight!) on Friday, Sep. 16, 2016 Assignment topic as stated in Course Outline: Case Study Report Further Information: (e.g. state if extension was granted and attach evidence of approval, Revised Submission Date) I declare that the work contained in this assignment is my own, except where acknowledgement of sources is made. I authorise the University to test any work submitted by me, using text comparison software, for instances of plagiarism. I understand this will involve the University or its contractor copying my work and storing it on a database to be used in future to test work submitted by others. I understand that I can obtain further information on this matter at http://www.unisanet.unisa.edu.au/learningconnection/student/studying/integrity.asp Note: The attachment of this statement on any electronically submitted assignments will be deemed to have the same authority as a signed statement. Signed: Date: Date received from student Assessment/grade Assessed by: Recorded: Dispatched (if applicable): [ENTER REPORT TITLE INCLUDING THE NAME OF THE MAGAZINE] [Enter the date of submission] prepared by [Enter your name]   Introduction (4 marks) Purpose Provide a qualitative description of report contents/problems addressed in the report (covering Appendices 1-4) and what insights the analyses will provide. Write this introduction after you have a clear understanding of the content of your report. Ensure you have explained what the report will contain. Follow the length guideline (given on the previous page as "at most 3 paragraphs"). Highlight and delete this message before submission [Click here and insert your text for the Introduction] Do Not Start The Report Body On This Page (Loss of 4 marks) Report Body Highlight and delete this question text before submission Use the questions below to guide your report-style discussion of the main results of your analyses in Appendices 1-4. You may use subsections, tables, graphs etc. You can also use bullet points to report key findings, providing you discuss any relevant points you wish to make. Clearly presented information will help to earn maximum presentation marks. Do not include calculations or spreadsheets – these should appear in the Appendices. Do quote quantitative results. Do include graphs when requested. You should have no more than 2 pages of writing (figures and tables excluded) (8 Marks) Appendix 1 Net Present Value Discussion Points Describe in 2-3 sentences, the aim of the analysis in this section. 2 marks Populate the table below with the cash inflows and outflows used in the Net Present Value (NPV) analysis (Appendix 1(a)) and include a meaningful table caption as indicated below. Clearly distinguish between cash inflows and outflows. You may customise the table including colour, fonts, font colour etc. if you wish. 4 marks Table 1: replace text with a meaningful caption Year 0 1 2 3 4 5 6 7 8 Cash Flow ($) Cost of capital (%) Net Present Value ($) By referring to Table 1, explain whether acquiring an in-house printing line is a financially viable venture (1-2 sentences) 2 marks (10 Marks) Appendix 2 Bank Loan Option Discussion Points Describe in 2-3 sentences, the aim of the analysis in this section. 2 marks Describe the first loan conditions: interest rate, number of payments and the repayment size (Appendix 2(a)). Include the amount of interest paid and total amount repaid on this loan (Appendix 2(c)) 3 marks Describe the second loan conditions: interest rate, number of payments and the repayment size (Appendix 2(b)). Include the amount of interest paid and total amount repaid on this loan (Appendix 2(c)) 3 marks Based on the results explain which loan option you recommend for QMag and why. 2 marks … continued over the next page … (14 Marks) Appendix 3 Break Even Discussion Points Loss of Marks! will occur if you use 'contribution margin' or any variant thereof, in the discussion below. Use non-specialist terms i.e. everyday language. Describe in 2-3 sentences, the aim of the analysis in this section. 2 marks Populate the table below, including the subscription options – you may customise the table including colour, font, etc.. By referring to this table in your discussion, include the full price of a single issue and include any assumptions about the target profit used for the calculations (Appendix 3(a) and (b)). Based on the analysis, which subscription option do you think QMag should promote more heavily? 4 marks Table 2: replace text with a meaningful caption Subscription Options (months) % discount off single issue price (%) Discounted price ($) (single issue) Subscription Price ($) Include the break-even graph here with an appropriate figure label and caption. By referring to the graph by figure label, state and discuss the two supermarket options to sell QMag in store, including the rental agreement and the number of QMag issues to sell each month to break-even. (Appendix (c), (d)) 3 marks Explain the impact the difference in fixed cost has on the break-even quantity of sales for both options, and why the break-even number of QMag sales is higher for the second option. (Appendix 3(c)) 2 marks State whether 3000 magazines will be enough to make selling in supermarkets viable. 1 mark Which supermarket option do you believe QMag should take? Explain. 2 marks (10 Marks) Appendix 4 Maximising Advertising Revenue Discussion Points Loss of Marks! will occur if you use 'Decision variables', 'shadow prices', 'answer report', 'sensitivity report', 'binding' and 'non binding'. Use non-specialist terms, i.e. everyday language. Describe in 2-3 sentences, the aim of the analysis in this section. 2 marks Describe your linear programming solution in terms of maximum advertising revenue and the optimum number of each advertisement type. Explain whether the maximum allowable 25 Ads will be used in a single issue of QMag. 2 marks Discuss the potential increase in revenue if an extra Ad of any kind is allowed in the magazine; explain whether there is an upper limit to the number of extra ads allowable. (Appendix 4(c)) 2 marks Explain the impact on the maximum advertising revenue if an extra Gift Ad is included in the magazine. Now explain whether it is worth increasing the number of Gift Ads and why. (Appendix 4(d)) 4 marks To help achieve maximum marks for the report, note any assumptions made for calculations (e.g. rounding down or up and why, monthly repayments, etc). Highlight and delete the above question text before submission Conclusions and Recommendation (4 marks) Conclusion: Summarise the main findings of your report: base this on your discussion. Do not introduce new information in the conclusion. Do not use direct quotes. Indicate whether the report fulfilled the purpose as stated in the introduction. Recommendation: Base these on your conclusion. Do not introduce new information in the recommendation. Present options for resolving the issue (purpose) presented in the introduction. Be brief – use of dot points is acceptable. Highlight and delete this question text before submission [Click here and insert the text for the Conclusion] Do Not Carry Over The Report Body On This Page (Loss of 4 marks) Appendix 1 - [Enter a suitable appendix name] Highlight and delete the question text below before submission. Include full details of your working out in this appendix. Should QMag go in print? If QMag goes to print, you will need a consistent and timely supply of magazines to supermarkets and newsagencies. You are interested to acquire an in-house printing line for this reason and from extensive market research, you have decided the best option is to take over an existing company that provides printing services. You would like to conduct a Net Present Value (NPV) analysis to determine whether this is a financially viable venture. QMag estimates the printing line will require an initial investment of $80,000. The printing line is expected to increase annual profits by $10,000 each year for the first 3 years and after this time, $15,000 each year for the following 5 years. There will be no recoverable amounts at the end of the 8 years. (a) (8 marks) For businesses similar to QMag, the cost of capital is 10% per year compounded monthly. You can use this estimate in your analysis. Use EXCEL to calculate the net present value of the proposed investment for the takeover bid. Assume that increases in annual profits are realised at the end of each year. For full marks show the NPV calculation (showing the calculation via the Excel function NPV and Excel cell references is OK). Include a printout or copy your spreadsheet results here in this appendix. EXCEL Instructions: Set up your spreadsheet as below and add your initials to column names (i.e. instead of _BC, unless you really are a BC!). Note: you will need to fill in the relevant information for years 0 and 2-7; year 1 and year 8 have been done for you and are correct. Note! The NPV function in Excel has the following syntax (turn the page): NPV(rate, value1,value2,…), where rate is the discount rate or cost of capital, and value1, value2, … represent cash flows received at times 1, 2, and onwards until the final cash flow. You can input cash flows into the NPV function by referring to the range of Excel cells containing these cash flows, e.g. B3:B10 in the example spreadsheet above. Hint! Apply the EXCEL NPV function to expected increases in profits only, and take the initial investment at time 0 into account separately as shown in Lectures (Week 3). The NPV example in Week 3 lecture notes, and the corresponding spreadsheet, may be of help here. TOTAL 8 MARKS Please provide the requested non-quantitative analysis in the report body. Highlight and delete the above question text before submission   Appendix 2 - [Enter a suitable appendix name] Highlight and delete the question text below before submission. Include full details of your working out in this appendix. Which bank loan should QMag use for financing? QMag will need an additional $50,000 to customise and calibrate the printing plant to match the dimensions of the magazine the company wants to print. You are trying to decide between two loan options. The first option is to take a 5-year loan from a local bank, which charges 6.75% per year compounded quarterly. The second option is to double the length of the loan to 10 years for which interest will still be charged at 6.75% but compounded semi-annually. Before you begin! You will need to use the Week 3 Amortisation EXCEL spreadsheet on the Lecture Notes page of the course website and adapt it for this appendix. (a) (8 marks) Evaluate the first bank loan option. You will need to enter the correct information in the first four rows of the spreadsheet to calculate the quarterly repayment QMag would have to make on the first loan option. For full marks, set up your spreadsheet similar to that shown below and include descriptions of calculations where requested. You will need to change the number of rows in the amortisation repayment schedule to the exact number of repayments require, with the repayment shown to 2 decimal places. Include a printout or copy your completed and annotated spreadsheet here in this appendix. EXCEL Instructions: Refer to the amortisation example in the Week 3 lecture notes and the Week 3 Amortisation spreadsheet on the Lecture Notes page. Label each item in column A, rows 1-5 with your initials and label each Amortisation column (Payment Number, Opening Balance, …, Closing Balance) with your initials. Marks will be lost if this request is not met and also if the spreadsheet shows more or fewer repayments than required. Hint! Use formulae to compute the repayment size and to populate each value in the amortisation schedule (columns Opening Balance through to Closing Balance), otherwise the repayments will not balance to 0. (b) (8 marks) Now repeat Part (a) for the second bank loan option. The repayment itself should be shown to 2 decimal places. Again, ensure you show the calculation for i, n and the repayment size. Include a printout or copy your completed and annotated spreadsheet here in this appendix. (c) (4 marks) Using the EXCEL SUM function, calculate the total interest paid on both loan options. Now calculate the total amount paid on both loan options. State these values here. For full marks, provide a typed version of the EXCEL formulae used to obtain the values. TOTAL 20 MARKS Please provide the requested non-quantitative analysis in the report body. Highlight and delete the above question text before submission Appendix 3 - [Enter a suitable appendix name] Highlight and delete the question text below before submission. Include full details of your working out in this appendix. QMag subscription prices and sales on supermarket shelves To boost the circulation of the printed version of QMag, you are pursuing two outlets for sales. The first is subscription-based sales, where readers are offered a lower magazine price, to encourage them to order a bulk number of magazines in advance. As readers can order either 12 or 24 magazine issues in advance, you need to work out an appropriate price to charge subscribers in both scenarios. (a) (6 marks) There are a number of costs associated with each issue of a magazine subscription. The postage cost of each magazine is $2.45 and plastic sleeve packaging plus labels for each magazine costs 45 cents, with magazine consumables such as paper and ink costing $1.23 per magazine. You estimate it will cost $1,200 for a printing run for an issue, irrespective of the number of magazines printed, and the costs of paying salaries of writers will be $6,540 per magazine issue. Your initial target is 7,000 magazine subscribers. To ensure the viability of the subscription service, you will need to make a profit of $5,000 per magazine issue. Using this information, calculate the price you should set for a single magazine issue for a subscriber. Use two decimal places. Now calculate the price for all 12 issues to two decimal places. This is the subscription price you will offer readers who order 12 magazines in advance. Hint! you did something very similar in the Week 5 tutorial – check it out! (b) (5 marks) To encourage readers of QMag to order 24 magazines in advance, you want to offer another option: each issue at 40% off of the full price of QMag. The price you calculated in (a) for a single magazine issue is a discounted price for subscribers and is 70% of the full price of a single magazine. Using this information, work out the full price of a single issue of QMag. Using this full price, what then is the price of a single issue of QMag if a 24-month subscriber is given 40% off of the full price? What is the price for a subscriber to all 24 issues? Keep all answers to two decimal places. Hint: the first part of this question to work out the full price is very similar to a question in the Week 2 tutorial – check it out! (c) (7 marks) You'd also like to sell QMag on supermarket shelves, to increase the visibility and readership of the magazine. A supermarket chain has offered you two pricing models and you need to determine the better option. The variable costs will be the same as in (a) and a magazine issue will sell for $8.50 in the supermarket. However shelf space for magazines must be rented and as you are a new magazine to be stocked, the supermarket has provided you with two options for the first year of your agreement. Option 1 is a fixed rental price of $3,960 which is on top of the fixed cost from (a). Option 2 is to pay the supermarket 25% of the supermarket selling price of the magazine for each of 3,000 magazines to be stocked across the country. This cost is also on top of the fixed cost from (a). Calculate the number of issues of QMag to sell each month to break-even for both options. Explain the role, and impact, of the contribution margin (s-v) on the fixed cost and thus the break-even quantity in both cases (2-3 sentences is OK). (d) (4 marks) Illustrate the break-even number of sales of QMag from (c) for both supermarket shelf space rental options in one graph, as explained below. In the graph, you will need to produce one line for Income and one line per Total Cost option, so that your final graph will look like the graph on Slide 45 of the Week 4 lecture notes. On your graph, identify the location of the two break-even points and which option they correspond to. You do not need to identify the profit/loss regions. EXCEL Instructions: Create a column called Number of sales and in that column enter values from 0 to 5000 in increments of 250. Then create three more columns, one for Income and one for each rental option – call these Cost Option 1 and Cost Option 2. Enter appropriate formulae in EXCEL to obtain the total costs and total income corresponding to each value in the Number of sales column. Highlight the resulting four columns and go to Charts Scatter to obtain a graph. Make sure that your graph has been labelled appropriately (i.e. title, axis labels, legend) and that your chart title includes your network ID (the first part of your e-mail address, before the @ symbol). TOTAL 22 MARKS Please provide the requested non-quantitative analysis in the report body. Highlight and delete the above question text before submission Appendix 4 - [Enter a suitable appendix name] Highlight and delete the question text before submission. Include full details of your working out in this appendix. Double-Page Ad Full-Page Ad Gift Ad Get The Look Ad Maximising QMag's revenue from advertising An important source of income when producing a magazine is advertising revenue. Print-based revenue is different to online-based revenue so you cannot use the advertising model you have for the web version of the magazine. You need to formulate an advertising model specifically for print. You have decided to offer 4 different types of advertisements (Ads) in each printed issue of QMag: Double-page Ads where a single advertiser takes two adjacent pages in the magazine; Full-page Ads in which a single advertiser takes up one entire page in the magazine; Gift Ads which offer the reader a promotional gift attached to the outside of the magazine in a plastic sleeve (e.g. t-shirt, beauty products, journal, etc.); and Look Ads, which are "Get The Look" articles featuring a celebrity and showing where to purchase similar clothing/accessories/beauty products at a more affordable price. Due to space limitations, each magazine issue can contain at most 25 Ads in total. There is a total of 50 hours available to produce each Ad for print; Double-page and Full-page Ads each require 60 minutes of work to produce; Gift Ads take 2.5 hours and Look Ads take 2 hours to produce. There can be at most one Gift Ad per issue, however to ensure a sizeable revenue from advertisers, you wish to have at least 15 Double- and Full-page ads combined per issue. On the other hand, the combined number of Gift and Look Ads cannot exceed 6 per issue. To justify large advertising rates for Double-page Ads, the difference between the number of Double-page and Look Ads must equal two (hint: this is an equality constraint). You have set the revenue for Double-page ads as $4000, Full-page Ads as $2000, Gift Ads as $1000 and Look Ads as $3000. You would like to know the number of each ad type a single issue of QMag should contain to maximise advertising revenue. (a) (7 marks) Formulate a linear programming model for QMag. You should populate the template provided here with your model before moving to part (b). Decision Variables [Define the decision variables] Objective and Objective Function [Objective (Maximise/Minimise)] [Define the objective function using the decision variables] Constraints [Constraint Name] [Formulate the constraint using the decision variables] Note: state each constraint on a separate line. (b) (3 marks) Before you begin! COMPLETE (a) or you will likely get (b) wrong. Once (a) has been completed, you will need to create an Excel spreadsheet and enter your linear programming model from Part (a). You can find a template for a linear programming model online, in the file LP_Solver_template.xls (Lecture notes page). You will need to adjust the number of parameters and constraints in this template to match your model from (a). The Excel booklet, Week 5 lecture notes and Stevenson supplement will be useful to help you set up the Linear Programming model and interpret the output and make the requested changes. Use EXCEL Solver to obtain a solution to the linear programming model from part (a), together with an Answer Report and a Sensitivity Report. In this Appendix, provide a printout of your solved EXCEL spreadsheet and of the two Solver reports with your Appendix. Do not discuss the output, save this discussion for the report body! EXCEL Instructions for Solver: All EXCEL output should bear your e-mail ID. To ensure this, you will need to save your EXCEL file as 'E-mail ID Assignment 1.xls' BEFORE you run Solver. In addition, your constraint names should begin with your initials, e.g. BC Max Ads. Refer to Topic 5 in the Excel Booklet or the Linear Programming Supplement to the textbook (Lecture notes page, course website) for detailed instructions on how to set up a spreadsheet and use Solver. You may use the LP Solver template, available from the course website, if you wish. EXCEL Instructions for Pasting Excel Worksheets into this Appendix: In EXCEL select the cells you wish to include in this Appendix and copy them (Ctrl-C in Windows, command-C on a Mac). Now come back to Word and select Edit > Paste Special. A new window should pop up with paste options. If you are in Windows, select the picture option. If you are on a Mac, you can use either the picture or pdf option (the pdf option is clearer). Resize the pasted picture as you like. (c) (5 marks + 2 bonus marks) For the problem solved in part (b), interpret the non-negative shadow prices. You are not required to interpret the shadow price for the equality constraint, however there are 2 bonus marks in it for you, if you want to give it a shot! Do not interpret the negative shadow price. For those constraints with a shadow price of zero, explain in a single statement, what a zero shadow price indicates in general. (d) (5 marks) Your colleague suggests increasing advertising revenue by increasing the number of Gift Ads by 1 extra ad, to use up printing space. Using the shadow price from (c) for the number of Gift Ads, explain the effect on the maximum advertising revenue if the number of Gift Ads is increased by 1. By making this change, would the solution obtained in part (b) still be optimal? Which of the Solver reports helps you answer this question and how? Show your calculation for the new value of maximum advertising revenue when the number of Gift Ads is increased by 1. Attach the new Answer Report ONLY, for the scenario in which the number of Gift Ads is increased by 1, verifying your calculated maximum advertising revenue. Do not discuss the rest of the Answer Report – you will do this in the report body! Hint #1: the number of Gift Ads constraint would have been implemented as a non-negativity constraint in your model. This is the constraint to change! Hint #2: when interpreting a shadow price, the size of the change in the objective function is given by the value of the shadow price, while the sign of the shadow price gives the direction of change in the objective function. TOTAL 20 MARKS Please provide the requested non-quantitative analysis in the report body. Highlight and delete the above question text before submission