City of Somerville, MA: Transaction Analysis (Financial Accounting)Learning objectivesCreate a pivot table in ExcelFormat a pivot tableApply filters to a pivot tableCreate sum, count, and average columns in a pivot tableSort a pivot tableCreate a pivot chart in ExcelAnalyze transactions using a pivot table and a pivot chartData Set BackgroundPaul Revere rode through the City of Somerville, Massachusetts, during his famous “Midnight Ride.” Its Prospect Hill was where the first Grand Union flag was raised under orders by General George Washington on January 1, 1776.Today, Somerville is a city with a population of almost 80,000 and is one of the most ethnically diverse cities in the country. It is located just two miles north of Boston and occupies just over 4 square miles.The City of Somerville, MA, posts its checkbook online (Links to an external site.) for public use. This data analytics activity uses the City of Somerville (Somerville) checkbook dataset for the years 2013 – 2016 and contains more than 55,000 records.Note: Even though the City of Somerville, MA, uses a fiscal year from July 1 through June 30, calendar years are used here so that the analysis does not get complicated from the conversion from fiscal years to calendar years in Excel pivot tables.Data DictionaryItem Number: This field is a sequential number assigned during the year. The year was added to the item number to create a unique identifier for each transaction.Category of Gov: This field indicates whether the transaction relates to Education, General Government, or Public Works, the three divisions of government for the City of Somerville.Vendor Name: This field contains the name of the entity related to the transaction.Amount: This field is the amount of the check.Check Date: This field is the date that the check was written.Department: This field is the department within the Category of Government related to this transaction.Check # : This field is the sequential check number.Org Description: This field gives additional detail about the specific organization within the general Department related to this transaction.Account Desc: This field provides additional detail about the purpose of the transaction.Item Class: This is the only fictitious field that was added to Somerville’s dataset. This field attempts to classify Somerville’s checkbook items into broad classes for ease of analysis and interpretation.RequirementsFor each of the following requirements, create a new pivot table in a new worksheet. Name each new worksheet as “Req 1,” “Req 2,” etc. Format the dollar amounts in each pivot table or pivot chart using the accounting format with zero decimal places. Format non-currency numbers in each pivot table or pivot chart using the accounting format with zero decimal places.From 2013 – 2016, what was the total spending in each of the four calendar years?In each of the years 2013 - 2016, how much was spent in each of the three categories of government (Education, General Government, and Public Works)?In 2016, which account (use the field “Account Desc” for this answer) was the largest in the General Government category?In 2016, who was Somerville’s largest vendor as measured by total dollars spent? How many separate payments did the city make to this vendor? What was the average amount of each payment to this vendor? Why did the city pay this vendor?How much in expenditures did Somerville have related to Property, Plant and Equipment (use the field “Item Class” for this answer) in the General Government category in each of the years 2013 – 2016? How much in expenditures in each of those years were related to repairs and maintenance?Prepare a pivot table that shows a line chart of Somerville’s expenditures in each of the three categories of government (Education, General Government, and Public Works) for the years 2013 – 2016. Prepare a pivot chart using the line chart type of this information on the same worksheet. Analyze the pivot chart and summarize the trends.

Writing Assignment #1Critical Annotated Bibliography of Five SourcesSummary of assignment· Task: In this assignment you will write a list of at least 5 sources in alphabetical order in APA format. In addition, each reference will be followed by a short analytical summaryof 150-200 words. At the end of the short summary, you will include a sentence or two that critically analyzes the source and that mentions distinctive features about the article and why it may or may not be useful for you in pursuing more research.● Length: Each annotation should be 150-200 words.● Format: APA● Sources: Five sources, all of which you fill find through UMGC’s OneSearcho All of your articles should be from scholarly journals or credible trade journals. They should not be from popular journals.Strategies in Selecting a Topic for your Annotated BibliographyYou may select a topic of your choice. However, when selecting a topic, keep in mind that it should be a topic on which a number of studies or scholarly articles have been published.Examples of topics students have chosen in the past or that would be workable for this assignment are the following:● The impact of the Internet on research methods of students and plagiarism in student papers● The impact of The No Child Left Behind (NCLB) Act on primary school education.● The impact of mobile devices on cybersecurityOverall, in considering a topic, you might consider your major, topics that will be covered in future classes you plan to take at UMGC, and your interests.For example, please examine the following ideas:MY TOPIC IS Attention Deficit Hyperactivity Disorder (ADHD) in college students and alcohol misuse.· If your major is criminal justice, you might consider researching the different types of CSI effects that have been explored in the literature or researching what studies have noted about body cameras for law enforcement officials.· If your major is psychology, you might consider researching counseling strategies, consumer behavior in online shopping, decision-making in upper-management positions, etc.· If your major is history, you might consider researching theories on the Civil War, studies on labor patterns in the United States, an aspect of military history, etc.· If your major is social science, you might consider researching community responses to health care threats,co-parenting patterns after divorce, etc.· If your major is cybersecurity, you might consider researching different studies that analyze the training of employees on cyber issues or researching the Agile and Waterfall methods of project development.Whatever topic you consider, keep in mind that it should be a topic on which a number of studies or scholarly articles have been published. You will conduct all or most of your research using UMGC’s OneSearch function in finding scholarly articles or articles from credible trade journals.ApproachThis assignment will be an analytical/critical/ annotated bibliography. The difference between the descriptive/informative annotated bibliography and the analytical/critical annotated bibliography is explained in the following video:https://sites.umgc.edu/library/libhow/bibliography...The transcript is at:https://sites.umgc.edu/library/libhow/bibliography...Moreover, the following website from UMGC’s Information and Library Services provides more information on how to complete this assignmenthttp://sites.umgc.edu/library/libhow/bibliography_...Submitting the assignment:You will submit a first draft of the essay to the assignment folder. The first draft will not be graded. The instructor will provide comments to it.After receiving comments from the instructor, you will submit a revised draft. The final draftwill be graded.Helpful Resources and WebsitesThe following are some links to sample Annotated Bibliographies. Some of them give examples in APA format. You may want to observe these examples and perhaps model your work after them.· A variety of samples from The University of North Carolina Writing Center, including these specific examples of APA-style annotations. You can find that at the following page: https://writingcenter.unc.edu/tips-and-tools/citin...· Some instruction from Cornell University. The website also provides more informationon the process of writing an annotated bibliography. You can find that at the following page: https://guides.library.cornell.edu/annotatedbiblio...· Some instruction from East Carolina University. You can find it at the following page: https://libguides.ecu.edu/c.php?g=17262&p=98192