MERGER PROJECT MEMO
DATE: MEMORANDUM FOR: FROM:
Young & Jolly, LLC
Merger Project Consultants Smart-Buy
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
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:
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.
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.
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
the department files provided.
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 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.
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.
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:
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
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:
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.
In this section you will outline any formatting or changes to the data that occurred.
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.
• 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
- Smart-Buy wants to understand how many employees we now have by department? Visualize
- How many employees were originally Largemart or Bullseye employees? Visualize
- How many employees were originally Largemart or Bullseye employees by state? Visualize
- 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.
Which departments in which states had the highest total payroll (gross_pay) costs in 2018?
List only the top 10.
What quarter cost the state of Oklahoma the most in gross pay costs?
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
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
Smart-Buy wants to understand the average gross salary by department in the 4/15/2018 period.
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 in2018?
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.
They would like to understand the amount of state income tax paid by state by quarter. Visualize
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.
Recently Asked Questions
- Webster Company produces 25,000 units of product A, 15,000 units of product B, and 17,500 units of product C from the same manufacturing process at a cost of
- MMP Inc. exchanged 500 common shares of Penguins Inc., which MMP Inc. was holding as an investment, for new equipment from Easter Sales. The Penguins Inc.
- Manitoba Ltd. purchased a tract of land for $765,000, which included a warehouse and office building. The following data were collected concerning the