This assignment asks you to create a polished Sales Report from raw data. While you work through the steps to create the Sales Report, you can use the linked image files as an example of what each sheet in the completed Workbook should look like.
Setup instructions:
Before you begin, you will need to download the sample file called Sample File – Original.xlsx. Open this file and use the File, Save As option to save it under the name Sample File – Completed. You will need to download and have available two other files which you will be asked to use in completing the Workbook. These are Background Image.png and New Staff.csv. You should not open these files at this time. You will need to download and have available the image files that you will be using as a visual guide for each sheet in the workbook.
You can download access these files : https://github.com/alcadelina/Excel-
How to submit:
You are required to submit your assignment using a single file in .xlsx format. It's recommended that peer review assignment file uploads be 10MB or lower. Click the My submission tab to submit your assignment.
How to create your assignment:
All Sales sheet The workbook currently has a single worksheet. Rename this sheet to All Sales. Sort the data to organize it initially by Month and then by Sales Area. Use commands in the Font, Number and Cell Styles to make all content and headings to match the sample. Widen columns were necessary. Add in the Targets and Commission columns. In Targets, enter one value of 15000. Use Fill Down to repeat for all cells under the Targets heading. Enter a value of 10% in a cell and assign the name commission to that cell. In the first cell under the heading Commission, create an IF formula that checks if the sales amount on that row is greater than or equal to the target. If it is, the IF formula should calculate the commission due using the named cell. If the target is not met, the IF formula should return the value zero.
North, South, East, and West sheets Add in four new sheets. Rename them North, South, East, and West. In the All Sales sheet, use Filters to identify entries for each region. Use copy and paste to add the data into the new sheet of the same name. In each of the area sheets, create blank rows and add the headings. Format each heading as per the sample. Add a Conditional Format rule to the Sales Amount column in each area sheet which highlights the top 5 values in color. In each sheet, add the salespeople’s names as headings at the top right. For the first salesperson in each sheet, use SUMIF to calculate their sales total. Use dollar signs in the formula so that you can copy the formula to the right.
Copy of All Sales sheet Copy the All Sales sheet. Rename it Copy of All Sales. Clear all formatting from the titles. Make the data into a table with the Total Row feature turned on. Add totals in the Sales Amount and Commission columns. Name the table Sales_Data and format it with the style feature. Add the Over/Under column. Create a formula using table references to calculate this. Change the formatting so that the results are displayed as currency, with negative values in red.
Chart sheet Add a sheet called Chart. Create a summary block of data which contains the area totals for sales and commission. Use the Total Row of the Table to generate the totals but ensure that the values in the Chart sheet are constant and will not change. Create a Column Chart from the block of data. Once the chart has been created. Change the Title text and the appearance of the Chart as per the sample. Change only the Commission data series to a green color. Add a border and beveled effect around the edges. Add an Alt+Text description to the chart.
Sales Analysis sheet Position the cursor in the Sales_Data Table and insert a Pivot Table in a new worksheet. Rename that worksheet Sales Analysis. Design a PivotTable that show the sales totals broken down by month and formatted as currency. Have a second set of totals visible as a percentage of the grand total. Set up the PivotTable so that the whole table can be filtered by payment type. (Tip: if you add the heading month to the appropriate box in the PivotTable pane, it will probably be visible as two entries. You can remove one to leave just a list of unique month names.) Change the style of the PivotTable and reword the headings to match the sample. Add the Sales Area and Employee slicers to the sheet. Format them so that they stand out.
New Staff sheet Use options in the Data Ribbon to import data from the New Staff CSV file. Load the data. Break the link to the original file. Delete any connections and close any query windows. Add in blank columns and by using text function formulas or Flash Fill (as appropriate) fill them with the staff names transformed to match the sample. In one column use a text function to extract the area initials from the column called Payroll ID. Hide columns except for First Name, Last Name and Area Code.
Cover Page sheet Create a new worksheet called Cover sheet. Organize the sheets in the workbook so that they match the order in the sample. (Left to right they should be: Cover Sheet, All Sales, North, South, East, West, Copy of All Sales, Chart, Sales Analysis, and New Staff.) On the Cover Page sheet, add the heading and the sheet name headings. Add a background using the Background.png file. Add hyperlinks to the sheet headings to facilitate clicking on the heading to navigate to that sheet. Add Alt+Text to the hyperlinks to show the sheet name and not the full path. Add a color to the Cover Page, All Sales, and Sales Analysis tabs to make them stand out. Save and close the file.
Examples of Good Feedback
As a reviewer, you will be required to provide feedback on an assignment submitted by two of your peers. The focus of your feedback should be on the appearance of the worksheets and the consistent use of Excel features.
While you work through the steps to create the Sales Report, you can use the linked image files as an example of what each sheet in the completed Workbook should look like.
To measure if the learner has created a professional Sales Report, consider the following points in relation to each sheet: Sheet: Cover Sheet.
LO: Use formatting to set the scene in a report and provide a quick way to navigate the workbook. Q: Are you immediately aware of the purpose and the status of the information in this report?
Is there a background image?
Do the headings stand out in the same way as the sample?
Are the headings hyperlinks which you can use to jump directly to a specific sheet? (They should appear in a blue color with an underline.)
Are the sheets organized in the same order as the sample? Left to right the order should be: Cover Sheet, All Sales, North, South, East, West, Copy of All Sales, Chart, Sales Analysis, and New Staff.
Do the Cover Sheet, All Sales, and Sales Analysis sheet names have a different background color?
Sheet: All Sales LO: To use formatting, sorting, and naming tools to provide useful insights on a block of data. Q: Is the block of data organized and laid out in a way that helps the viewer to understand the data and the results presented?
Are the headings formatted to stand out?
Are there columns called Targets and Commission? Do these columns contain similar numbers to the completed samples?
Is there a 10% value-added to the sheet?
Sheets: North, South, East, and West LO: Use the filter and copy and paste tools to extract focused subsets of data from a larger block. Q: Does each sheet focus on one area and provide targeted insight on the performance of the team in that region?
Does each of these sheets contain only the data that is relevant for the area named?
Does each sheet have an overall heading that is formatted to stand out and centered over the data?
Are the salespeople’s names visible on the top right of the sheet with totals underneath? Do these totals match the samples?
Do all figures have the appropriate currency symbol next to them?
Are five of the figures under the heading Sales Amount standing out in a different color?
Sheet: Copy All Sales
LO: Use and understand storing data in a dynamic table and use table features to generate results. Q: Does the assignment display the sales data in an organized way and contain results that give a clear picture of performance?
Is the data formatted as a Table? (The headings should have a different background color. There should be drop-down arrows beside the heading and the rows should have alternating background colors.)
Is there a column on the right of the data called Over/Under? Do the figures under this heading match the sample? Are negative figures appearing in a different color?
Is there a row at the bottom of the data called Total? (This row will stand out in some way. For example, in the sample it there a double line above and a thick black line below.)
Are there number totals in the Sales Amount and Commissioncolumns?
Sheet: Chart LO: Use the Chart feature to create an effective visual representation of the Data. Q: Does the assignment provide a clear and easy-to-understand picture of the data in the Chart?
Is there a block of results in the sheet showing the Sales and Commission totals for each region? Do the figures in this block of data match the sample?
Is there a chart on this sheet with vertical columns?
Is the one called Commission colored green?
Does the Chart have the title Sales 2021?
Does the Chart have a border that stands out?
Sheet: Sales Analysis LO: Use pivot tables to summarize and analyze data Q: Does the assignment provide a breakdown and analysis of the sales data by month?
Is there a table of results on this sheet with Sales Totals shown by month?
Is there a second column of results shown as percentages?
Do these columns have the headings Month, Sales Total and % of Grand Total?
Above the table is there a heading Payment Type with a drop-down arrow? (This drop-down arrow will be to the right of an entry in brackets. That entry could read All, Cash, Credit Card or On Account.)
Are there two graphics on the sheet with colored buttons? One should be called Sales Area and have entries North, South, East, and West. The other should contain peoples’ names. They should have different color schemes.
Sheet: New Staff LO: Use the import tools to bring information from another file and then shape and refine it. Q: Has the assignment transformed the unrefined data into a form that is clear and easy to read and work with?
Are there only three columns with data called First Name, Last Name and Area? (These should be the only visible content. There should be no side windows open in Excel to the right of the screen.)
Do the names all begin with a capital letter?
Under the heading Area, are there entries comprised of just two initials?
Are the First Names in column C, the Last Names in Column D, and the Area entries in column E? Is the next column letter H?
No comments:
Post a Comment