SPREADSHEETS 1. For this part of the assignment browse through all worksheets before attempting the question. Using the Microsoft excel workbook assgt_mgmt1000_open_s3_18.xls complete the questions below to provide solutions for the directors and board members of RSL, regarding their financial reporting requirements: [100 Marks] a. Using the “Green Savings” worksheet, provide the formulas to calculate the savings for each computer type usage, and corresponding totals. [4] b. Using the auto format feature format range A3:D12 in “Green Savings” worksheet. Note that values are to be formatted with the thousands separator, ZERO decimal places with “$” signs all values [2] c. Using the “Quarterly Revenue – 2016” worksheet provide the relevant formulas to complete the worksheet. Apply appropriate formatting. [5] d. In the same worksheet in part (c) dynamically apply the following formats for Qtr1 through Qtr4 for all revenue types: i. Values over $150,000 should be green and bolded [2] ii. Values under or equals to $80,000 should be red and italicize [2] e. Create a clustered bar chart with 3-D visual effect with all relevant labels, titles and legend while changing the default colors, to a selection of your own , different from what is shown below in Figure 1. The chart is to be inserted in new worksheet called Quarterly Revenue Chart – 2017 worksheet. Hint: change the font size on the text areas on the chart to assist in resizing the chart. [15] Figure 1: Pictorial view of chart requirements (Note: actual number representation may differ from actual results) f. Using the “Statement of Income” worksheet, center the financial statement titles across the data below and bold the company name. [2] g. Insert a column between columns D and E. Resize this new column to width 5.50 and apply a blue fill with a border around range E column to the point where it meets the “Net Income” row. [4] h. Insert a row above row 5 and resize to 9.50. [2] 0 200000 400000 600000 1 2 3 4 Other Investment Income Interest Sales i. Using the correct method/formula enter the correct values for each revenue item for 2016. [5] j. Complete the formulas in cells provided for revenues* for 2017. 2017 revenues have increased by ½ % over 2016 values. [10] k. Using the correct function or formula calculate the total revenue and gains in the cells provided. Insert the comment, “Discuss with sales manager” in the cell containing the words “Total revenues and gains”. [4] l. Complete the formulas in cells provided for expenses for 2017. 2017 expenses** have increased by ¾ % over 2016 values with the exception of computer expenses*** which should be used from the “Green Savings” worksheet. [15] m. Using the correct function or formula calculate the total expenses and losses in the cells provided. [4] n. Insert an exploded pie chart with a 3-D visual effect using all expense items for 2017, in a new worksheet called, “Comparing Expenses”. Include all relevant labels, titles (not shown) and legends while changing the default colors to your own, difference from what is shown below in Figure 2. Figure 2: Pictorial view of chart requirements (Note: actual number representation may differ from actual results) o. Using all relevant cells calculate the income tax expenses for each year. [10] p. Using the correct function or formula calculate the net income. [2] q. Format the comparative income statement use dollars signs where necessary. All values are to have the thousand separator format with no decimal places. [4] r. Using the “Aggregates” worksheet, complete all formulas (hint: must use “Statement of Income” worksheet). [3] s. Save your MS Excel workbook as studentid_open_s3_18xl for submission. For example 411012456_open_s3_18xl.