Reference no: EM133820023

Informatics and Financial Applications

Background

The assignment of a customer information workbook demonstrates Excels ability to automate and present financial calculation information clearly. The ability to build and professionally present a workbook is an important skill. Please note: that where the assignment states that you must use an expression, we mean a function. This workbook should be dynamic so if you change a value the change should flow to all affected values.

The starting workbook consists of one worksheet, download from BlackBoard.

Task 1
Format the worksheet to create professional looking worksheet, format the column headings, width and it is best not to use Excel to create tables. Those columns missing headings, please give the columns appropriate headings. Rename the worksheet to CustomerData

Remark: Font: Arial. Font size: 10, Border done, Text left aligned, Used Proper Function so that first letter of the Names should be in capital

Task 2
After completing task 1, a sequence of tasks needs to be completed on the CustomerData worksheet. These tasks are listed below: Hide the columns that you no longer need as you have formatted them, do NOT delete them.

Add a Customer Number column before the rest of the columns and give every Customer a unique number.
Remark: Done

Join the State and Postcode data into one column. This must be done using the various text functions available in Excel. It will require that you insert one additional column into this worksheet. One column should be labelled "State Postcode". In addition, all excess spaces should be removed from the text and all "State" text should be full upper case. This must be done using functions from Excel. You should also make sure there is a single space between the state and postcode.
Remark: Used "Concatenate" and "Upper" formula. Removed excess space using "Ctrl+H"

All columns should be formatted to use the correct data type for their contents.
Remark: Done

Using conditional formatting, identify any cells with missing phone numbers and change their background colour to light blue.
Remark: Use the following steps:
Select the data.
Go to the Home tab.
In the Conditional Formatting drop down, click on New Rule.
In the 'New Formatting Rules' dialog box, select 'Format only cells that contain'.
Select 'Blanks' from the drop down

Format the data in "First Name", "Last Name" and "company name" and so that it begins with a capital letter and the balance of the word is lower case. This must be done by using the appropriate Excel function.
Remark: Use "Proper" function.

Sort the data alphabetically on "Company name". This will put the customer number out of order.
Remark: Done

Task 3
Add ta new worksheet to your Excel workbook, called "CustomerSales".
Remark: Done
Create a column for Customer numbers in Column A and create the customer numbers here in order. Hire writer now!
Remark: Done

In Column B retrieve the company name from CustomerData. This must be done using a Vlookup expression.
Remark: Done

In Column C retrieve the sales from the month with the highest average sales from CustomerData. This must be done using a Vlookup expression.
Remark: Done

In Column C retrieve the sales from the month with the lowest average sales from CustomerData. This must be done using a Vlookup expression.
Remark: Done
These sales are all GST inclusive and we want GST exclusive sales. On the "CustomerSales" worksheet add one named cells called "GST_Exclusive" with a value of 0.0909% in L2. This cell should be appropriately labelled in your worksheet.
Remark: Done

Add a table on the "CustomerSales" worksheet listing the total sales for each month in columns I and J.
Remark: Done

Add another column to your Total Sales table for the GST exclusive sales, calculated using the value in your GST_Exclusive named cell.
Remark: Done

Task 4
Add a new worksheet to your Excel workbook, called "CustomerSummary". This should be the first worksheet in your workbook.
Remark: Done

For each month sum the sales for each State putting the values in a table.
Remark: Done

Task 5
On a new worksheet build a Pivot Table showing the monthly sales of each customer in State and Territories columns
Remark: Done

Task 6
In the Customer Summary Worksheet create a table with the columns, Month, Company Name and Amount of Sales. Use an expression to fill the table with data showing the company with the Maximum sale for each month. You will use the Functions INDEX and MATCH to find the company name of the maximum sale for the month.