Assignment A3.1 - Making a Business Decision Part 2 In this final assignment, you are asked to answer several questions regarding a potential business idea (described below). Remember to use last week's work as the groundwork for this project. Apply all that you have learned throughout the course to answer the questions - and good luck! Please note: this is an individual assighment. A small bookstore on Gran Via, one of the main arteries through Madrid and a famous shopping street, has come up with the idea of delivering books to visitors to Madrid who stay in local hotels. Given the central location of the bookstore, the books would be delivered to tourists staying in nearby hotels within less than 30 minutes. The idea for the delivery service originally came from five MBA students at IE Business School who conducted a market study into the potential need for such a service. From personal experience, the students knew that it is easy to forget one's current book at home while traveling. The five MBA students spent last summer interviewing local tourists about the desirability of such a service. Manuel Munoz, the bookstore owner, sponsored this market study with 1,250. The market study also revealed that Amazon was planning to set up a similar delivery service in central Madrid. Due to various administrative delays, Amazon would only be able to start its delivery service in exactly 3 years from now. It would then immediately price Manuel's bookstore out of the market. The bookstore would need to buy two electrical bicycles for the delivery guys for 1,500 each. The two bicycles would be fully depreciated in a straight line over the three years at a rate of 10%. They could be sold for 400 each at the end of the project. The bookstore would also need to use an empty room at the back of the store as a storeroom. If the bookstore were not to go ahead with the delivery service, the empty room could be rented out at an annual rent of 2,000. The storage room would require heavy-duty metal shelves at a total cost of 4,000. These metal shelves would have to be fixed to the walls and would therefore be difficult to remove once fixed. Hence, Manuel assumes that the shelves will not have a resale value. He also assumes that they will not be depreciated. If going ahead, Manuel wants to advertise the new delivery service by distributing leaflets to nearby hotels. He has been quoted a fee of 15,000 for the design of various promotional items and another 3,200 for the printing of sufficient leaflets and other promotional items (such as free keyholders) to last for the next three years. The new delivery service would generate sales of 750 books, 1,000 books, and 1,200 books each month during the first year, the second year, and the third year, respectively. The average sales price of the books would be 20. The cost of the books sold is 75% of their sales price to the customers. Manuel expects that the number of books sold in-store would drop by 25% of the number of books sold via the delivery service. The average sales price per book for the eroded sales is 15 and the cost of the books sold is 80%. The bookstore pays a 30% tax on its profits. The two delivery guys needed for the delivery service would be paid 16,000 p.a. However, Manuel expects to relocate an existing employee who already works for the bookstore to the delivery service. Given the erosion of the in-store book sales, if Manuel were to go ahead with the delivery service, this employee would no longer be needed in the store. This employee currently earns 15,000 p.a. Net working capital of 12% of the annual sales revenue would need to be in place at the start of each year. The net working capital would be fully recovered at the end of the project. Assignment 3.1 Manuel is somewhat confused about his cost of capital. He knows that he should not rely on the bookstore's historic cost of debt. He therefore phoned up his bank manager who quoted him an interest rate of 7% if he were to take out a loan today. Following some extensive research on the internet, he is pretty sure that the equity beta of his bookstore is roughly 1.3. He asked one of his regular customers, a finance professor at IE, what risk premium he should use for the market premium. The professor suggested using a market risk premium of 5.8% p.a. However, Manuel does not know what rate to use for the risk-free rate. He looked up the yields on various debt securities issued by the Spanish government and he found the following: Debt security Current yield 3-month bills 1.820% 1-year bills 2.655% 3-year bonos 2.746% S-year bonos 2.952% 10-year bonos 3.384% Note: The Spanish word for bond is \"bono". The bookstore is currently financed by 35% of debt and 65% of equity. The new project is expected to require the same capital structure or mix of debt and equity as the entire bookstore. Manuel does not feel very confident about estimating his cost of capital. Would you be able to help him? What value would you suggest for the cost of capital? Please answer each of the following questions: 1. Determine the cost of capital for the delivery service. 2. Using the preparatory work you did in week 2, compute the NPV, IRR, and payback period for the project. Should Manuel go ahead with this project? Does any of the investment profitability metrics raise any concerns? 3. Manuel does not feel very confident about his estimated cost of capital. Given your calculations for part d), how would you be able to reassure him? Assignment Structure Your assignment will be an Excel spreadsheet clearly showing your answers to the questions below (three main questions, with each question subdivided into parts). For Question 1 (25% of your grade): A. Determine the cost of capital for the delivery service. B. Make sure to show your work for how you calculated this. For Question 2 (50% of your grade): A. Compute the NPV, IRR, and payback period for the project - calculate each. B. Should Manuel go ahead with this project? Write your recommendation (paragraph, using a cell in the Excel sheet and clearly label it as your answer to Question 2B) with your explanation for what your suggestion is for Manuel as far as going ahead with the project. For Question 3 (25% of your grade): A. Manuel does not feel very confident about his estimated cost of capital. Given your calculations for Question 1, how would you be able to reassure him? Write your recommendation (paragraph, using a cell in the Excel sheet and clearly label it as your answer to Question 3A) explaining your thought process. Assignment Format Your assignment should be handed in as an Excel sheet containing your calculations and answers to the questions (as listed above): e Question 1: A) Calculations of cost of capital Question 2: A) calculation of NPV, IRR, and payback period for the project; B) paragraph of justification/thought process. Question 3: A) paragraph of justification/thought process. Grading Your assignment will be graded as follows: COST OF CAPITAL CALCULATION Correct calculation of Cost of 25 possible points capital with proper work shown. COMPUTING NPV, IRR, AND PAYBACK PERIOD 50 possible points Correctly computing each value and providing an appropriate recommendation for Manuel on whether to accept the project or not COST OF CAPITAL RECOMMENDATION Correctly providing a 25 possible points thorough explanation of why the cost of capital is appropriate for the project. Considerations for Completing this Assignment This is individual work. Your assignment should be submitted in Excel format. Please make sure you include both your calculations and the text explanation within the Excel sheet, clearly labeling your text explanations as "Question 1 Response,\" "Question 2b Response," etc. (where applicable). e This assessment will be graded by your Learning Facilitator and will count as 60% of your grade. e Use the rubric in the Grading section (above) as a guide to understand how you will be assessed. Please label your assignment with the following format: as CXXFinanceAXX- LastNameFirstName Note: Please do not copy and paste the text above in your submission, as this could be detected as plagiarism by Turnitin when submitting your file. Rubric Assignment A3.1 Assignment A3.1 Criteria Ratings Pts This criterion is linked to a learning outcomeCOST OF CAPITAL CALCULATION 25 Pts 20 Pts 15 Pts 10 Pts 0 Pts 25 pt The ability of the Full Mostly Adequate | Inadequate @No Marks Correct Marks participant to calculate cost of capital and show their calculations This document has been classified as Internal Assignment A3.1 Criteria Ratings Pts This criterion is linked to a learning outcomeCOMPUTIN G NPV, IRR, AND PAYBACK PERIOD The participant was 50 Pts 40 Pts 30 Pts 20 Pts 10 Pts 0 Pts 50 pt able to correctly Full Mostly Adequate Inadequate Mostly No compute each value Marks Correct Incorrect Marks and provided an appropriate recommendation for Manuel on whether to accept the project. This criterion is linked to a learning outcomeCOST OF CAPITAL RECOMMENDATI 25 Pts 20 Pts 15 Pts 10 Pts 0 Pts ON 25 pt Full Mostly Adequate Inadequate No The participant S Marks Correct Marks provided a thorough explanation of why the cost of capital is appropriate for the project. Total points: 100 This document has been classified by mohannad.habaseh 2024-11-2511:22:287This document has been classified as Public PART B a) Based on your answer to part a) and following the general structure below, compile your cash flow statement. Cash flow / Year Profit before tax Profitaftertax | | | | | - [Netcashflow | | [ [ | This assignment is connected to the final assignment of the course. In the same way that this course builds on the previous week, your work after this week will provide the groundwork for your final project. A small bookstore on Gran Via, one of the main arteries through Madrid and a famous shopping street, has come up with the idea of delivering books to visitors to Madrid who stay in local hotels. Given the central location of the bookstore, the books would be delivered to tourists staying in nearby hotels within less than 30 minutes. The idea for the delivery service originally came from five MBA students at |IE Business School who conducted a market study into the potential need for such a service. From personal experience, the students knew that it is easy to forget one''s current book at home while traveling. The five MBA students spent last summer interviewing local tourists about the desirability of such a service. Manuel Munoz, the bookstore owner, sponsored this market study with 1,250. The market study also revealed that Amazon was planning to set up a similar delivery service in central Madrid. Due to various administrative delays, Amazon would only be able to start its delivery service in exactly 3 years from now. It would then immediately price Manuel's bookstore out of the market. The bookstore would need to buy two electrical bicycles for the delivery guys for 1,500 each. The two bicycles would be fully depreciated in a straight line over the three years at a rate of 10%. They could be sold for 400 each at the end of the project. The bookstore would also need to use an empty room at the back of the store as a storeroom. If the bookstore were not to go ahead with the delivery service, the empty room could be rented out at an annual rent of 2,000. The storage room would require heavy-duty metal shelves at a total cost of 4,000. These metal shelves would have to be fixed to the walls and would therefore be difficult to remove once fixed. Hence, Manuel assumes that the shelves will not have a resale value. He also assumes that they will not be depreciated. If going ahead, Manuel wants to advertise the new delivery service by distributing leaflets to nearby hotels. He has been quoted a fee of 15,000 for the design of various promotional items and another 3,200 for the printing of sufficient leaflets and other promotional items (such as free keyholders) to last for the next three years. The new delivery service would generate sales of 750 books, 1,000 books, and 1,200 books each month during the first year, the second year, and the third year, respectively. The average sales price of the This document has been classified by mohannad.habaseh 2024-11-18T11:15:07Z This document has been classified as Public books would be 20. The cost of the books sold is 75% of their sales price to the customers. Manuel expects that the number of books sold in-store would drop by 25% of the number of books sold via the delivery service. The average sales price per book for the eroded sales is 15 and the cost of the books sold is 80%. The bookstore pays a 30% tax on its profits. The two delivery guys needed for the delivery service would be paid 16,000 p.a. However, Manuel expects to relocate an existing employee who already works for the bookstore to the delivery service. Given the erosion of the in-store book sales, if Manuel were to go ahead with the delivery service, this employee would no longer be needed in the store. This employee currently earns 15,000 p.a. Net working capital of 12% of the annual sales revenue would need to be in place at the start of each year. The net working capital would be fully recovered at the end of the project. Instructions a) Attached is an Excel file with the potential incremental cash flows for this project. Determine which ones are incremental cash flows by stating Yes or No in the second column of the table below. Then state your reason for answering yes or no in the third column. b) Based on your answer to part a) and following the general structure in the excel sheet, compile your cash flow statements. You should submit the excel file provided with your answers to instructions a and b filled out. Assignment 2.1 Worksheet.xlsxDownload Assignment 2.1 Worksheet.xIsx Rubric Some Rubric This document has been classified by mohannad.habaseh 2024-11-18T11:15:07Z This document has been classified as Public Some Rubric Criteria Ratings Pts This criterion is a learnin outcomeCash Flow Correctly Selected Each cash flow is correctly identified 32.5 to >22.0 Pts 22 to >12.0 Pts 12 to >0.0 Pts 0 Pts 32.5 pts as incrementalot Full Marks Mostly Correct Mostly Incorrect No Marks incremental. (2.5 points for each correct cash flow) This criterion is linked to a learning outcomeDescription of Each Cash Flow The student provided an appropriate description of why 32.5 to >22.0 Pts 22 to >12.0 Pts 12 to >0.0 Pts 0 Pts each cash flow is 32.5 pts incremental/ not Full Marks Mostly Correct Mostly Incorrect No Marks incremental. (2.5 points for each correct description) This document has been classified by mohannad.habaseh 2024-11-18T11:15:07ZCriteria This criterion is linked to a learning outcomeCash Flow Statement The student provided a completed cash flow statement. Total points: 100 35 Pts Full Marks The cash flows are properly labeled, and correctly placed in the respective year. The student also correctly calculated the total cash flows before and after taxes. Some Rubric 27.5 Pts Mostly Correct Most cash flows are properly labeled, and only minor mistakes are found in calculations. Ratings 17.5 Pts Adequate Cash flow is properly structured, and while there are errors, there is an adequate understanding of how a cash flow statement works. 10 Pts Inadequate The cash flow statement is incomplete, and there are many errors. Pts 0 Pts No Marks No Cash Flow Statement provided. 35 pts Assignment 2.1 : Making a Business Decision Part 1 Download the following template to work on your assignment, you will submit this completed template for this assignment. Assignment 2.1 Worksheet.xlsxDownload Assignment 2.1 Worksheet.xIsx ASSIGNMENT 2.1 A: Complete BOTH tabs in this excel sheet and submit your completed file. PART A a) The table below lists the potential incremental cash flows for this project. Determine which ones are incremental cash flows by stating Yes or No in the second column of the table below. Then state your reason for answering yes or no in the third column. Incremental cash flow e (Yes/No) Cost of the design and printing of the leaflet and the cost of the keyholders Depremahon of the bikes Salvage value of the bikes The erosion of the existing book sales The income from the books and their cost The salaries of the two delivery guys The tax