Description
In this project, you will demonstrate your mastery of the following competency:
Use spreadsheet applications to interpret information and draw conclusions
Scenario
You work at All-Mart, a large general store that sells everything from groceries and office supplies to over-the-counter medicine, toys, and books. Unfortunately, the company’s profits have been down for the last six months because of competition, specifically from other local businesses and online vendors. Jill Oliviera, the general manager, has decided to take action by rethinking the store’s business strategies. One of these strategies is to focus the business’s efforts on just a few departments and specialize in those areas rather than continuing to support all of the current offerings.
Jill Oliviera has decided on a drastic change and announced that the company will offer goods from just three departments going forward. All-Mart will greatly expand and promote these three departments and gradually phase out the rest. To pick which three, she thought it would be best to consult with her shift managers. To her surprise, they had vastly differing opinions on what these departments should be. Their observations differed depending on what time they worked and whether they were talking about total revenue or profit margin.
Because of this, you’ve been asked to analyze the company data so she can make an informed decision. The store’s point-of-sale system can generate a sales report by department—but frankly, it’s a total mess. Your general manager has asked you to make the report from the last two weeks more presentable and provide recommendations based on your findings so she can make a decision.
Directions
Part 1: Reformatted End of Day Report
Review the End of Day Report spreadsheet in the Deliverables section and consider best practices from the learning resources. How can you make the sheet easier to read?
Use effective formatting and spacing to display the numerical data in a readable manner. Specifically, use the following:
Row and column spacing
Row and column alignments
Cell formatting
A thumbnail of the End of Day Report spreadsheet. The full document is available in the Deliverables section of this project.
Create a new worksheet in the same document with some aggregated calculations: These calculations combine data from several different places. At minimum, this should include the average sales for each department and the average cost of purchasing these goods (CoG). Make sure to look at both the average for the week and the average on a given day in the week (Monday, Tuesday, etc.).
Calculate these values using appropriate formulas in the spreadsheet. Do not perform these calculations yourself (on paper, with a calculator, etc.). Use the following formulas and functions instead:
SUM
AVERAGE
PRODUCT or multiplication
QUOTIENT or division
COUNTIF or COUNTA
IF
Use charts and/or graphs to accurately display the financial data, as this will help your general manager make sense of the data. Select at least two of the following to represent graphically in the spreadsheet:
A bar chart that shows the average daily revenue for each department
A pie chart that shows the average daily gross profit for each department
A pie chart that shows the average daily profit margin for each department
A bar chart that shows the average profit per day in each department
A line chart that shows sales in each department for each day listed in the report
Part 2: Written Recommendation
Finally, provide your general manager with a short (250- to 300-word) written recommendation for the top three departments the store should keep, supported by specific calculations and data from your spreadsheet.
When making your recommendations, consider which departments bring in the most revenue, as well as those that have the highest profit margins.
Keep in mind what will be most profitable for the company overall.
Deliverables
Every project has a deliverable or deliverables, which are the files that must be submitted before your project can be assessed. For this project, you must submit the following:
Reformatted End of Day Report
Download this sales report and use it as the basis for your reformatted spreadsheet. Make sure to convert it into an XLS or XLSX file before starting your work. This reformatted spreadsheet should include a second worksheet with aggregate data, charts, and graphs.
Written Recommendation
Write a 250- to 300-word recommendation for the top three departments the store should keep.
Project Rubric
Reformatted End of Day Report
Use effective formatting and spacing to display numerical data in a readable manner
This includes row and column spacing, row and column alignments, and cell formatting.
~Calculates values using appropriate formulas in a spreadsheet
This includes SUM, AVERAGE, PRODUCT or multiplication, QUOTIENT or division, COUNTIF or COUNTA, and IF.
~Uses multiple charts and/or graphs to accurately display financial data
Written Recommendation
~Makes recommendations supported by specific calculations and data
General
~Articulation of Response: Clearly conveys meaning with correct grammar, sentence structure, and spelling, demonstrating an understanding of audience and purpose
~Citations/Attributions: Attributes sources where applicable using citation methods with no major errors