BACKGROUD INFORMATION You are hired to be the financial analyst at Happy Group of Companies, and you are to prepare one spreadsheet to show the different ratings of the hotels in the gaming/hotel industry in the United States of America. The rating is based on the hotel revenue over the total revenue generated in each hotel according to the following guidelines: - "A" rating for hotel revenue more than or equal to 45% - "B" rating for hotel revenue more than or equal to 38% - "C" rating for hotel revenue more than or equal to 28% - "D" rating for hotel revenue more than or equal to 18% - "F" rating for hotel revenue less than 18% (Hint: Use Vlookup or HLookup built-in function) You are also to remark "Try Next Year" if the Hotel Proportion Revenue is 45% or lower, and "Good Job it is over 45%. You may also want to calculate some statistical calculations such as Highest Revenue, Lowest Revenue, Average Revenue, etc.) Then, also plot an embedded chart to display any information that you like. Do all the above in Spreadsheet \#1 and name this spreadsheet REVENUE. REQUIREMENTS All the casino companies belong to Happy Holdings Group of Companies. On a second spreadsheet (name it FORECAST ), you are required to create a spreadsheet-based decision support model that allows the CEO of Happy Holdings Group of Companies to forecast all the companies' revenue, cost and gross profit from 2022 to 2026. You have to design use the What-if Analysis tool to design 3 different scenarios to allow the General Manager, to use MS EXCEL to compare figures generated by the program (e.g. Bull Time, Likely Time, Recession Time). Rather than having a separate model for each scenario, you are expected to design a single model and employ "scenarios" to change only those aspects of the model that varies with the scenarios being considered. On this spreadsheet, prepare a 5-year forecast (Y2022 to Y2026). The year 2022 figures are computed by taking the year 2021 figures and multiply it by the percentage increase given in the 3 different scenarios. (you may wish to use linking spreadsheet concepts to calculate the figures for Y2021. The different scenarios are as follows: The Y2021 Total Cost of Operations For Happy Holdings group of companies is $9,500M and will increase/decrease annually according to the above scenarios. The gross profit is calculated by taking the difference between the Total Revenue generated by the different hotels MINUS the Total Cost of Operations. You may wish to plot a bar chart (Separate chart) to display the gross profit over the 5 years using the Recession Time Scenario. Also insert a graphic in the spreadsheet and create a hyperlink for user to click on the graphic and it will bring the user to an appropriate web-site that might be useful for the manager. The company CEO decided to borrow a onetime loan of $8,000M in Jan 2022 to bring the company "out of the woods". Compute the gross profit in each scenario. On a separate row, add in the Loan amount in 2022. Given the bank charges 3.50% interest rate and the company wishes to borrow for 5 -year term, add another row for Annual Payment to the bank (Happy Group of Companies chooses to pay monthly payment, calculate the total 12 monthly payments using the appropriate built-in functions and formula.) Then compute the Accumulated Cash Flow from 2022 to 2026 (taking in consideration the profit/loss in 2021 as well as the loan and 12 monthly bank payments). You DO NOT need to worry about the Present Value/Future Value concepts (just to keep the assignment simple). Also prepare a memo as a GM to the CEO using MS Word (together with data and charts from MS Excel) to "advice" and comments on the sales figures projected based on the scenario using the recession time scenario. DESIGN ISSUES - Never use a number in a formula and try to plan your spreadsheet so that it can be re-used in the future. SUBMISSION You are required to e-mail the following to me with the e-mail subject (example FirstName_tastName Campus) (example: your subject line should read Johnsmith Mon or JohnSmith Tue): - Happy.xlsx which will contain 2 spreadsheets (revenue \& forecast as well as a separate chart) - Memo.docx - memo written to your CEO You should also make a backup of the system; late submissions will not be graded. BACKGROUD INFORMATION You are hired to be the financial analyst at Happy Group of Companies, and you are to prepare one spreadsheet to show the different ratings of the hotels in the gaming/hotel industry in the United States of America. The rating is based on the hotel revenue over the total revenue generated in each hotel according to the following guidelines: - "A" rating for hotel revenue more than or equal to 45% - "B" rating for hotel revenue more than or equal to 38% - "C" rating for hotel revenue more than or equal to 28% - "D" rating for hotel revenue more than or equal to 18% - "F" rating for hotel revenue less than 18% (Hint: Use Vlookup or HLookup built-in function) You are also to remark "Try Next Year" if the Hotel Proportion Revenue is 45% or lower, and "Good Job it is over 45%. You may also want to calculate some statistical calculations such as Highest Revenue, Lowest Revenue, Average Revenue, etc.) Then, also plot an embedded chart to display any information that you like. Do all the above in Spreadsheet \#1 and name this spreadsheet REVENUE. REQUIREMENTS All the casino companies belong to Happy Holdings Group of Companies. On a second spreadsheet (name it FORECAST ), you are required to create a spreadsheet-based decision support model that allows the CEO of Happy Holdings Group of Companies to forecast all the companies' revenue, cost and gross profit from 2022 to 2026. You have to design use the What-if Analysis tool to design 3 different scenarios to allow the General Manager, to use MS EXCEL to compare figures generated by the program (e.g. Bull Time, Likely Time, Recession Time). Rather than having a separate model for each scenario, you are expected to design a single model and employ "scenarios" to change only those aspects of the model that varies with the scenarios being considered. On this spreadsheet, prepare a 5-year forecast (Y2022 to Y2026). The year 2022 figures are computed by taking the year 2021 figures and multiply it by the percentage increase given in the 3 different scenarios. (you may wish to use linking spreadsheet concepts to calculate the figures for Y2021. The different scenarios are as follows: The Y2021 Total Cost of Operations For Happy Holdings group of companies is $9,500M and will increase/decrease annually according to the above scenarios. The gross profit is calculated by taking the difference between the Total Revenue generated by the different hotels MINUS the Total Cost of Operations. You may wish to plot a bar chart (Separate chart) to display the gross profit over the 5 years using the Recession Time Scenario. Also insert a graphic in the spreadsheet and create a hyperlink for user to click on the graphic and it will bring the user to an appropriate web-site that might be useful for the manager. The company CEO decided to borrow a onetime loan of $8,000M in Jan 2022 to bring the company "out of the woods". Compute the gross profit in each scenario. On a separate row, add in the Loan amount in 2022. Given the bank charges 3.50% interest rate and the company wishes to borrow for 5 -year term, add another row for Annual Payment to the bank (Happy Group of Companies chooses to pay monthly payment, calculate the total 12 monthly payments using the appropriate built-in functions and formula.) Then compute the Accumulated Cash Flow from 2022 to 2026 (taking in consideration the profit/loss in 2021 as well as the loan and 12 monthly bank payments). You DO NOT need to worry about the Present Value/Future Value concepts (just to keep the assignment simple). Also prepare a memo as a GM to the CEO using MS Word (together with data and charts from MS Excel) to "advice" and comments on the sales figures projected based on the scenario using the recession time scenario. DESIGN ISSUES - Never use a number in a formula and try to plan your spreadsheet so that it can be re-used in the future. SUBMISSION You are required to e-mail the following to me with the e-mail subject (example FirstName_tastName Campus) (example: your subject line should read Johnsmith Mon or JohnSmith Tue): - Happy.xlsx which will contain 2 spreadsheets (revenue \& forecast as well as a separate chart) - Memo.docx - memo written to your CEO You should also make a backup of the system; late submissions will not be graded