please make a spread sheet analysis for the 3 scenarios given so I can calculate future questions. I don't need you to do any of the questions, I have only included them as a guide so you know what the spread sheet analysis requires. Please include the excel files in the upload. no referencing necessary
FIN1101 Major Assignment Task Sheet This assignment involves some different but realistic situations in which a finance graduate may one day find themselves. We have tried to make the assignment experience more realistic by portraying the 2-hour window that you have to complete the question set as a set of different ‘meetings’ with during which you answer questions. This is supposed to be both authentic and fun. The unrealistic bit is that all the questions are multiple choice! Do not worry! It is not difficult and everyone who puts in a genuine effort should do well. I expect the average score for this assignment will be very high. And it is good exam practice too. This assignment refers to the task detailed below. Some parts can be calculated in advance while other parts will need to be calculated once you access the assignment question set. Read the task below and prepare as much as you can in Excel (or by hand) and then log in once the assignment question set opens to answer a series of questions. The link to the question set is found under the ‘assessment tab’. Grades will be released once the deadline passes. In accordance with USQ policy, no attempts will be allowed once the deadline passes and the correct answers are available. USQ policy allows extensions to be granted, in compelling circumstances, if the application is received before the due date. Task Details Scenario #1: Grant’s Emporium You work for Dynamo Consulting Inc., a business consultancy, helping small to medium business owners make better financial decisions. W.T. runs Grant’s Emporium, a ‘five and dime’ store. He’s thinking of opening another store. Your firm has analysed the problem and determined a number of key data inputs. This morning, you are meeting with W.T. to discuss your results and answer his questions. Based on some emails and prior meetings, you have a good idea what W.T. will ask, so you can prepare ahead. Your manager also has some suggestions about additional questions that he might ask. You will need to be prepared to answer these during the meeting. Data: Opening Grant’s Emporium II The capital investment required for opening a new store is $1,500,000, covering shop-fitting and other equipment such as cash registers etc. Additional working capital of $75,000 is also needed. The fixed costs of running a new store are $90,000 in the first year while variable costs, including labour costs, are $120,000. Both fixed and variable costs are expected to grow in line with inflation at 2 percent p.a. Forecast sales are $780,000 in the first year but can be expected to grow by 5 percent p.a. Advertising is important to this type of business and will cost $60,000 per year. The capital investment is to be depreciated straight-line to zero. W.T. hopes to keep the store running for 10 years. The discount rate that your team believes should be applied to the cash flows is 12 percent. The taxation rate is 27.5 percent (because this is a small-medium business in Australia). All working capital is returned at the end of the project. You intend to tell W.T. the NPV and IRR for this project idea and advise him on whether or not the idea will create market value for him and his family who own and operate the stores. Your manager’s guidance: In addition to presenting the NPV and IRR (and associated advice), your manager, having worked with W.T. before, knows that he likes to scope out as many dimensions of a potential business decision as possible before committing. No doubt, W.T. will be interested in the sensitivity of the NPV and IRR to changes in forecast sales and the forecast growth rate. He might also be interested in knowing whether the project is viable if costs are greater (or grow faster) than expected or if additional working capital is required at different points. Being a bit of a finance buff, W.T. might also want to know more about the discount rate and how changes in it affect the viability of the project. You will need to make sure your spreadsheet analysis can cope with these adjustments so that you can quickly work out your answers during the meeting. Scenario #2: Family Finances & Retirement Planning Imagine now that you work for FinDec (Financial Decisions) Co. It’s a small firm, based in Brisbane, which provides basic financial advice, budget planning, and retirement income strategies. Today, you are dealing with the Runkle family. The Runkles have provided some of their financial information and a set of questions. You need to prepare your answers and also get yourself organised for a meeting with them later this evening in which additional questions might be asked. 1. Meet the Runkles: Harry and Marcia. The Runkles have two boys, Brian and Terry. Brian turns 11 years old in 2020 and starts secondary school in (2021) while Terry is a year younger and starts the year after (2022). The Runkles’ neighbours send their children to a private school. It costs $10,000 p.a. for year 7 and increases at a rate of 15 percent p.a. for years 8, 9, 10, 11 and 12. If, instead of sending Brian and Terry to the private school, they send them to the public school for a total cost of $10,000 each across all years, how much will the Runkles have saved by the end of 2027, when Terry would finish up? 2. If they choose the public school option, the Runkles could put the saved money into a trust that distributes the cash when the youngest, Terry, turns 50. The trust invests into a managed fund earning an average of 7 percent p.a., how much would the two boys have to divide between themselves when Terry turns 50? 3. Harry Runkle has a weakness. He likes cars. The new Porsche Cayman looks so good on the showroom floor that he could barely control himself. He managed to escape the salesman but has been thinking about the car night and day. It is priced at $149,000. He can get 0% finance for five years with weekly repayments of $399. At the end of the five years (read the fine-print!), Harry will have to pay the remaining balance. Bearing in mind that he already has a nice car, how much could Harry save over the next ten years if: (a) he puts $399 each week into a managed fund earning 5 percent p.a. (adjust this for weekly compounding); (b) takes the total accumulated sum at the end of five years and adds to it the balance that would have been outstanding on the car loan and invests the total for a further five years into the same managed fund earning 5 percent p.a.? 4. Your firm has discovered that unplanned grocery shopping has a significant impact on the family budget (and diet). Your colleague went shopping with the Runkles and found that they spend $350 per week at the grocery store and that this included very little in the way of actual food! They have to spend an additional $150 on other food items here and there throughout the week. By developing a meal plan based mainly on fresh fruit, vegetables and some meat and having a regular grocery order delivered to avoid impulsive purchases, your firm has worked out that the Runkles could reduce their weekly expenses by $145. If the Runkles save this amount into their superannuation fund and the fund earns 8 percent p.a., how much would they have saved over the next 15 years? Your manager’s guidance: Apart from answering these questions, the Runkles might have some additional scenarios to explore. For example, if they saved some amount per month into their superannuation fund, how much would it grow to? If they saved some amount into a managed fund, how long would it take until some amount was saved? That is, the sorts of basic present and future value math questions that can assist people with everyday decision-making. It should not be too complicated but you should do some practice to brush up your financial math skills before the meeting. Scenario #3: Risk & Reward Imagine, finally, that you are applying for a position as an analyst at Blackstone Asset Management. You are attending a testing session today. Some basic portfolio analysis skills will be tested. You have been given two series of prices (below) and you guess that you will have to compute the expected return and standard deviation (risk) for the two assets and portfolios containing different combinations of them. The company’s recruiters have made it clear that you can bring your spreadsheet files with you to the test and that you should prepare a basic framework with as much work pre-done as possible. Breville Monadel Date Price Dividend Price Dividend 1/12/2018 10.66 13.77 1/01/2019 10.98 14.77 1/02/2019 15.75 0.18 17.81 1/03/2019 16.28 17.35 0.25 1/04/2019 19.24 18.95 1/05/2019 16.88 19.07 1/06/2019 16.36 18.81 1/07/2019 19.23 18.93 1/08/2019 16.26 15.91 1/09/2019 16.1 0.18 15.75 0.23 1/10/2019 15.34 15.34 1/11/2019 16.98 16.02 1/12/2019 17.36 16.5 Beta 1.15 Beta 1.40 Your Mentor’s Advice: This should be relatively easy. BUT… Don’t forget that the prices must be converted to returns before you can start computing averages, covariance or standard deviation. Once you have two returns series, you can compute all these things very easily using Excel. You will have to be prepared to work out the portfolio expected return for different weights, so it might be best to set up a spreadsheet based on a calculation for the equally weighted portfolio and then change the weights once you know what they are. Given that the data set contains information about the betas, you might also expect questions based on the Capital Asset Pricing Model (CAPM). You should approach this assignment as follows: 1. Cover the necessary material (up to chapter 10). 2. Prepare your calculations and organise your results. 3. Prepare for the ‘open’ questions by either practicing the calculations or carefully preparing your spreadsheets so that inputs can be changed and new results determined easily. 4. The question set will become available a few days before the due date. Once you are ready, click on the link and start your attempt. 5. Like a quiz, the attempt must be completed once you start. The time limit is 2 hours. This should be more than enough