Project Overview For this project, you will complete anend of day reportand awritten...

Question

Project Overview For this project, you will complete an end of day report and a written recommendation. Using spreadsheets and data to analyze a company’s profit is essential to keeping a business...

Project Overview

For this project, you will complete anend of day reportand awritten recommendation.Using spreadsheets and data to analyze a company’s profit is essential to keeping a business thriving. The effective use of spreadsheets can help keep sales, purchasing, and profit data organized and accurate. Using spreadsheet applications to account for a company’s information is an essential organizational skill.

Competency

In this project, you will master the following competency:

Use spreadsheet applications to interpret information and draw conclusions

Scenario

You work at All-Store, a large general store. All-Store sells everything from groceries and office supplies to medicine, toys, and books. Unfortunately, the company’s profits have been down for the last six months. This is likely because of competition from other local businesses and online vendors.

Jill Oliviera, All-Store’s general manager, has decided to rethink the store’s business strategies. One of these strategies is to focus the business’s efforts on just a few departments. This way, the store can specialize in those areas rather than continuing to support all of its current offerings.

Jill Oliviera has announced that the company will offer goods from just three departments going forward. All-Store will greatly expand and promote these three departments and gradually get rid of the rest.

To pick which three departments will stay, she thought it would be best to consult with her shift managers. To her surprise, they had very different opinions on what the three 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, Jill has asked you 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, the report is a total mess. Jill needs you to make the report from the last two weeks more presentable. Then you should use your findings to give a recommendation that will help her make a decision.

Directions

Part 1: End of Day Report

a. After reviewing the learning resources,look at the
End of Day Reportspreadsheet and consider what the best practices are for reformatting the report. How can you make the spreadsheet more informative and easier to read?

b.Use effective formatting and spacingto make the data look more readable. Specifically, you should use the following:

1. Row and column spacing

2. Row and column alignments

3. Cell formatting

A thumbnail of the End of Day Report spreadsheet. The full document is available in the Deliverables section of this project.

c.Create a new worksheetin the same spreadsheet. Include the aggregated calculations: these are calculations that combine data from several different places.

Your new worksheet should look at both the weekly average and the average for a single day (Monday, Tuesday, etc.) for each of the following areas:

1. The average sales for each department

2. The average cost of purchasing (CoG) these goods for each department

Use the appropriate formulas in the spreadsheet. You don’t need to do these calculations yourself (on paper or with a calculator). Instead, use these formulas and functions:

1. SUM

2. AVERAGE

3. PRODUCT or multiplication

4. QUOTIENT or division

5. COUNTIF or COUNTA

6. IF

d.Use charts and graphsto accurately display the financial data. This will help your manager quickly make sense of the data. For each display, be sure to create an appropriate title and to label all data directly on your display so that any reader will be able to understand the information you are sharing. Choose two of these items to represent graphically:

1. A bar chart that shows the average daily revenue (in dollars) for each department

2. A pie chart that shows the average daily gross profit (in dollars) for each department

3. A pie chart that shows the average daily profit margin (in percentages) for each department

4. A bar chart that shows the average profit per day (in dollars) in each department

5. A line chart that shows sales (in dollars) in each department for each day listed in the report

Part 2: Written Recommendation

Finally, send your general manager a short (200–300 words) written recommendation. Explain which top three departments the store should keep. Be sure to support your suggestions with specific calculations and data from your spreadsheet.When making your recommendations:

Think about which departments bring in the most revenue. You should also consider the departments with the highest profit margins.

Keep in mind what will be most profitable for the company overall.

What to Submit

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:

1.

End of Day Report

Download this sales report and use it as the basis for your reformatted spreadsheet. Make sure that this is an XLS or XLSX file before starting your work. This spreadsheet should include a second worksheet with aggregate data, charts, and graphs.

2.
Written Recommendation

Write a recommendation (200–300 words) for the top three departments the store should keep.

instructions2-reviewfeedback-rd4gu0qt.docx endofdayreport-ltq4hx20.docx mat20014endofdayreportf-r5n0bc4c.xlsx

Answered 13 days AfterMar 16, 2021

Tanmoy · Accepted Answer

