BAB120 EXCEL PROJECT
SECTION A
Project Description:
Your parents have just told you that they will give you the money to update all your technology so that you will be successful in college. They want you to create a list of all the new devices that you need, note the cost, and provide a website for each device where they can find more information.
Steps to Perform:
Step Instructions Points
Possible
1 | Download the Excel file TIA_Ch2_Start.xlsx. Format the title in cell A1 with the Title Cell Style, and format the column headers in cells A3:F3 with the Heading 3 Cell Style. | 2 |
2 | Merge and Center A1 across columns A through F, and Center align the column headers in cells A3:F3. | 2 |
3 | Modify column widths so that Column A is 25 and Column D is 45. | 2 |
4 | In cells B4:F9, fill in the table with the Brand and Model of the six devices that you would like to purchase. The device type is filled out for you. In the Reason column, write a brief note as to why this device will help you. (You’ll format the text so it all displays later.) Enter the cost of the device in the Cost column. Don't include tax and/or shipping costs. Change the workbook Theme to Integral. | 2.5 |
5 | In cells F4:F9, create a Hyperlink to a webpage that features each respective product so your grandparents can have access to more information if they need it. Ensure that each hyperlink includes the URL to the exact webpage for the device in the Address, but displays the Make/Model of the device in the worksheet cell. | 2.5 |
6 | Wrap the text in cells C4:C9, D4:D9, and F4:F9 so all text displays. | 2 |
7 | Format the values in cells E4:E9 with Accounting Number Format with two decimals. | 2.5 |
8 | In cell A10, type Subtotal, then in cell E10 use a SUM function to calculate the total cost of all devices. Format the results in Accounting Number Format with two decimals. Autofit column E, if necessary. | 6.5 |
Step Instructions Points
Possible
9 | In cell A11, type Estimated Tax, then in cell E11, create a formula that references the subtotal in cell E10 and multiplies it by a tax of 6%. Format the results in Accounting Number Format with two decimals, if necessary. | 7 |
10 | In cell A12, type Estimated Shipping, then in cell E12, create a formula that uses the COUNTA function to count the number of devices being purchased (in column B), and then multiplies that number by 10 to apply a $10 shipping charge for each device. Format the results in Accounting Number Format with two decimals. | 7 |
11 | In cell A13, type Total Cost, then in cell E13, use the SUM function to create a formula that adds up the Subtotal, Estimated Tax, and Estimated Shipping costs. Format cells A13:E13 with the Total Cell Style. | 7 |
12 | Right align cells A10:A13. | 2.5 |
13 | In cell D14, type Estimated Monthly Payment, and then in cell E14 use the PMT function to calculate the monthly payment owed to your grandparents to pay back the total purchase amount (in E13) in two years at a 2.5% annual interest rate (the result will display as a negative value in your worksheet). | 4.5 |
14 | Save the workbook and submit for grading. | 0 |
Total Points | 50 | |
SECTION B
Project Description:
As a sector analyst for one of the regional banks, you are responsible for reviewing and analyzing data to determine trends. Your analyses are used by bankers to inform prospective and current clients of possible investment opportunities. Currently, you are looking at the market trends of operating systems and have obtained historical market share data for mobile/table operating systems in the U.S., global market share data for desktop and table operating systems, and market share data for all operating systems for select countries around the world. You will chart the data to determine trends.
Using Excel, you will display the market share statistics of both Mobile and Desktop Operating Systems using line, column, and pie charts.
Steps to Perform:
Step Instructions Points
Possible
1 | Download the Excel file TIA_Ch5_Start.xlsx. | 0 |
| Step | Instructions | Points Possible |
2 | Create a 2-D Area chart using the range A3:G7. Add a Chart Title: Change in Mobile OS Market Share 2013-2018. Place 2013-2018 on a separate line. Modify the font size of 2013-2018 to 10. Position the chart to fill cells J2:S19. | 10 |
3 | Filter the Area chart by removing Others and Windows data so only Android and iOS data displays. If necessary, resize the chart to ensure that it fill cells J2:S19. Mac users, use the Select Data Source dialog box to filter the data. | 5 |
4 | Add a Callout: Line Shape to the Area chart with the line pointing to the top of the orange area between 2014 and 2015. Add text to the callout: Android Market Share Takes Off!. Format the callout with Subtle Effect - Gold, Accent 4 Shape Style. Adjust the size of the shape, as necessary, so that all the text in the callout is visible. Note, depending on the version of Office used, the shape name may be Line Callout 1. | 5 |
5 | Create a 2-D Pie chart using the ranges A15:A21 and G15:G21. Move the chart so it is on its own worksheet. Rename the worksheet Global OS. | 7.5 |
6 | Modify the chart as follows: Add a title: Operating System 2018 Global Market Share Use Quick Layout 1 to add data % and Series data labels to each data point. | 7.5 |
7 | On the Data worksheet, create a Clustered Column chart using cells A26:A39 and D26:D39 to show only Android data. Place the chart to fill the range I26:S45. Add a title: 2018 Android Market Share by Country. | 10 |
8 | Add a Column Sparkline to cells H16:H21 using the data in range B16:G21. Increase the width of column H to 15. | 5 |
9 | Create a Filled Map Chart from the data in ranges A26:A39 and D26:D39. Place the chart to fill the range A42:G56. Add a title: 2018 Android Market Share by Country. | 0 |
10 | Save and submit the workbook for grading. | 0 |
Total Points | 50 | |