Reference no: EM131142062
Complete 2 challenges using a fresh excel file. answer all questions with an explanation.
Gievn the Case study of "Skinny Joe's Coffee House".
CHALLENGE 1:
For this assignment you are required to analyze the point of sales (POS) records fora café, and determine various business metrics based on the creation of an analysis model. Once you have created a model and prepared your findings, you will write a brief business memo to present your findings and make recommendations to the proprietor of Skinny Joe's Café.
Assignment Structure & Format
You should submit two files for this assignment:
1. A business memo that presents your findings and makes recommendations (in PDF format).
2. A summary page from your Excel model that presents the facts and conclusions found in your model in an Excel document
The business memo should be a maximum of 2 pages in length, using no smaller than a 10 point font with minimum line spacing of 1.5.
Include the questions from the assignment, and feel free to include content from Excel model. Remember, as in business, presentation matters so please pay attention to formatting and aesthetics with memo and summary page.
There is an infinite number of ways you can layout the data for this assignment and model for your solutions, but there are a finite set of correct answers.
Instructions
Download the Skinny Joes Case Study file, and POS data files (2) for this assignment. After reading the case study, perform the following tasks:
1. Load two text files from Kirby's POS program into an Excel spreadsheet using the appropriate features and functions (you will need to open and save both files on your local computer before importing). The layout of the files is included in the case study.
2. Create Excel functions and formulas that answer the questions posed below.
3. Consolidate your findings from the formula and functions into a summary page (a simple summary of the most important data you've used/found) in the Excel model, formatting it as you've learned through the various lessons.
4. Answer the questions posed below in a maximum 2 page business memo that you will "present" to Kirby as your findings from this consulting assignment.
5. Save the memo in a PDF format, and submit both the memo and summary document.
Questions
Answer the following questions within the body of the Business memo:
1. By POS and as a summary for the café, what are Kirby's:
a) Daily total sales, most expensive sale, least expensive sale and the value of the average sale for each of the thirty days we have data for?
b) Daily total transactions and busiest hour / day?
c) What was Kirby's busiest day (based on number of transactions) during this period? What was his least busy day?
d) What are the monthly total sales?
2. Using the item cost data found in the case appendices, by POS and as a summary for the café, what are Kirby's:
a) Daily & monthly cost of goods sold (COGS) and profitability?
b) What are his most and least profitable offerings? How many times each day & month are these items sold?
3. Do you see any patterns in these findings that indicate ways Kirby can maximize his sales and minimize his costs?1. Save the ‘
CHALLENGE 2
1. Save the STATS file as a text file.
2. Import the CHALLENGE 2 STATS file into Excel, using delimited importing (with tab as a delimiter).
3. Use ‘Green-Yellow-Red' Color Scale Conditional Formatting on the Order Quantity (Shelves) column.
4. Add Filters to the spreadsheet (remember to make sure you select a cell within the data when you format), and only display Order Quantity (Chairs) values of 1, 2, 3, 4.
5. Use Sort, to sort all data by the values in ‘Store Location' by A to Z.
6. Freeze Panes, and ensure that the top row is visible when scrolling through the rest of the document.
Attachment:- CHALLENGE 1 CASE.rar
Attachment:- CHALLENGE 2 STATS.rar