Bridgewater
State University
Joseph M. D’Adamo
ACFI
470/570 Financial Information Systems and Control
2023 –
2024
Excel
Assignment #1
Page 1 of
4
You will use the Excel file provided to complete exercises using filtering, sorting, and graphing.
My interest is for you to learn the skills required to complete these exercises.
Requirements:
1) On the Problem #1 Data worksheet, you will find a data set that contains a list of unit sales of Rosebud Distributing Company’s five products by its five sales representatives in three markets during the year ending December 31, 2023.
In this assignment, you will use the filter function to answer four questions. The questions are presented on the Problem #1 Questions & Support worksheet.
For each question:
a) Filter the data on the Problem #1 Data worksheet as required by the question.
b) Copy the filtered data into the applicable area on Problem #1 Questions & Support worksheet.
c) Use the =sum and =count functions in the applicable boxes provided to answer the questions presented.
d) Use the data sort function to answer part of the fourth question.
2) Use the data provided for Mondelez International Inc. on the Problem #2 worksheet to prepare five
charts: (5 points per graph = 30 points)
• Stacked column graph of the total amounts of Net Revenues by Segment over the five-year period
• Stacked column graph of the total amounts of Operating Income by Segment over the five-year period
• Line graph of Total Net Revenues and Total Operating Income over the five-year period
• Pie graph that presents the relative composition of Net Revenue by segment for 2022
• Pie graph that presents the relative composition of Operating Income by segment for 2022
You must include appropriate headings and proper labels/legends for your graphs. (5 points)
3) On the Problem #3 worksheet, you will complete a problem from. The worksheet includes a list of 200 invoices from sales to various customers that are past due.
The data dictionary for the dataset in the table:
Customer Field: Name of the customer
Invoice Amount Field: The invoice amount owed
Due Date Field: Date the invoice was due
Brewer Sales Corp. maintains a dataset of past due invoices. Once an invoice reaches 180 days past due, it is sent to a collection agency and remove from the past due accounts dataset.
In the problem, you need to create a pivot table to age the past due receivables into six categories:
1 -30 days past due
31 – 60 days past due
61 – 90 days past due
91 – 120 days past due
121 – 150 days past due
151 – 180 days past due
Step #1: (5 points)
Assume the current date is 12/31/2023 as is provided in cell A3. In column D, create a formula to compute the number of days that the first invoice is past due. Your formula must be created using only cell references.
Use an absolute cell reference to “freeze” the reference to the current date cell.
Copy your formula down for the other invoices.
Step #2:
Turn the data set into a sortable table.
1) Click into any cell within the dataset.
2) Click on Insert then Table. You may access the option by typing <Ctrl>T or <Command>T. Select “OK”.
You should see the following:
Step #3:
Follow the following instructions to start a pivot table:
1) Click on the Table Design menu option.
2) Select “Summarize with Pivot Tables” option.
You know have a pivot table that can be summarized to achieve our desired goal of preparing an aging summary.
3) Drag the “Days Past Due” field name down to the “Rows” box.
4) Drag the “Invoice Amount” field name down to the “Σ Values” box.
The pivot table fields should look like:
Step #4: (25 points)
Create an aging schedule of past due accounts by using the grouping option.
1) Click into any cell of the pivot table.
2) Right click on the “Group” selection.
3) Enter “1” in the “Starting at:” box.
4) Enter “180” in the “Ending at:” box.
5) Enter “30” in the “By:” box.
You now have a pivot table that summarizes the invoices into the desired aging categories.
Step #5: (5 points)
Properly format the aging schedule by changing the column headers to appropriate titles and using proper accounting formatting for the amounts ($ formatting at the top and bottom of the column, comma formatting for the amounts in between).
Step #6: (5 points)
Create a report of the list the invoices that are past due by 151 to 180 days past due range.
1) Double Click the cell that contains the total dollar amount of the invoices that are in the 150 to 180 days past due range to create a pivot table that summarizes the amounts 151 to 180 days past due range by Customer Name.
3) Properly format the new schedule.
Submissions:
You will submit your work for grading on Blackboard. Please include your name in the file name so I may distinguish each student’s work as I grade it.
