A person who just won $10 million dollars cash from "Mark 6 ", goes to a financial company for help and advise on how to invest the $10 million dollars cash for a year in stocks, trusts and bank deposits, and obtain the best return. The financial advisor explains that there are risks involved in buying certain stocks and that an investment portfolio incurs expenses (e.g. commissions) as well as generating revenue. Both the client and the financial advisor have agreed to some profitability and safety goals in that they would like to maximize the portfolio net income but do not want to take unnecessary risks. They have identified five securities to invest: AD\&D common stock, King Cola common stock, PolyNet common stock - a red-hot Internet stock, certificates of deposit (CD) issued in a bank, and units of a real estate investment trust called Delaware REIT (DEL REIT). This one-year investment portfolio decision problem is done in Microsoft Excel spreadsheet using Excel's Solver, and you are required to write Excel formula and constraints to help with the calculations of this problem - see figure below. Microsoft Excel's Solver will then calculate the number of shares or units in cells B3 to B7 for the five securities mentioned above. The "Constants" section of the spreadsheet (see cells A10 to B34) contain the following data: Tax rate is expected to be 30% per year (cell B11) The "Calculations" section of the spreadsheet (see cell A42 to cell B89) contains immediate results that will be used in the income statement of the spreadsheet, and/or will be used as constraints. Write Excel formula for the following: - The amount invested in common stocks (cell B43) is the total $ invested in AD\&D, PolyNet, and King Cola. - Total amount invested (cell B44) is the total $ invested in all five securities. - The ratio of $ invested in stocks (cell B45) to the total $ investment. - The ratio of net income to total revenue (cell B46). - Total invested in AD&D and King Cola (cell B47) is the total S invested in these 2 stocks. - The ratio of $ invested in CDs (cell B48) to the total $ investment. - Ratio of (AD\&D and King Cola) to PolyNet (cell B49) is the ratio of total S invested in (AD\&D and King Cola) to the total S invested in PolyNet. - Cells B50 to B53 calculate the total S invested in AD&D, dividends \& interest earned on AD\&D, capital gain expected from selling AD\&D and administrative cost on AD\&D respectively. Similarly for the other four securities, the total S invested, expected capital gain, interest and dividend earned and administrative cost are calculated in the same ways from cell B55 to cell B70. The "Income Statement" section of the spreadsheet (see cell A81 to cell B89) contains the "net income" that the Solver is expected to maximize. Write Excel formula for the following: - Total interest and dividends, which are totaled for all five securities (cell B82). - Total gains on investment (cell B83), which are totaled for all five securities. - Total administrative costs (cell B85), which are totaled for all five securities. - The only costs here are administrative costs. - Gross income before tax (cell B87). - Income tax expense is zero if pre-tax revenue is zero or negative; otherwise, apply the tax rate to the pre-tax revenue (cell B88). - Net income after tax (cell B89). Constraints part Formulate constraints from the following statements made by the financial advisor: The financial advisor wants to earn a good return on investment, and he would like the net income to total investment revenue to be at least 10%. The financial advisor sets the following rules to spread the risks among the five securities: - At least $1 million must be invested in each of the frve securities. - The entire $10 million dollars may be invested, but at least $9.8 million dollars must be invested to maximize income. - It is not good to over-invest in common stocks as they have some risks; thus the ratio of S invested in common stocks to total S invested must be at least 40% but not more than 67%. - PolyNet is the riskiest common stock out of all three; as it is most likely to drop dramatically in price. Thus the amount invested in AD\&D plus King Cola must be at least as much as the amount invested in PolyNet. - Placing deposits in banks involves the least risks. To give the portfolio a safe foundation, at least 20% of the total funds invested must be in CDs. 1 The One-Year Portfolio Decision Changing Cells Shares of AD\&D Common Stock 4 Number of CD Certificates 5 Shares of PolyNet Common Stock 6 Shares of King Cola Common Stock 7 Shares of Delaware REIT 11 Tax rate expected 12 Cash available to invest 0.3 13 Minimum investment - each security 1000000 14 Unit price -AD\&D 50 15 Rate of return - AD\&D 0.07 16 Projected gain - AD\&D 0.1 17 Administrative cost rate - AD\&D 0.003 18 19 Unit price-CD's 20 Rate of return-CD's 1000 21 Projected gain-CD's 0 22 Administrative cost rate - CD's 0.001 23 Unit price - PolyNet 130 24 Rate of return - PolyNet 0 25 Projected gain - PolyNet 0.4 26 Administrative cost rate - PolyNet 0.015 27 Unit price - King Cola 80 28 Rate of return - King Cola 0.08 29 Projected gain - King Cola 0.1 30 Administrative cost rate - King Cola 0.004 31 Unit price - DEL REIT 10000 32 Rate of return - DEL REIT 0.15 33 Projected gain - DEL REIT 0.2 34 Administrative cost rate - DEL REIT 0.007 35 36 37 38 \begin{tabular}{l|l} 40 & \\ 42 & \\ 4 Calculations \end{tabular} 43 Amount invested in common stocks 44 Total amount invested 45 Ratio of \$ invested in stocks to total \$ invested 46 Net income to total revenue ratio 47 Total Invested in AD\&D and King Cola 48 Ratio of \$ invested in CDs to total \$ invested 49 Ratio of (AD\&D + King Cola) to PolyNet 50 Total invested in AD\&D 51 Dividends earned on AD\&D 52 Gain on AD\&D 53 Administrative cost - AD\&D 54 55 Total invested in CDs 56 Interest earned on CDs 57 Gain on CDs 58 Administrative cost - CDs 59 Total invested in PolyNet 60 Dividends earned on PolyNet 61 Gain on PolyNet 62 Administrative cost - PolyNet 63 Total invested in King Cola 64 Dividends earned on King Cola 65 Gain on King Cola 66 Administrative cost - King Cola 67 Total invested in DEL REIT 68 Dividends earned on DEL REIT 69 Gain on DEL REIT 70 Administrative cost - DEL REIT Income Statement Interest and dividends Gains Total Revenue Administrative costs Total Costs Pre-tax income Income tax expense Net Income