I got this big assignmnet for Quantitative methods for business

i really need help

Azimi, Hamida - azihy004 AH Share Comments Delete this page before submission Q-Constructions - building your future Construction is a company that is trying to get the edge in and be recognised on the forefront of building custom construction projects. The company's try is to use mathematical techniques to produce curate insights that can increase their profits, inform their clients and provide them with the most affordable contracts You have just been recruited by Constructions to work in the back office to make and provide insights into their business model com project mana, marketing and financial mays. They believe you possess the skills to deal with the data and the number crimchine wind to propel the company forward-Jackey you! The big boss has a list of tasks objectives for you to eated with which we hireto the company's progression. The first task is to analyse the past financial data and produce mais ce their yearly profit margins. It is noticed that someone didn't do the job correctly and forgot to lodie their net profit a few years back, so you will have to use your soils to fit out The company has received a fairly large commercial building proposal that they want you to be and assess its financial ability and whether they should proceed. They have provided you with the cash flow breakdowns per month for the math of the project for you is the important question they want you to determine is that age in the project proposal is needed to be completed such that it will be financially viable. Finally, they wally have a few projects on the roat any time during a financial year, they need you to analyse the number of projects the need per year to break-evesThey can see that the hard working onsite workers have been in contact with the construction is and feel they deserve a pay-rise and their salary should be converted to a percent of a project price. Otherwis, they are ready to strike if their demands are not met -Construction seed to me the weekendende meet and provide them with important insights. On the other hand, the compuhas cond they need to be covered, and profit needs to be made overall to keep moving forward The company is fimded by some confidential investors, and they don't want the development you will be working on out! They are rely on your Quants mode so far to solve the problem They have rated you access to the internet to use Google and calculation too, however, they don't want you sharing secrets with your friend who might be working at a mal como They are currently planning their incentives for you as well...because happy to positive resu and they want to build your future...30 a cofee and muffins on them plus the potential of 19 n your course Delete this pare before on TL alam and the fall Delete AH E Share Comments Delete this page before submission The report length needs to be as follows: Delete this page before Introduction: At most 3 paragraphs where I paragraph is 3.5 sentences. Should appear on a separate page as indicated in the template The introduction should discuss the sims of all sections of the report Infographic: Exactly 1 page as indicated and with the elements in the presented order The infographic should start on a separate page after the introduction The infographic must contain the elements indicated in the allocated spaces DELETE the text in as indicated in the infographic You are welcome to customise decorate the rest of this area as you like Report Body: A maximum of 1 page of writing, excluding the infographic. Ensure you stay within these limits, The report body should start on a separate page after the infographic Use the questions given to guide your discussion of the results in Appendices 1-3. Clearly stated results and interpretations will help to earn maximum presentation marks Do not include numerical calculations or spreadsheets Do quote quantitative (the final answer!) results You should have no more than 1 page of writing (the infographic is excluded) Conclusion & Recommendations: At most 2 paragraphs where I paragraph is 4.6 sentences You should discuss the analyses that were presented for all three report body questions and provide recommendations Should appear on a separate page as indicated in the template The conclusion summarises the results do not introduce new information shoush recommendations are fine Font Types up to you, but keep in mind it should be easy to read make it easy for us to give you make! Font Sie minimum llet it shouldn't look smaller than this Amy text or fontsemaller than this sentence will incur marks (this is too small! Da't make your text the mall) Search 20 STUDENT Release (2) - Word View Review Shape Format Help es Mailings Introduction (2 marks) Purpose Provide a qualitative description of report contents/problems addressed in the report (covering Appendices 1-3) 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 at most 3 paragraphs, each with 3-5 sentences. Highlight and delete this message before submission Delete the blue box and write your introduction on this page] Delete this page before submission Report Body Marka Q-Constructions Financials By referring to the column chart (Appendix 1(b)), discuss the overall financial trends over the years. Draw a general observation quoting values from the chart (Appendix 10b)). By referring to the spadine (Appendix 1(c)), explain why there is a negative value for the sparkline and include it to support your discussion Based on the analysis, what can you conclude about the future of Q-Constructions? Explain briefly and refer to the sparkline to support your discussion (Appendix 1(a), (c) 3.3.2 marks 110 Marka) New Protect Financial Viability Loss of Murkol will occur you use the words prosent value, number of compounding period, or any variant thereof, in the discussion below in the worst case scenario, state the total amount of interest - Constructions would need to pay on the upfront initial outlay loan of $200,000 taken out and the interest rate (Appendix 2(a)) Briefly discuss the proposed cash flows for Q-Constructions each month. Ensure lo refer to the infographic (bar chart) to support your discussion State whether Q-Constructions should accept the project and the overall value in terms of today of this project's proposal including the current cost of capital (Appendix 2(b)) Explain, possible, when Q-Construction would receive a positive return on the project and why (Appendix (c)) 2.3.3.2 marks 12. Marks Polects.lects and workers salades Loss of Markel will occur if you use words such as variable cost' or contribution margin or any variant thereal, in the discussion below. Use everyday language. For full marks use the following words 'cost per ride' and 'profit per ride Briefly explain the key information such as the combined fixed & variable costs, the revenue per project per year Explain how many projects are required to break even per year. Make reference to the break even graph in the infographic as part of your discussion (Appendix 3(a), (D) Provide the insights of your analysis in relation to the number of projects that Q-Constructions should complete to make $500,000 profil per year. (Appendix 3 (b)) Q-Constructions meet the demands of the workers, summarise the analysis and the insights obtained in relation to the changes across the company. Ensure you quote the percentage that the demander require now, the new break even number and the number of projects required to achieve the target of $500,000 (Appendix 3c)() Explain whether Q-Construction will be financially profitable in the future they decide to adjust to the demands of the workers requirements (Appendix 1,3 (c)(O) 3.2.2.3.2 marks Highlight and delete the above question text before submission Page 7 of 19 4761 words EX English (Australia) es Mailings Start the Conclusions and Recommendation section on a separate page. Conclusions and Recommendation (2 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 dot points Highlight and delete this question text before submission Share Comments 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 AWESOMENESS IS OUR PASSION Insights into the Financial Profit of Q-Constructions A key to success of any business is to analyse their yearly profit margins and monitor performance. Each financial year, the figures are punched into a spreadsheet and glanced over to ensure the company is on track to produce a continual positive outlook surrounding the company and their clients. However, we all know that numbers always tell the truth and they want you to provide the hard facts and insights into their feeling of success. In addition, to analysing their financial outlook, they require you to portray the data visually. Table / is the data provided to you from the main spreadsheet describing the Net Profit figures and the Relative Percentage Change over the last 10 years. Unfortunately, it has some Raps that they need you to calculate! (a) (6 marks) It has come to your attention that someone in the company has not been consistent in entering financial data-some years are missing the relative percentage change or the net profit as presented below in Table 1. You are required to calculate the relative Percentage Change in the company for the blanks below and the Net Profit for the other blanks Hint! it's easier to convert a % to a number before commencing the calculation and more the percentage part of the calculation, i.e. where you multiply by 100% Show all working out below including the formula used for each year and include the completed table here. Year 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 Net Profit ($ 000) blanke 50 200 150 225 250 blank 200 250 260 Relative Percentage NA -50% 300% blank 50% 11% -30% 14% 25% Change Table : Net Profit (s) per financial year (b) (2 marka) Using Excel, create a coloro chart of the Net Profit calculated in part(a) for the years 2010-2019. For full marks, label the axis and provide an appropriate title. Please include it here, however, you will also use it in the infographic, How Instructions on how to create a colchart is on the course homepage under Excel videos and the Excel Supplement booklet. File () (marks) Using Excel, create a Sparklme of the Relative Percentage Change calculated in part () for the years 2011-2019. For full marks, use the Sparkime options to mark if there are any negative values and include a horizontal axis to easily visualise changes. The sparkline should be included here however you will also use it in the report body text. What is a sparklime I hear you ask? A sparkline is a tiny graph that appears in text like this exciting!!! You can customise them to change the line colour and individual marker colours as well I know how good is that?!? EXCEL Instructions: 1. In Excel select your calculations as shown below the values used here are not blank! 2. To create the sparkline go to Insert then Sparklines and select Line: Column We 3. You should see something similar to the window below - select the location for the sparkline I chose ES however you can place the graph anywhere). Press OK and a sparkline will appear after clicking OK 2011 2015 2016 2019 2012 200 2013 110 2018 30 225 11 4. Customise the sparkline using the Sparkline Colour and Marker Colour menus. Highlight the lowest and highest points using two different colours to the rest of the points: S. You've got a sparkline for your report! Hint! take a screenshot of the sparkline and crop it as the scale giving better visual presentation For full marks include a screenshot of your Excel spreadsheet showing 1. The variable names with your initials added. Also include the years in your set-up, 2. Your entered values and the corresponding sparklines TOTAL 10 MARKS Please provide the requested non-quantitative analysis in the report body. Hithitht and delete the above question text before submission O 8 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 appandix WWW After analysing the financial data of Q-Constructions, you notice that they are trending in the night direction. A new 12-month construction proposal has come to the company worth $1,000,000 and important question is whether it will be financially viable. They want you to analyse the proposal, in particular, the recommended cash flow schedule and to understand the key financial points during the construction project. The following cash flow schedule is summarised below. To ensure that all upfront and on-going outlay costs are covered in advance, Q-Constructions incur an initial start-up cost of $200,000. The proposal states that they will receive a deposit from the client of 10% of the total project price at the beginning. They then receive four equal instalment payments of 20% of the total project price associated to project milestones from the client at the end of the 24, 6, 8 and 10 month. Finally, they receive the last 10% project milestone on lock-up which occurs at the end of the 12 month Q-Constructions has ongoing project costs of $20,000 to pay salaries and services at the end of each month. In additional, there are material costs of $100,000 associated for each of the project milestones at the end of the 2", 6, 8 and 10 month. The current cost of capital for company is 8% per annum compounded monthly. You have been tasked with the important objective to determine whether this future project is financially viable. In addition, they want you to determine which milestone is needed to be completed in the project proposal such that it will be financially viable. It's time to show your Duarus browledge and expertise with Excel to determine the financial viability of this project (a) (4 marks) In a worst-case scenario where the project does not proceed, and the initial outlay is paid. Calculate the amount of interest that would have accrued on an amount of $200,000 at the end of 12 months with an interest rate of 8%..compounded monthly. Do not use EXCEL for this calculation (6) (6 marks) Set up a cash inflow and outflow for the 12-month construction project proposal based on the information provided by the company above. By using the current 8% Da compounded monthly cost of capital, calculate the Net Present Value of this proposal and whether it is financially viable project. Use EXCEL to calculate the net present value of the current situation For full marks show 1. The full wreadabest with all.completed entries. Show how you entered cash inflow and cash outflow amounts at the beginning, 14.2.3 months. You can type this in Word. 2. The NPV calculation (showing the calculation via the Excel function NPV and Excel cell references is OK). You can show this either in the spreadsheet or type it in Word. File Home EXCEL Instructions: Set up your spreadsheet as below and add your initials to column names (e unless your initials really are NFYT). The coloured boxes below contain instructions. We used the NPV function in Lectures (Week 2) - see lecture recordings for a demonstration. Month NFY Cash Flows NFY 0 1 2 Cost of Capital Change/sure that the cost capital is correct as in Appendix 2 text SN Enter the NPV formula in cel 13 using the NPV function NPV, vel, value 4 Each row of column will contain the cash low/outflows for the month. where the pote the cost of capital and the value valued are the cash Inflow/outflow 7 Enter a value for each month 10 $ 9 10 11 12 NPV INPV Formula) (c) (4 marka) The company wants to know at which milestone in the project proposal would be financially viable if the contract has terminated was early. Determine the milestone in the construction proposal for which the project would be financially viable, Hint: By using the NPV function in Excel and your cash flows in part(b), calculate the NPV if the project finished at the end of the 6 and 84 month in the proposed contract. (d) (2 marka) Q-Construction would like you to create a visualisation of the completed NPV spreadsheet from part (b). Include the graph here and in the infographic as indicated. EXCEL Instructions: Select your completed spreadsheet as shown below for months 0-12. Note the values shown in the screenshot are not correct and you should use the correct values from (b). Month NFY Cash Flow NFY 0 - 100.000 1 20,000 2 20.000 20,000 20,000 20,000 6 20.000 7 20,000 20,000 9 20,000 10 20.000 11 20.000 12 20,000 6) Go to Intert > Recommended Charts (below, top) and then choose Bar Chart (below, bottom) and click OK - you will get a chart similar to the one below. Your chart will look a bit different that's OK because you're using the correct values! Mailings Review View es Help Project Cash Flows NFY 13 1 20.000 40.000 120.000 300.000 0.000 0.00 0.000 30.000 Clustered Colum Chart Dollars (1) Customise with a meaningful title including your student network id and an x-axis label. Using a text box, annotate the graph with the Net Present Value of the project TOTAL 16 MARKS Please provide the requested non-quantitative analysis in the report body. Highlight and delete the above question text before submission te (c 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 Projects, projects and workers' salaries! Q-Constructions has tasked you to investigate the number of construction projects per year for which the company would need to break-even and make a profit of $500,000 per year. The average price of a building contract is $700,000 per project. The following are the fixed and variable costs of O. Constructions in Table 2: Description Office Space 55.000 Professional Staff Salaries 205.000 Insurances 50.000 Machine Maintenance 80,000 Website Management 30.000 On-site workers' salaries $120.000 per project Averare Material Cost 60% of the project price per proiect Table 2: Associated Costs of Q-Construction Use this information above to complete the requested analyses below. () (5 marks) Calculate: 1 The break-even number of projects needed by the company. ii. The income made by the company at break-even. Show all working out including the modelling and solution steps. (b) (3 marka) O Constructors is interested in making a profit per year to ensure the company has a positive financial outlook and new ventures can be done in the future. Calculate how many projects per year need to be completed to make a profit of $500,000 per year. (c) (6 marka) O Coretruction worker' have approached the building union and been informed they could be paid a higher salary and want their salaries to be determined based on a percentage of the project price. The company has reviewed their historical records on the munber of projects per year and has made the decision to respect the workers' demands and notice that the company would maintain a positive financial outlock if they set their break-even target at 4 projects per year. Determine the new salary percentage for the onsite worked on a project price based on the company's average project price and associated costs in Table 2. Ba (d) (3 marks) Based on the new on-site workers' cost per project from part (c), calculate the new number of projects that need to be completed to maintain a profit of $500,000 per year. (e) (3 marks) Due to the change in the on-site workers' salaries, what is the effect on contribution margin in relation to the variable cost? Explain the effect of this change on the break-even number in part (a). Hind Your discussion should focus on the impact made by the contribution margin. You can show the calculation of the contribution margin to support your discussion, but no other calculations should be used ((4 marks) In Excel, produce a break-even graph for Q-Constructions and include it here - you will also include a copy in the infographic where requested. There will be 3 lines on the Q-Constructions Breat-Ever graph: one for total revenue for Q- Constructions and two representing the original total cost and the new total cost for Q- Constructions On the graph, identify the general regions corresponding to profits and losses. The units along the x-axis will be the number of projects. The units along the y-axis gives the revenue in dollars. Want a video how-to on producing a break-even graph? We demonstrated this with a detailed explanation on the Week 3 lecture - check out the second hour of your class' recording The instruction below tell you what to name each column and other important details so keep reading! Excel Instructions: 1. Create a column called Number of Projects and enter values from 0 to 20 in single unit increments for Q-Constructions anabis. This column plays the role of 'x' in break-even calculations 2. Create four more columns: Total Original Cost, Total New Cost, Total Revenue, Total Profit and add your initials to these column names. In each of these columns, enter appropriate formulae in EXCEL to obtain the total cost and total revenue corresponding to each value in the Number of Projects column 3. Highlight all the columns and go to Insert Charts Scatter to obtain a graph Label the graph appropriately (e. title, axis labels, legend) and ensure the chart uitle includes your network ID (the part of your email address before e blogg@unisa.edu au has the network ID ilosa) TOTAL 24 MARKS Please provide the requested non quantitative analysis in the report body. Hirhlirht and delete the above question text before submission Azimi, Hamida - azihy004 AH Share Comments Delete this page before submission Q-Constructions - building your future Construction is a company that is trying to get the edge in and be recognised on the forefront of building custom construction projects. The company's try is to use mathematical techniques to produce curate insights that can increase their profits, inform their clients and provide them with the most affordable contracts You have just been recruited by Constructions to work in the back office to make and provide insights into their business model com project mana, marketing and financial mays. They believe you possess the skills to deal with the data and the number crimchine wind to propel the company forward-Jackey you! The big boss has a list of tasks objectives for you to eated with which we hireto the company's progression. The first task is to analyse the past financial data and produce mais ce their yearly profit margins. It is noticed that someone didn't do the job correctly and forgot to lodie their net profit a few years back, so you will have to use your soils to fit out The company has received a fairly large commercial building proposal that they want you to be and assess its financial ability and whether they should proceed. They have provided you with the cash flow breakdowns per month for the math of the project for you is the important question they want you to determine is that age in the project proposal is needed to be completed such that it will be financially viable. Finally, they wally have a few projects on the roat any time during a financial year, they need you to analyse the number of projects the need per year to break-evesThey can see that the hard working onsite workers have been in contact with the construction is and feel they deserve a pay-rise and their salary should be converted to a percent of a project price. Otherwis, they are ready to strike if their demands are not met -Construction seed to me the weekendende meet and provide them with important insights. On the other hand, the compuhas cond they need to be covered, and profit needs to be made overall to keep moving forward The company is fimded by some confidential investors, and they don't want the development you will be working on out! They are rely on your Quants mode so far to solve the problem They have rated you access to the internet to use Google and calculation too, however, they don't want you sharing secrets with your friend who might be working at a mal como They are currently planning their incentives for you as well...because happy to positive resu and they want to build your future...30 a cofee and muffins on them plus the potential of 19 n your course Delete this pare before on TL alam and the fall Delete AH E Share Comments Delete this page before submission The report length needs to be as follows: Delete this page before Introduction: At most 3 paragraphs where I paragraph is 3.5 sentences. Should appear on a separate page as indicated in the template The introduction should discuss the sims of all sections of the report Infographic: Exactly 1 page as indicated and with the elements in the presented order The infographic should start on a separate page after the introduction The infographic must contain the elements indicated in the allocated spaces DELETE the text in as indicated in the infographic You are welcome to customise decorate the rest of this area as you like Report Body: A maximum of 1 page of writing, excluding the infographic. Ensure you stay within these limits, The report body should start on a separate page after the infographic Use the questions given to guide your discussion of the results in Appendices 1-3. Clearly stated results and interpretations will help to earn maximum presentation marks Do not include numerical calculations or spreadsheets Do quote quantitative (the final answer!) results You should have no more than 1 page of writing (the infographic is excluded) Conclusion & Recommendations: At most 2 paragraphs where I paragraph is 4.6 sentences You should discuss the analyses that were presented for all three report body questions and provide recommendations Should appear on a separate page as indicated in the template The conclusion summarises the results do not introduce new information shoush recommendations are fine Font Types up to you, but keep in mind it should be easy to read make it easy for us to give you make! Font Sie minimum llet it shouldn't look smaller than this Amy text or fontsemaller than this sentence will incur marks (this is too small! Da't make your text the mall) Search 20 STUDENT Release (2) - Word View Review Shape Format Help es Mailings Introduction (2 marks) Purpose Provide a qualitative description of report contents/problems addressed in the report (covering Appendices 1-3) 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 at most 3 paragraphs, each with 3-5 sentences. Highlight and delete this message before submission Delete the blue box and write your introduction on this page] Delete this page before submission Report Body Marka Q-Constructions Financials By referring to the column chart (Appendix 1(b)), discuss the overall financial trends over the years. Draw a general observation quoting values from the chart (Appendix 10b)). By referring to the spadine (Appendix 1(c)), explain why there is a negative value for the sparkline and include it to support your discussion Based on the analysis, what can you conclude about the future of Q-Constructions? Explain briefly and refer to the sparkline to support your discussion (Appendix 1(a), (c) 3.3.2 marks 110 Marka) New Protect Financial Viability Loss of Murkol will occur you use the words prosent value, number of compounding period, or any variant thereof, in the discussion below in the worst case scenario, state the total amount of interest - Constructions would need to pay on the upfront initial outlay loan of $200,000 taken out and the interest rate (Appendix 2(a)) Briefly discuss the proposed cash flows for Q-Constructions each month. Ensure lo refer to the infographic (bar chart) to support your discussion State whether Q-Constructions should accept the project and the overall value in terms of today of this project's proposal including the current cost of capital (Appendix 2(b)) Explain, possible, when Q-Construction would receive a positive return on the project and why (Appendix (c)) 2.3.3.2 marks 12. Marks Polects.lects and workers salades Loss of Markel will occur if you use words such as variable cost' or contribution margin or any variant thereal, in the discussion below. Use everyday language. For full marks use the following words 'cost per ride' and 'profit per ride Briefly explain the key information such as the combined fixed & variable costs, the revenue per project per year Explain how many projects are required to break even per year. Make reference to the break even graph in the infographic as part of your discussion (Appendix 3(a), (D) Provide the insights of your analysis in relation to the number of projects that Q-Constructions should complete to make $500,000 profil per year. (Appendix 3 (b)) Q-Constructions meet the demands of the workers, summarise the analysis and the insights obtained in relation to the changes across the company. Ensure you quote the percentage that the demander require now, the new break even number and the number of projects required to achieve the target of $500,000 (Appendix 3c)() Explain whether Q-Construction will be financially profitable in the future they decide to adjust to the demands of the workers requirements (Appendix 1,3 (c)(O) 3.2.2.3.2 marks Highlight and delete the above question text before submission Page 7 of 19 4761 words EX English (Australia) es Mailings Start the Conclusions and Recommendation section on a separate page. Conclusions and Recommendation (2 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 dot points Highlight and delete this question text before submission Share Comments 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 AWESOMENESS IS OUR PASSION Insights into the Financial Profit of Q-Constructions A key to success of any business is to analyse their yearly profit margins and monitor performance. Each financial year, the figures are punched into a spreadsheet and glanced over to ensure the company is on track to produce a continual positive outlook surrounding the company and their clients. However, we all know that numbers always tell the truth and they want you to provide the hard facts and insights into their feeling of success. In addition, to analysing their financial outlook, they require you to portray the data visually. Table / is the data provided to you from the main spreadsheet describing the Net Profit figures and the Relative Percentage Change over the last 10 years. Unfortunately, it has some Raps that they need you to calculate! (a) (6 marks) It has come to your attention that someone in the company has not been consistent in entering financial data-some years are missing the relative percentage change or the net profit as presented below in Table 1. You are required to calculate the relative Percentage Change in the company for the blanks below and the Net Profit for the other blanks Hint! it's easier to convert a % to a number before commencing the calculation and more the percentage part of the calculation, i.e. where you multiply by 100% Show all working out below including the formula used for each year and include the completed table here. Year 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 Net Profit ($ 000) blanke 50 200 150 225 250 blank 200 250 260 Relative Percentage NA -50% 300% blank 50% 11% -30% 14% 25% Change Table : Net Profit (s) per financial year (b) (2 marka) Using Excel, create a coloro chart of the Net Profit calculated in part(a) for the years 2010-2019. For full marks, label the axis and provide an appropriate title. Please include it here, however, you will also use it in the infographic, How Instructions on how to create a colchart is on the course homepage under Excel videos and the Excel Supplement booklet. File () (marks) Using Excel, create a Sparklme of the Relative Percentage Change calculated in part () for the years 2011-2019. For full marks, use the Sparkime options to mark if there are any negative values and include a horizontal axis to easily visualise changes. The sparkline should be included here however you will also use it in the report body text. What is a sparklime I hear you ask? A sparkline is a tiny graph that appears in text like this exciting!!! You can customise them to change the line colour and individual marker colours as well I know how good is that?!? EXCEL Instructions: 1. In Excel select your calculations as shown below the values used here are not blank! 2. To create the sparkline go to Insert then Sparklines and select Line: Column We 3. You should see something similar to the window below - select the location for the sparkline I chose ES however you can place the graph anywhere). Press OK and a sparkline will appear after clicking OK 2011 2015 2016 2019 2012 200 2013 110 2018 30 225 11 4. Customise the sparkline using the Sparkline Colour and Marker Colour menus. Highlight the lowest and highest points using two different colours to the rest of the points: S. You've got a sparkline for your report! Hint! take a screenshot of the sparkline and crop it as the scale giving better visual presentation For full marks include a screenshot of your Excel spreadsheet showing 1. The variable names with your initials added. Also include the years in your set-up, 2. Your entered values and the corresponding sparklines TOTAL 10 MARKS Please provide the requested non-quantitative analysis in the report body. Hithitht and delete the above question text before submission O 8 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 appandix WWW After analysing the financial data of Q-Constructions, you notice that they are trending in the night direction. A new 12-month construction proposal has come to the company worth $1,000,000 and important question is whether it will be financially viable. They want you to analyse the proposal, in particular, the recommended cash flow schedule and to understand the key financial points during the construction project. The following cash flow schedule is summarised below. To ensure that all upfront and on-going outlay costs are covered in advance, Q-Constructions incur an initial start-up cost of $200,000. The proposal states that they will receive a deposit from the client of 10% of the total project price at the beginning. They then receive four equal instalment payments of 20% of the total project price associated to project milestones from the client at the end of the 24, 6, 8 and 10 month. Finally, they receive the last 10% project milestone on lock-up which occurs at the end of the 12 month Q-Constructions has ongoing project costs of $20,000 to pay salaries and services at the end of each month. In additional, there are material costs of $100,000 associated for each of the project milestones at the end of the 2", 6, 8 and 10 month. The current cost of capital for company is 8% per annum compounded monthly. You have been tasked with the important objective to determine whether this future project is financially viable. In addition, they want you to determine which milestone is needed to be completed in the project proposal such that it will be financially viable. It's time to show your Duarus browledge and expertise with Excel to determine the financial viability of this project (a) (4 marks) In a worst-case scenario where the project does not proceed, and the initial outlay is paid. Calculate the amount of interest that would have accrued on an amount of $200,000 at the end of 12 months with an interest rate of 8%..compounded monthly. Do not use EXCEL for this calculation (6) (6 marks) Set up a cash inflow and outflow for the 12-month construction project proposal based on the information provided by the company above. By using the current 8% Da compounded monthly cost of capital, calculate the Net Present Value of this proposal and whether it is financially viable project. Use EXCEL to calculate the net present value of the current situation For full marks show 1. The full wreadabest with all.completed entries. Show how you entered cash inflow and cash outflow amounts at the beginning, 14.2.3 months. You can type this in Word. 2. The NPV calculation (showing the calculation via the Excel function NPV and Excel cell references is OK). You can show this either in the spreadsheet or type it in Word. File Home EXCEL Instructions: Set up your spreadsheet as below and add your initials to column names (e unless your initials really are NFYT). The coloured boxes below contain instructions. We used the NPV function in Lectures (Week 2) - see lecture recordings for a demonstration. Month NFY Cash Flows NFY 0 1 2 Cost of Capital Change/sure that the cost capital is correct as in Appendix 2 text SN Enter the NPV formula in cel 13 using the NPV function NPV, vel, value 4 Each row of column will contain the cash low/outflows for the month. where the pote the cost of capital and the value valued are the cash Inflow/outflow 7 Enter a value for each month 10 $ 9 10 11 12 NPV INPV Formula) (c) (4 marka) The company wants to know at which milestone in the project proposal would be financially viable if the contract has terminated was early. Determine the milestone in the construction proposal for which the project would be financially viable, Hint: By using the NPV function in Excel and your cash flows in part(b), calculate the NPV if the project finished at the end of the 6 and 84 month in the proposed contract. (d) (2 marka) Q-Construction would like you to create a visualisation of the completed NPV spreadsheet from part (b). Include the graph here and in the infographic as indicated. EXCEL Instructions: Select your completed spreadsheet as shown below for months 0-12. Note the values shown in the screenshot are not correct and you should use the correct values from (b). Month NFY Cash Flow NFY 0 - 100.000 1 20,000 2 20.000 20,000 20,000 20,000 6 20.000 7 20,000 20,000 9 20,000 10 20.000 11 20.000 12 20,000 6) Go to Intert > Recommended Charts (below, top) and then choose Bar Chart (below, bottom) and click OK - you will get a chart similar to the one below. Your chart will look a bit different that's OK because you're using the correct values! Mailings Review View es Help Project Cash Flows NFY 13 1 20.000 40.000 120.000 300.000 0.000 0.00 0.000 30.000 Clustered Colum Chart Dollars (1) Customise with a meaningful title including your student network id and an x-axis label. Using a text box, annotate the graph with the Net Present Value of the project TOTAL 16 MARKS Please provide the requested non-quantitative analysis in the report body. Highlight and delete the above question text before submission te (c 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 Projects, projects and workers' salaries! Q-Constructions has tasked you to investigate the number of construction projects per year for which the company would need to break-even and make a profit of $500,000 per year. The average price of a building contract is $700,000 per project. The following are the fixed and variable costs of O. Constructions in Table 2: Description Office Space 55.000 Professional Staff Salaries 205.000 Insurances 50.000 Machine Maintenance 80,000 Website Management 30.000 On-site workers' salaries $120.000 per project Averare Material Cost 60% of the project price per proiect Table 2: Associated Costs of Q-Construction Use this information above to complete the requested analyses below. () (5 marks) Calculate: 1 The break-even number of projects needed by the company. ii. The income made by the company at break-even. Show all working out including the modelling and solution steps. (b) (3 marka) O Constructors is interested in making a profit per year to ensure the company has a positive financial outlook and new ventures can be done in the future. Calculate how many projects per year need to be completed to make a profit of $500,000 per year. (c) (6 marka) O Coretruction worker' have approached the building union and been informed they could be paid a higher salary and want their salaries to be determined based on a percentage of the project price. The company has reviewed their historical records on the munber of projects per year and has made the decision to respect the workers' demands and notice that the company would maintain a positive financial outlock if they set their break-even target at 4 projects per year. Determine the new salary percentage for the onsite worked on a project price based on the company's average project price and associated costs in Table 2. Ba (d) (3 marks) Based on the new on-site workers' cost per project from part (c), calculate the new number of projects that need to be completed to maintain a profit of $500,000 per year. (e) (3 marks) Due to the change in the on-site workers' salaries, what is the effect on contribution margin in relation to the variable cost? Explain the effect of this change on the break-even number in part (a). Hind Your discussion should focus on the impact made by the contribution margin. You can show the calculation of the contribution margin to support your discussion, but no other calculations should be used ((4 marks) In Excel, produce a break-even graph for Q-Constructions and include it here - you will also include a copy in the infographic where requested. There will be 3 lines on the Q-Constructions Breat-Ever graph: one for total revenue for Q- Constructions and two representing the original total cost and the new total cost for Q- Constructions On the graph, identify the general regions corresponding to profits and losses. The units along the x-axis will be the number of projects. The units along the y-axis gives the revenue in dollars. Want a video how-to on producing a break-even graph? We demonstrated this with a detailed explanation on the Week 3 lecture - check out the second hour of your class' recording The instruction below tell you what to name each column and other important details so keep reading! Excel Instructions: 1. Create a column called Number of Projects and enter values from 0 to 20 in single unit increments for Q-Constructions anabis. This column plays the role of 'x' in break-even calculations 2. Create four more columns: Total Original Cost, Total New Cost, Total Revenue, Total Profit and add your initials to these column names. In each of these columns, enter appropriate formulae in EXCEL to obtain the total cost and total revenue corresponding to each value in the Number of Projects column 3. Highlight all the columns and go to Insert Charts Scatter to obtain a graph Label the graph appropriately (e. title, axis labels, legend) and ensure the chart uitle includes your network ID (the part of your email address before e blogg@unisa.edu au has the network ID ilosa) TOTAL 24 MARKS Please provide the requested non quantitative analysis in the report body. Hirhlirht and delete the above question text before submission