Part A- Visual Workshop
Open the file EX J-7.xlsx and create the custom chart shown in FIGURE. (Hint The trendlines forecast three periods forward and use the standard line colors red and purple.) Save the workbook as EX J-Organic Sales. Study the chart and worksheet carefully to make sure you select the displayed chart type with all the enhancements shown. Enter your name in the center section of the worksheet footer, then preview the worksheet in landscape orientation on one page. Submit the workbook to your instructor.
Part B- Independent Challenge 2
As the senior accountant at Cambridge Electrical Supply you are adding new features to the company's accounts receivables workbook. The business supplies both residential and commercial electricians. You have put together an invoice table to track sales for the month of June. Now that you have this table, you would like to manipulate it in several ways. First, you want to ilk the table to show only invoices over a certain amount with certain order dates. You also want to subtotal the total column by residential and commercial supplies. To prevent data entry errors you will restrict entries in the Order Date column. Finally, you would like to add database and lookup functions to your worksheet to efficiently retrieve data from the table.
a. Start Excel, open the file EX H-4.xlsx from the location where you store your Data Files, then save it as EX H-Invoices.
b. Use the Advanced Filter to show invoices with amounts more than $100.00 ordered before 6/15/2016, using cells A27:828 to enter your criteria and extracting the results to cell A33. (Hint: You don't need to specify an entire row as the criteria range.) Enter your name in the worksheet footer.
c. Use the Data Validation dialog box to restrict entries to those with order dates between 6/1/2016 and 6/30/2016. Test the data restrictions by attempting to enter an invalid date in cell B25.
d. Enter 23698 in cell G28. Enter a VLOOKUP function in cell 1128 to retrieve the total based on the invoice number entered in cell 628. Make sure you have an exact match with the invoice number. Format 1128 using Accounting format with two decimal places. Test the function with the invoice number 23720.
e. Enter the date 6/1/2016 in cell J28. Use the database function, DCOUNT, in cell K28 to count the number of invoices for the date in cell J28. Save the workbook, and then preview the worksheet.
f. On the Subtotals worksheet, sort the table in ascending order by Type, then convert the table to a range. Create subtotals showing the totals for commercial and residential invoices. Display only the subtotals for the commercial and residential accounts along with the grand total.
g. Save the workbook, preview the worksheet, close the workbook, then exit Excel. Submit the workbook to your instructor.
Part C- Independent Challenge 1
As the office manager of Ocean Point Consulting Group, you need to develop ways to help your fellow employees work more efficiently. Employees have asked for Excel macros that can do the following:
- Adjust the column widths to display all column data in a worksheet.
- Place the company name of Ocean Point Consulting Group in the header of a worksheet.
a. Plan and write the steps necessary for each macro.
b. Start Excel, open the Data File EX I-1.xIsx, then save it as a macro-enabled workbook called EX I-Consulting.
c. Check your macro security on the DEVELOPER tab to be sure that macros are enabled.
d. Create a macro named ColumnFit, save it in the EX I-ConsulIing.xlsm workbook, assign the ColumnFit macro a shortcut key combination of [Ctrl][shift][X], and add your name in the description area for the macro. Record the macro using the following instructions:
- Record the ColumnFit macro to adjust a worksheet's column widths to display all data. (Hint: Select the entire sheet, click the HOME tab, click the Format button in the Cells group, select AutoFit Column Width, and then click cell Al to deselect the worksheet.)
- End the macro recording.
e. Format the widths of columns A through G to 8.43, then test the ColumnFit macro with the shortcut key combination [Ctrl][Shift][X].
f. Create a macro named CompanyName, and save it in the EX I-Consulting.xlsm workbook. Assign the macro a shortcut key combination of [Ctrl[Shift][Y], and add your name in the description area for the macro.
g. Record the CompanyName macro. The macro should place the company name of Ocean Point Consulting Group in the center section of the worksheet header.
h. Enter CompanyName test data in cell A1 of Sheet2, and test the CompanyName macro using the shortcut key combination [Ctrl][Shift][Y]. Preview Sheet2 to view the header.
i. Edit the CompanyName macro in the Visual Basic Editor to change the company name from Ocean Point Consulting Group to Shore Consulting Group. Close the Visual Basic Editor and return to Excel.
j. Add a rectangle button to Sheet3 in the range A6:B7. Label the button with the text Company Name.
k. Assign the CompanyName macro to the button.
l. Enter New CompanyName Test in cell A1. Compare your screen to given figure. Use the button to run the CompanyName macro. Preview the worksheet, checking the header to be sure it is displaying the new company name.
m. Enter your name in the footer of all three worksheets. Save the workbook, close the workbook, then submit the workbook to your instructor and exit Excel.
Attachment:- Assignment.rar