ACC508 Informatics and Financial Applications Assignment

Assignment Detail:- Informatics and Financial Applications Assignment Part - ATMC 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, please download from BlackBoard- Part 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- Part 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- 1- Add a Customer Number column before the rest of the columns and give every Customer a unique number- 2- 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- 3- All columns should be formatted to use the correct data type for their contents- 4- Using conditional formatting, identify any cells with missing phone numbers and change their background colour to light blue- 5- 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- 6- Sort the data alphabetically on "Company name"- This will put the customer number out of order- Part 3 - 1- Add ta new worksheet to your Excel workbook, called "CustomerSales"- 2- Create a column for Customer numbers in Column A and create the customer numbers here in order- 3- In Column B retrieve the company name from CustomerData- This must be done using a Vlookup expression- 4- In Column C retrieve the sales from the month with the highest average sales from CustomerData- This must be done using a Vlookup expression- 5- In Column C retrieve the sales from the month with the lowest average sales from CustomerData- This must be done using a Vlookup expression- 6- 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- 7- Add a table on the "CustomerSales" worksheet listing the total sales for each month in columns I and J- 8- Add another column to your Total Sales table for the GST exclusive sales, calculated using the value in your GST_Exclusive named cell- Part 4 - 1- Add a new worksheet to your Excel workbook, called "CustomerSummary"- This should be the first worksheet in your workbook- 2- For each month sum the sales for each State putting the values in a table- Part 5 - 1- On a new worksheet build a Pivot Table showing the monthly sales of each customer in State and Territories columns Part 6 - 1- In the CustomerSummary 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- 2- In the CustomerSummary worksheet place a comment in cell A1 containing your name and student number- Attachment:- Informatics and Financial Applications Assignment Files-rar
solvedassignments.net Rated 4.8 / 5 based on 22789 reviews.
Captcha

Helping Students of Australia/New Zealand, GET Help with Classroom Assignments? Order Now