Sheet1
		Week 1																						Week 2
		Departments	Mon 6/16/2020			Tue 6/17/2020			Wed 6/18/2020			Thur 6/19/2020			Fri 6/20/2020			Sat 6/21/2020			Sun 6/22/2020			Mon 6/23/2020			Tue 6/24/2020			Wed 6/25/2020			Thur 6/26/2020			Fri 6/27/2020			Sat 6/28/2020			Sun 6/29/2020
			SALES	COG	Profit	SALES	COG	Profit	SALES	COG	Profit	SALES	COG	Profit	SALES	COG	Profit	SALES	COG	Profit	SALES	COG	Profit	SALES	COG	Profit	SALES	COG	Profit	SALES	COG	Profit	SALES	COG	Profit	SALES	COG	Profit	SALES	COG	Profit	SALES	COG	Profit	Average Daily Profits
		ELECTRONICS	$732.36	$256.33	$476.03	$791.24	$276.94	$514.30	$716.02	$250.61	$465.41	$859.52	$300.83	$558.69	$804.72	$281.65	$523.07	$1,823.56	$638.25	$1,185.31	$2,168.72	$759.05	$1,409.67	$803.85	$281.35	$522.50	$697.58	$244.15	$453.43	$874.55	$306.09	$568.46	$787.38	$275.58	$511.80	$786.89	$275.41	$511.48	$1,866.82	$653.39	$1,213.43	$1,695.33	$593.37	$1,101.96	$715.40
		GROCERY	$10,571.07	$7,716.88	$2,854.19	$10,971.26	$8,009.02	$2,962.24	$11,575.71	$8,450.27	$3,125.44	$11,859.49	$8,657.43	$3,202.06	$12,112.78	$8,842.33	$3,270.45	$15,531.24	$11,337.80	$4,193.44	$16,950.57	$12,373.92	$4,576.65	$11,085.97	$8,092.76	$2,993.21	$9,321.31	$6,804.55	$2,516.76	$11,729.01	$8,562.18	$3,166.83	$10,324.98	$7,537.24	$2,787.74	$12,247.84	$8,940.93	$3,306.91	$15,150.87	$11,060.13	$4,090.74	$13,782.71	$10,061.37	$3,721.34	$3,340.57
		HOME	$938.56	$375.42	$563.14	$894.35	$357.74	$536.61	$1,011.98	$404.79	$607.19	$965.00	$386.00	$579.00	$1,085.50	$434.20	$651.30	$1,360.20	$544.08	$816.12	$1,283.04	$513.21	$769.83	$886.68	$354.67	$532.01	$776.72	$310.69	$466.03	$1,035.43	$414.17	$621.26	$889.47	$355.79	$533.68	$1,066.96	$426.78	$640.18	$1,153.32	$461.33	$691.99	$1,198.06	$479.22	$718.84	$623.37
		OFFICE	$1,592.40	$796.20	$796.20	$1,567.49	$783.75	$783.74	$1,675.17	$837.59	$837.58	$1,692.52	$846.26	$846.26	$1,778.26	$889.13	$889.13	$1,305.60	$652.80	$652.80	$1,302.17	$651.09	$651.08	$1,636.31	$818.15	$818.16	$1,349.35	$674.68	$674.67	$1,717.42	$858.71	$858.71	$1,569.10	$784.55	$784.55	$1,686.09	$843.04	$843.05	$1,322.96	$661.48	$661.48	$1,086.88	$543.44	$543.44	$760.06
		PHARMACY	$1,934.70	$1,267.23	$667.47	$1,910.07	$1,251.09	$658.98	$1,951.81	$1,278.44	$673.37	$2,044.25	$1,338.99	$705.26	$2,047.99	$1,341.43	$706.56	$1,329.44	$870.78	$458.66	$1,466.51	$960.56	$505.95	$1,870.21	$1,224.99	$645.22	$1,700.41	$1,113.77	$586.64	$2,085.98	$1,366.32	$719.66	$1,861.17	$1,219.06	$642.11	$2,159.13	$1,414.23	$744.90	$1,176.24	$770.44	$405.80	$1,017.22	$666.28	$350.94	$605.11
		TOYS	$878.08	$482.94	$395.14	$963.28	$529.80	$433.48	$920.51	$506.28	$414.23	$925.96	$509.28	$416.68	$967.00	$531.85	$435.15	$1,959.95	$1,077.97	$881.98	$2,221.64	$1,221.90	$999.74	$909.33	$500.13	$409.20	$790.75	$434.91	$355.84	$998.53	$549.19	$449.34	$858.87	$472.38	$386.49	$1,058.26	$582.04	$476.22	$1,915.55	$1,053.55	$862.00	$1,669.67	$918.32	$751.35	$547.63
		BOOKS	$312.90	$204.95	$107.95	$286.86	$187.89	$98.97	$214.50	$140.50	$74.00	$348.40	$228.20	$120.20	$198.40	$129.95	$68.45	$339.92	$222.65	$117.27	$449.19	$294.22	$154.97	$204.01	$133.62	$70.39	$248.00	$162.44	$85.56	$228.32	$149.55	$78.77	$273.63	$179.23	$94.40	$328.30	$215.04	$113.26	$407.66	$267.02	$140.64	$229.95	$150.62	$79.33	$100.30
		Total	$16,960.07	$11,099.95	$5,860.12	$17,384.55	$11,396.23	$5,988.32	$18,065.70	$11,868.48	$6,197.22	$18,695.14	$12,266.99	$6,428.15	$18,994.65	$12,450.54	$6,544.11	$23,649.91	$15,344.33	$8,305.58	$25,841.84	$16,773.95	$9,067.89	$17,396.36	$11,405.67	$5,990.69	$14,884.12	$9,745.19	$5,138.93	$18,669.24	$12,206.21	$6,463.03	$16,564.60	$10,823.83	$5,740.77	$19,333.47	$12,697.47	$6,636.00	$22,993.42	$14,927.34	$8,066.08	$20,679.82	$13,412.62	$7,267.20	$6,692.44
		Departments	Average
			Sales	COGS	Gross Profits	Profit Margin
		ELECTRONICS	$1,100.61	$385.21	$715.40	10.69%
		GROCERY	$12,372.49	$9,031.92	$3,340.57	49.92%
		HOME	$1,038.95	$415.58	$623.37	9.31%
		OFFICE	$1,520.12	$760.06	$760.06	11.36%
		PHARMACY	$1,753.94	$1,148.83	$605.11	9.04%
		TOYS	$1,216.96	$669.32	$547.63	8.18%
		BOOKS	$290.72	$190.42	$100.30	1.50%
		Total	$19,293.78	$12,601.34	$6,692.44	100.

Project Overview For this project, you will complete an end of day report and a written recommendation. Using spreadsheets and data to analyze a company’s profit is essential to keeping a business...

Answer To: Project Overview For this project, you will complete an end of day report and a written...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment