I THINK THIS CAN BE SOLVED IN EXCEL. IF REPORT NEEDED THEN MAKE IT

Grupo 3Stars is a mobile phone company that enjoys great recognition worldwide. European, it is also a leader in various branches of the electronics industry. despite being more Known as an electronics company, 3stars is also involved in heavy industry, automotive, maritime, air and chemical products. In the menth of September, the Management of 3Stare, obeerved the need to renew ite current range of products with the aim of continuing to maintain the levels of competitiveness achieved During the last years. During the last financial year, the Company's Research and Development department had developed a new substitute product for NOTE, called MEGA, a new Smartphone of larger than usual market size. The company beleves that precisely these extra centimeters will allow them to access a larger audience, an audience that until now has resisted using Smartphones. However, a part of the company's Management mistrusts of the economic viability of this new product, for which they ask us for your opinion. The Commercial and Financial departments of 3Stars have prepared the income statement provisional forecasting the following evolution of estimated income from the sale of the product MEGA: In addition, the income from the sale at the liquidation price of current stock of the NOTE product for 60,000 to a customer in North Africa, as well as the machinery used for the production of NOTE. Said machinery is useless for the production of MEGA, is fully amortized and represents a cash inflow of 70,000 additional. The forecast for operating expenses is as follows: - Cost of raw materials 34% of sales - Commission for sellers 15% on sales - Transportation costs 4% on sales The first year an advertising expense of 650,000 is expected, with an increase of 5% for the following exercises. The introduction of the new machinery will require the need to retrain the staff of production, estimating an expense of 14,000 the first year. - An additional expense of 40,000 is considered for the gross salary of the production in the frst year (includes payroll and social expenses), with an increase of 5% for the following exercises. - Other general expenses that will be increased by 27,000 the first year, with a increase of 5% for the following years. - Supplies due to the increased use of current facilities are expected to be around 618,000 , with an increase of 5% for the following years. - The expenses incurred for a prototype made while the characteristics were being decided MEGA product techniques. Said prototype amounts to 20,000. - This investment will force the occupation of a part of the unused facilities until the moment, whose amortization cost amounts to 5,000 per year. The increase in the rate of production will generate the need to rent a new warehouse from the third year. It is estimated that this cost amounts to about 23 thousand euros per year (increased by 5% for the following years). The necessary investment in assets for the start-up of the production of MEGA is estimated at 640,000 to be carried out in the current year. At the end of the project, the machine will be sold for 80,000 in Year 6 1. Are you satisfied with all the items considered in the estimation of the MEGA product planning? 2. Make the cost budget and analyze the results. Calculate the deadlock. 3. Prepares the treasury budget and analyzes the results. What should be the minimum financing for the Current Year? 4. Calculate the WACC, VAN and TIR. Is the project viable? 5. If the premium required by the shareholders was 20% instead of 8%, would project would be viable? 6. What actions can we take to improve the profitability of the project? Run the simulation in a new tab. Current gear YEAR * YEAR +2 YEAR +3 YEAR *4 YEAR +5 YEAR +6 9 Income from the sale of fized assets. 10 Yariable costs 11 Raw Materials 12 commissions 13 Transport 15 Gross margin 42 43 Deadpoint: ( % ) 44 Deadlock (ABSOLUTE) CASH FLOW PROJECTIONS Current gear YEAR *1 YEAR+2 YEAR 3 YEAR *4 YEAR +5 YEAR+6 Operating result [EBIT] Corporation tas Amortization OPERATING CASH FLOY FIXED ASSETS INYESTMENTS Machinery CURRENT NEEDS Customers - Suppliers FREE CASH FLOY Mortgage loan Amortization General line Financial expenses Mortgage Loan SHAREHOLDER CASH FLOY capital contribution dividends FINANCIAL CASH FLOY Treasurg at the beginning of the gear Treasurg at the end of the gear 84 GO AND TIR CALCULATION Foreign financing \begin{tabular}{r|r|} \hline GO \\ IRR \\ \hline \end{tabular} Grupo 3Stars is a mobile phone company that enjoys great recognition worldwide. European, it is also a leader in various branches of the electronics industry. despite being more Known as an electronics company, 3stars is also involved in heavy industry, automotive, maritime, air and chemical products. In the menth of September, the Management of 3Stare, obeerved the need to renew ite current range of products with the aim of continuing to maintain the levels of competitiveness achieved During the last years. During the last financial year, the Company's Research and Development department had developed a new substitute product for NOTE, called MEGA, a new Smartphone of larger than usual market size. The company beleves that precisely these extra centimeters will allow them to access a larger audience, an audience that until now has resisted using Smartphones. However, a part of the company's Management mistrusts of the economic viability of this new product, for which they ask us for your opinion. The Commercial and Financial departments of 3Stars have prepared the income statement provisional forecasting the following evolution of estimated income from the sale of the product MEGA: In addition, the income from the sale at the liquidation price of current stock of the NOTE product for 60,000 to a customer in North Africa, as well as the machinery used for the production of NOTE. Said machinery is useless for the production of MEGA, is fully amortized and represents a cash inflow of 70,000 additional. The forecast for operating expenses is as follows: - Cost of raw materials 34% of sales - Commission for sellers 15% on sales - Transportation costs 4% on sales The first year an advertising expense of 650,000 is expected, with an increase of 5% for the following exercises. The introduction of the new machinery will require the need to retrain the staff of production, estimating an expense of 14,000 the first year. - An additional expense of 40,000 is considered for the gross salary of the production in the frst year (includes payroll and social expenses), with an increase of 5% for the following exercises. - Other general expenses that will be increased by 27,000 the first year, with a increase of 5% for the following years. - Supplies due to the increased use of current facilities are expected to be around 618,000 , with an increase of 5% for the following years. - The expenses incurred for a prototype made while the characteristics were being decided MEGA product techniques. Said prototype amounts to 20,000. - This investment will force the occupation of a part of the unused facilities until the moment, whose amortization cost amounts to 5,000 per year. The increase in the rate of production will generate the need to rent a new warehouse from the third year. It is estimated that this cost amounts to about 23 thousand euros per year (increased by 5% for the following years). The necessary investment in assets for the start-up of the production of MEGA is estimated at 640,000 to be carried out in the current year. At the end of the project, the machine will be sold for 80,000 in Year 6 1. Are you satisfied with all the items considered in the estimation of the MEGA product planning? 2. Make the cost budget and analyze the results. Calculate the deadlock. 3. Prepares the treasury budget and analyzes the results. What should be the minimum financing for the Current Year? 4. Calculate the WACC, VAN and TIR. Is the project viable? 5. If the premium required by the shareholders was 20% instead of 8%, would project would be viable? 6. What actions can we take to improve the profitability of the project? Run the simulation in a new tab. Current gear YEAR * YEAR +2 YEAR +3 YEAR *4 YEAR +5 YEAR +6 9 Income from the sale of fized assets. 10 Yariable costs 11 Raw Materials 12 commissions 13 Transport 15 Gross margin 42 43 Deadpoint: ( % ) 44 Deadlock (ABSOLUTE) CASH FLOW PROJECTIONS Current gear YEAR *1 YEAR+2 YEAR 3 YEAR *4 YEAR +5 YEAR+6 Operating result [EBIT] Corporation tas Amortization OPERATING CASH FLOY FIXED ASSETS INYESTMENTS Machinery CURRENT NEEDS Customers - Suppliers FREE CASH FLOY Mortgage loan Amortization General line Financial expenses Mortgage Loan SHAREHOLDER CASH FLOY capital contribution dividends FINANCIAL CASH FLOY Treasurg at the beginning of the gear Treasurg at the end of the gear 84 GO AND TIR CALCULATION Foreign financing \begin{tabular}{r|r|} \hline GO \\ IRR \\ \hline \end{tabular}