+1-415-670-9189
info@expertsmind.com
Format worksheet to create professional looking worksheet
Course:- Other Subject
Reference No.:- EM132369421




Assignment Help
Expertsmind Rated 4.9 / 5 based on 47215 reviews.
Review Site
Assignment Help >> Other Subject

Informatics and Financial Applications Assignment Task - 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.

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.

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.

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.

Task 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.

Task 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.

Task 5 -

1. On a new worksheet build a Pivot Table showing the monthly sales of each customer in State and Territories columns

Task 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




Put your comment
 
View Conversion
Minimize
  1. user image
    len2369421

    The assignment MUST be submitted online using Blackboard and on time. The submission link will be open at least a week before the due date. Please follow the submission instructions in Blackboard. The assignments can only be marked if submitted through Blackboard. The assignment will be marked out of a total of 100 marks and forms 30 % of the total assessment for the course. Once marked, ALL assignments will be checked for plagiarism and/or collusion between individuals. Refer to your Course Outline or the Course Web site for a copy of the “Student Misconduct, Plagiarism and Collusion” guidelines.

  2. user image
    len2369421

    Note: Each student MUST make a second copy of the assignment and this copy MUST be produced within 24 hours of it being requested by the Course Co-ordinator. Failure to produce the second copy of the assignment when requested may result in loss of marks or a fail grade for the assignment. Requests for an extension to an assignment extension MUST be made prior to the date of submission and requests made on the day of submission or after the submission date will only be considered in exceptional circumstances. Please note you are expected to manage your workload so other assignments being due is not enough reason to be granted an extension.

  3. user image
    len2369421

    Assignment Requirements and Deliverables - The report is to be prepared as a single Microsoft Excel. Submission - The completed assignment is to be submitted by the due date and is to be an electronic submission of assignments please upload your Excel file. The file naming format should be lastName_FirstName_StudentNumber_ACC508_Assn2. The assignment will be assessed according to the marking sheet (Appendix A). Late submission of the assignment will result in a deduction of 10% of the available marks for each day that the assignment is late. This includes Saturday and Sunday. Assignment Return and Release of Grades - Where an assignment is undergoing investigation for alleged plagiarism or collusion the grade for the assignment and the assignment will be withheld until the investigation has concluded.

  4. user image
    len2369421

    Marking Sheet - Task 1 Development and presentation of Excel Spreadsheet and use of appropriate formulas. (5 Marks) Thorough – Extended: Uses appropriate, relevant, and correct formulas producing correct and nicely presented content. A skillful use of presentation tools to produce a professional level spreadsheet. All queries are complete and correct. Task 2 Development and presentation of Excel Spreadsheet and use of appropriate formulas. (15 Marks) Thorough – Extended: Uses appropriate, relevant, and correct formulas producing correct and nicely presented content. A skillful use of presentation tools to produce a professional level spreadsheet. All queries are complete and correct.

  5. user image
    len2369421

    Task 3 Development and presentation of Excel Spreadsheet and use of appropriate formulas. (20 Marks) Thorough – Extended: Uses appropriate, relevant, and correct formulas producing correct and nicely presented content. A skillful use of presentation tools to produce a professional level spreadsheet. All queries are complete and correct. Task 4 Development and presentation of Excel Spreadsheet and use of appropriate formulas. (20 Marks) Thorough – Extended: Uses appropriate, relevant, and correct formulas producing correct and nicely presented content. A skillful use of presentation tools to produce a professional level spreadsheet. All queries are complete and correct.

  6. user image
    len2369421

    Task 5 Development and presentation of Excel Spreadsheet and use of appropriate formulas. (20 Marks) Thorough – Extended: Uses appropriate, relevant, and correct formulas producing correct and nicely presented content. A skillful use of presentation tools to produce a professional level spreadsheet. All queries are complete and correct. Task 6 Development and presentation of Excel Spreadsheet and use of appropriate formulas. (20 Marks) Thorough – Extended: Uses appropriate, relevant, and correct formulas producing correct and nicely presented content. A skillful use of presentation tools to produce a professional level spreadsheet. All queries are complete and correct.



Ask Question & Get Answers from Experts
Browse some more (Other Subject) Materials
Is your personality more suited to a position in the front of the house, the back of the house, or the office Explain your answer. Describe three responsibilities associated
How will you prepare for your role as a consultant to hostage negotiators?What specific consultation will you provide to the negotiators?Who will be in charge of the negotiati
How do the study participants compare from study to study? How do the findings of each study compare and contrast with each other? What are some of the strengths and weaknesse
Describe a contemporary monument in your city or in another U.S. city and whether it holds some special significance for the citizens of the city where it was erected. State w
According to the YouTube video interview with Dr. Suzel Reily, speaking about her training and work as an Ethnomusicologist, what are the two major categories into which peo
Financial statements are prepared using generally accepted accounting principles (GAAP). What group is responsible for establishing these principles, and what goals guide thei
Despite the criticism leveled at Freud’s theory of the unconscious for being unmeasurable he is still considered to be the single most influential person in the field of psych
describing a situation in which you, as a member of a group, strongly disagreed with the actions of the group based on ethical grounds. Did you feel free to voice your opinion