15. Header: In the right section of the header, type Prepared by followed by your name and the date prepared on a separate line. 16. Footer: In the left section of the footer, insert the file name. In the middle of the footer, enter the page number and number of pages. In the right section of the footer, insert the worksheet name. Each of these should use the automatic field available. 17. Preview the workbook (using print preview) making sure your headers, footers, and tables are properly displaying on the page. 18. First, save the workbook as \{LastFirstName\}_AppwichProject_01.xlsx, where \{LastFirstName\} is your Last name followed by your first name, capitalizing the first letter (same as Step \#1). 19. Next, group select all worksheets EXCEPT THE DATA Transactions SHEET with the transactions, and save your file in a PDF format using the 'Save As' dialog. To do this, select 'Save As' and make sure your filename is named appropriately (same as step above) in the File name: text field. Click within the 'file type' are to select PDF (*.pdf) as the file type. Then you must click on the 'Options' button to see the Options dialog and select 'Active sheet(s)', so only your selected workbooks will be saved in the PDF. Select OK and then save your file. You can open your PDF file (using a PDF viewer like Adobe Acrobat) to make sure your file was correctly converted. Figure 3: Use 'Save As', NOT 'Save as Adobe PDF' 9. Create a new worksheet in your workbook and place immediately after your "Documentation" worksheet. Rename this worksheet at "Descriptives". In this worksheet using the example given below, conduct a descriptive analysis of your dataset using SIDEV.S(), MIN(), MAX(), Range*, COUNI(), SUM() \} (typed in numbers will be heavily penalized). This should include appropriate measures of location and variability. ( note: you must enter your own formula to calculate the range). 10. With the descriptive analysis, evaluate your data for any missing values (hint: evaluate the COUNT() of records per column, or use a COUNTBLANK() function). If any blank values are found, calculate the average of the column and enter this number in the blank cell within the DATA_Transactions. Below your descriptive table, make a note of the transaction ID and the field of which you conducted this imputation. (e.g. Note: the following fields were imputed with the average value: ID:476, Sold; ID:2879, Price) 11. An example is provided for you below. As a note, examples in this document are given to provide you guidance. All assigned datasets are unique and results will be different. Color styles and formatting options are your choice. Figure 2: Example descriptive statistics table. Red marks indicate areas where missing values exist and require notation for imputation. FORMATTING AND PRESENTATION Formatting You will need to prepare your workbook that, if printed, it is legible to be understood by the audience. Prepare your workbook to be printed with the given formatting guidelines. 12. Format your entire workbook in a color scheme of your choice. The formatting should be appropriate and tasteful to tell your data story. Make sure your color choices are consistent across all worksheets. 13. Scale each worksheet (excluding the data worksheet) so that it the width fits on a single page in landscape orientation. 14. Adjust the line breaks so that your charts and tables are not cut in half. Figure 1: Example documentation worksheet ART 1 EXPLORATORY - KNOW YOUR DATA or this objective, you must identify the quantitative data in your dataset and prepare summaries of our data. Requirement guidelines are provided below. 5. You will be given a dataset by your instructor in a *.Csv format. You must import this data into an Excel workbook into a single worksheet. Name this worksheet "DATA_Transactions". 6. Before you begin, be sure you understand your data. You will need to prepare your data to conduct your analysis. Not all goods sold have dates of sale. You will need to replace all "N/A" in the 'Date' column with the data September 1, 2012 (a rough middle date of the dataset). Format this column with a date formatting of your choice. 7. Create the following calculated columns with the following formulas: Table 1: Formula reterences for calculated columns Format all dollar related columns to accounting showing 2 decimal places. 8. Your data set requires you to calculate different time periods. Create 4 calculated columns with the following headers (Month, Day, Year, and Quarter) referencing the column with the date value. Table 2: Function references for date fields Format these new fields with General formatting