ACCY6501_10 Merger Project Memo Writing Assignment Help

(/0x4*br />

MERGER PROJECT MEMO

MEMORANDUM FOR: Young & Jolly, LLC Merger Project Consultants

FROM: Smart-Buy

SUBJECT: Merger Project Parameters

Now that the contracts are signed, we are providing more detailed guidance for the team that will help
us with our Merger Project. This memo addresses the following topics:
• Background Information
• Specific Files Provided
• Deliverables
BACKGROUND INFORMATION
Recently, Largemart, Incorporated and Bullseye, Incorporated, two large, regional retailers, went
through a merger. The newly formed company operates under the name Smart-Buy, Incorporated.
Smart-Buy, Inc. is a large retailer with $11.5B USD in annual sales and 80,000+ employees. Smart-Buy is
in the process of merging its operations,
Currently, Smart-Buy has retail stores on the West Coast, as well as in Plain and Southern states, but
intends to expand into other regions. The company is in the process of determining where its new
headquarters will be, so Smart-Buy executives are splitting their time between Largemart’s and
Bullseye’s headquarters in California and Oklahoma, respectively. The combined company has retails
stores, transportation and distribution centers, as well as a few manufacturing plants.
There were many factors weighed by the company executives and board of directors prior to merging
the two companies. One company had distribution centers and manufacturing plants that could prove
to be advantageous to the combined company since there would be no need to ramp up new facilities
or create facilities from green fields. And, the fact that Largemart already have retail stores in
California, meant that the newly formed company would have a Human Resource and Legal
Department already well versed in the strict workplace laws and regulations used in California.
Additionally, with a significantly larger workforce and store locations, the executives saw that the new
combined revenue and synergies between the two companies would be a powerful force to take into
the marketplace.
Smart-Buy’s workforce has full-time, part-time, and seasonal workers. During the holiday sales
period, their levels generally peak since this is the time of year that Smart-Buy generates their highest
sales.
Smart-Buy is in the process of changing its store banners (e.g. Largemart store and Bullseye store
signage and related company logos) and implementing SAP as their enterprise resource planning (ERP)
system. It has taken a considerable longer time to implement the new system as Smart-Buy works
2
through taking on new best practices as it relates to their business processes and upgrading their
technical infrastructure to support the larger company. In the meantime, financials, including payroll,
are still being done in separate legacy systems. While this is cumbersome, Smart-Buy’s Chief Information
Officer (CIO), thinks that it was more strategic to get the new ERP system implemented as quickly as
possible, than to have his limited information technology (IT) teams concurrently working on an interim
solution that will be thrown away once the new ERP system was fully functional. As a result, each
quarter and annually, there is a manual process of combining financials and reporting. This manual
process has caused earning restatements and delays in providing financial information to executives.
We chose Young & Jolly (Y&J), LLC, a Big 6 Accounting Firm to help expedite payroll reporting. It is
critical that Y&J completes this task as expeditiously as possible, especially taking into account all of the
recent employment regulation changes. As Y&J Consultants, your team has been asked to tackle this
assignment. The Managing Partner of the Oklahoma City practice specifically asked for your team to
work on this effort because of the outstanding results she saw on your last assignment and because she
knows that you do not take shortcuts or wait until the last minute to get your work done. The
Managing Partner also gave your team specific instructions to get started on this assignment now
because of the large amount of data.
“This is more data than we typically work with,” she said. “This is Big Data!”
SPECIFIC FILES PROVIDED
Smart-Buy has provided you with employee files. The employee files consist of demographic
information. Each company provided files from separate legacy systems. In addition, Smart-Buy has
provided you with quarterly payroll files from fiscal year 2016 for each of the two companies.
Specifically, you should have the following files for each company:
Addresses
This file contains address information sorted by employee id. Both large-mart and Bulls-Eye
will provide these files to Young&Jolly Inc. Any queries that need to understand the location
of the employee by city, state or zipcode, should use this table as all employees have an
address record. These files will need to be merged together.
Calendar
This file contains information about the calendar utilized to calculate payroll. Pay checks are
cut for hourly employees on the 1st and the 15th of each month. This means that the number
of hours available to work in any given pay period must be calculated based on the number
of week days in the pay period. The calendar file will provide the number of hours available
for an employee to work in a pay period. Both companies will provide this file. If the files are
exact matches, only one of these files are required for use in your new database. This file
will be useful for limiting the results by period, month or quarter.
Departments
The departments at both companies will have the same names; however, not both of the
companies have all departments. For reporting and data integrity purposes, please merge
3
the department files provided.
Employee Information
Employee Files are provided by each company and we would like one Employee file as part
of the merger. Not all employees are assigned to a store as those in support jobs, like
human resources and accounting do not work for just one store. This means that there are
nulls in the data for Store_ID for many employees. For querying anything that has to do
with an employee city or state, the address table should be used. The store information is
useful for understanding how many employees are assigned to a store, etc.
Payroll Register
Payroll information has been downloaded quarterly for each company. This means that
Large-Mart has 5 Payroll files and Bulls-Eye has 5 Payroll files that need to be combined into
one large payroll file (this may exceed 1,000,000) records.
Stores
This file includes information about the stores, including the address of the store and the
number of customers in the store’s immediate area (estimated population). The store address
does not reflect where employees are located.
Revenue-Stores
This file includes information about the Revenue brought in by each store. The Revenue is
reported using the same calendar that is used to calculate payroll.
Smart-Buy provided this picture of how the tables are related:
4
DELIVERABLES
The Merger project is split into two parts. The first part of the project is to extract, translate and load the
data into a database. We refer to that as the ETL Deliverable. For the second part, we want you to
analyze and visualize the data. We refer to that as the Analyze and Visualize Deliverable
ETL Deliverable
For the first part, we want to understand how you create the database against which you will run you
analysis. All the analysis should be run from the same database. So, we ask that you create a database
and write a process memo to explain what you have done so that we can ensure the integrity of our
data through the Extraction, Translation, and Load Process. We have extracted the data, so no write up
of the extraction process is necessary. Your memo should have the following sections:
1) Process
In this section we want you to write the steps of creating your database in enough detail
that should we need to recreate your work in an audit we are capable of such. A picture of
your final tables is required.
2) Translation
In this section you will outline any formatting or changes to the data that occurred.
3) Load
In this section you will outline the final disposition of the database. This includes screen
shots of sample data for each table, and total record counts against your tables. Start by
describing the final structure of your database. Tell your audience what tables you now
have in the database. A screen shot of the access screen(s) to support this is required.
Analyze and Visualize Deliverable
For each question, please tell your client how you arrived at the answer. This will help any future
consultant figure out exactly what you did if they need to replicate it. This can be a couple of sentences
describing any software utilized (Access, Excel, IDEA, Alteryx, Tableau, or SQL) and a detailed
description of any steps taken within that software (combination of multiple software or solutions is
acceptable).
• For questions that you are answering with only a query, please copy the formulas, SQL, etc.
(screen shot) and the results (screen shot) and explain what you are showing.
• For questions that you are answering with a query that you a have connected to an Excel
spreadsheet, include the query, a screen shot of one page of the results, and then show what
you have done in excel in terms of your pivot table.
• A screen shot of the Excel screen will do this. However, think about trying to explain this to a
reader. There should be some summary verbage to support all these pictures. We want this
process to be as repeatable as process should we want to use your queries and processes
against future data.
5
• Graphs – If the answer is one number, no graph is needed. If the answer is a group of numbers,
please include a graph. We have specified where a visualization is required, but any additional
visualization to help explain your conclusions are welcome. Remember, the visualizations
should add to understanding, so if one number is an outlier, please exclude and footnote so that
the graph makes more sense.
QUESTIONS TO BE ADDRESSED BY ANALYSIS
1. Smart-Buy wants to understand how many employees we now have by department? Visualize
2. How many employees were originally Largemart or Bullseye employees? Visualize
3. How many employees were originally Largemart or Bullseye employees by state? Visualize
4. Are there any departments that end up being entirely staffed by Largemart or Bullseye? Management thinks
there are a few departments that might be fully staffed by Largemart. Please explain.
5. Which departments in which states had the highest total payroll (gross_pay) costs in 2018?
List only the top 10.
6. What quarter cost the state of Oklahoma the most in gross pay costs?
7. Which department had the highest gross payroll in 2018 across all states in the company? What
percentage of the gross payroll did that department represent? Visualize
8. We would like to understand by department whether there is a significant pay difference between men and
women in 2018 (average gross salary by gender per pay period). Visualize
9. Smart-Buy wants to understand the average gross salary by department in the 4/15/2018 period.
10. The cost of living is higher in California than in other states. Smart-Buy would like to understand if
California has a disproportionate number of people making more than $125,000. How many
employees in Oklahoma and California made over $125000 in 2018?
11. They want to know how much federal income tax Smart-Buy paid on behalf of its hourly and
seasonal employees in retail in December of 2018.
12. They would like to understand the amount of state income tax paid by state by quarter. Visualize
13. Where should Smart-Buy’s new headquarters be and why should it be located there? Visualize
Recognize that for this question, you could answer this in a variety of ways. You could take into
account the tax jurisdictions, you should take into account the departments, and the number of
people in the departments. You could gather information about the zipcodes where the
departments/people are located and base an argument around that. There is no one right answer
to this question. Creativity and clarity will be rewarded. Solid analysis of the information that you
have (the database) should be used to come up with your conclusions. Write a summary and
provide graphs to support your answer for this question. Good analysis requires at least a couple of
queries.
6
14. Is the social security tax calculated correctly for the 10 highest paid employees? Do these
employees making more than the upper limit have tax withheld correctly?
15. What is the total store revenue per state?
16. Which store had the most employees? Please include the city and state.
17. Did more men or women work in January 2018 and from which company did these employees
(that worked in January) Originate?
18. List the employees at SmartBuy that average more than 3000 per month? Limit the results to top
10 highest average paid records. Show the employee ID.
19. What is the annual net pay of the top 10 highest paid salaried male executives? Report their
last name and role.
20. Report the top 10 employees that worked overtime in the first quarter of 2018. Report the
employee ID.