Frame Financial

Data file needed: Frame.xlsx

Jeri Carbone is the owner of Frame Financial, a small financial consulting firm in Marion, Iowa. Among her many tasks is to maintain Excel workbooks with information on companies and their stock market activity. One of her workbooks contains information on Harriman Scientific, a company traded on the stock exchange. She wants you to complete the workbook by adding charts that describe the companys financial status and stock charts to display recent values of the companys stock. The stock chart should display the stocks daily opening, high, low, and closing values, and the number of shares traded for each day of the past few weeks. The volume of shares traded should be expressed in terms of millions of shares. Complete the following;

1) Open the Frame workbook from Moodle. Save as yourlastname_Exam1_Frame.xlsx

2) In the Documentation worksheet, enter your name in cell B3 and the date in cell B4.

3) In the Overview worksheet, create a 3-D pie chart describing the companys shareholders based on the data source values in the range K4:L6.

4) Remove the chart title and chart legend and then resize the chart so that it is contained within the range M3:O7.

5) Change the fill colors of the ranges K4:L4, K5:l5, and K6:L6 to match their corresponding pie slice colors. Change the font color in those ranges to white.

6) In the Income Statement worksheet, create a clustered column chart of the companys net revenue and operating expenses using the data values in the nonadjacent range B4:F4;B6:F6,B12:F12. Move the chart to the Overview worksheet.

7) On the CHART TOOLS FORMAT tab, use the Size group to set the height of the chart to 2.44 and the width to 3.51. Move the chart so that its upper-left corner is aligned with the upper-left corner of cell B17.

8) Format the chart with the Style 11 chart style, change the chart title to Revenue and Expenses, and then reduce the font size of the chart title to 11 points.

9) In the Balance sheet worksheet, create a stacked column chart of the companys assets and liabilities using the data in the range B4:F4; B18:F18; B26:F26. Move the chart to the Overview worksheet. Set its size to 2.44 high by 3.51 wide and place the chart so that it is directly to the right of the Revenue and Expenses chart.

10) Change the chart title to Assets and Liabilities. Copy and paste the format used with the Revenue and Expenses chart into this chart.

11) In the Cash Flow Statement worksheet, create a 2-D line chart of the data in the range C4:F4; C28:F28. Move the chart to the Overview worksheet. Resize the chart to 2.44 high by 3.51 wide and place the chart directly to the right of the Assets and Liabilities chart.

12) In the line chart, apply the Style 13 chart style, change the chart title to Net Cash Flow, and then reduce the font size of the chart title to 11 points.

13) In the Income Statement, Balance Sheet, and Cash Flow Statement worksheets, add line sparklines to the Trend column based on the financial values for 2012 through 2015. 14) In the Stock Values worksheet, select the range A4:F34, and then insert a Volume-Open-High-Low-Close stock chart.

15) Move the chart to a new worksheet named Stock History.

16) Make the following changes to the stock chart: a) Change the chart title to Recent Stock History and increase the font size to 24 points. b) Set the font size of the horizontal and vertical axes to 12 points. c) Add Axis titles to the chart. Set the primary vertical axis title to Volume of Shares Traded, the secondary vertical axis title to Stock Value, and the horizontal axis title to Date. d) Set the font size of the axis titles to 16 points and rotate the text of the secondary axis title down. e) Remove the chart legend f) Change the scale of the primary vertical axis to range from 200,000 to 2,000,000 and then change the display unit of the primary vertical axis to thousands. g) Change the scale of the secondary vertical axis to range from 10 to 35. h) Add primary major horizontal and vertical gridlines, and remove any secondary gridlines. i) Set the gap width of the columns in the stock chart to 20%.

17) Save and close the workbook,