Springfield-Green-Part1

docx

School

Indiana University, Bloomington *

*We aren’t endorsed by this school

Course

K201

Subject

Business

Date

Feb 20, 2024

Type

docx

Pages

Uploaded by BaronTeamDog38

SPRINGFIELD BEANS - GOING GREEN K303 CAPSTONE CASE CASE OVERVIEW For a while now, you have been working with Springfield Beans, a gourmet coffee shop located in a Midwestern college town. The shop opened in June of 2013 and has been steadily growing their client base, as well as expanding their product offerings. They offer a wide variety of specialty coffee and espresso-based drinks. They also offer a variety of non-coffee drinks, such as teas and smoothies. They have partnered with several local bakeries and sandwich shops to offer food items that complement their beverage options. The original location offers the convenience of off-street parking, free wi-fi, and comfortable seating, which makes the shop a popular spot to work or study. In April of 2017, they opened a second location. This location primarily serves as a coffee roasting facility, but also has a small retail space, providing the downtown community with a limited menu of food and beverages for carry-out only. Springfield realizes that as their business has grown, so has the amount of waste they generate in the form of disposable food and beverage containers. Taking inspiration from a coffee shop in Australia that has almost completely eliminated waste (see http://www.bbc.com/capital/story/20130722-erasing-coffee-shop-waste ), Springfield aims to improve their environmental impact without sacrificing quality or customer experience. They know that switching to compostable products, recyclable products, or products made from recycled materials will bring additional costs, risks, and benefits. Since coffee and other prepared beverages are their primary business, they would like to start by analyzing their use of disposable cups, lids, and straws, and compare the costs and impact of using suppliers who are producing more sustainable products. They have hired a team of outside business analysts (that’s you!) to help them make an informed, data-driven decision. YOUR TASK The case is presented in two parts. In Part 1, you will analyze existing transaction data and report on trends associated with Springfield Beans’ past usage of disposable products. See the Part 1 section of this document for detailed requirements. In Part 2, you will develop spreadsheet models to compare the outlooks of the two decisions Springfield is weighing. See the separate Part 2 document for detailed requirements. PART I: CURRENT SITUATION – VISUAL REPORT OVERVIEW 1 of 4

SPRINGFIELD BEANS - GOING GREEN K303 CAPSTONE CASE For Part 1, you will submit your work in the file named Going Green_K303_CasePart1.xlsm. This contains the same data that you used in Project 2 (with relationships and some calculations already present in the Data Model). It’s fine if your team would like to reorient yourself to the business landscape by looking back at the “Monthly Drink Sales” dashboard in your Project 2 file. Starting out this way will also allow everyone on the team to simultaneously engage in exploratory analysis before you turn to preparing your Part 1 deliverable. However, you must use the “...CasePart1.xlsm” file to structure, present, and explain your findings. Read this entire document for guidance, and refer back to it as you work toward your Part 1 report. STEP 1: PREPARE THE DATA The costs currently associated with Springfield’s disposable drinkware can be found on the Drinkware costs sheet in the Going Green_K303_Part 1.xlsm file. Note that hot drinks require a cup and lid, and cold drinks also include a straw. 1. Turn the data into an Excel table and name it. 2. Add the data to the Data Model. 3. Create a composite key field in both the trans details table and the newly imported drinkware costs table, using fields that both tables share. Suggested name for this new field: CostItemID (Hint: You did something very similar in Project 2 to be able to relate the price list and trans details tables. The drinkware costs table only has two fields that appear in the trans details table.) 4. Create a relationship between the two tables in the Data Model. STEP 2: EXPLANATORY ANALYSIS At minimum, your report should address the following questions. Use charts and tables as appropriate, and include text boxes that explain what you see in every one of the reporting objects you present. 1. How many cups were used of each type each year? 2. What size of cup is used most frequently? 3. Is the most common size different in hot drinks than cold drinks? 4. Is there any seasonality (meaning is there a time of year where the trends seem to change – months are a good option to examine here) for the most common sizes or types of cups? For example, are the larger size hot beverage cups used more in winter, and larger size cold drink cups used more in summer? 5. What is the annual cost of disposable cups, lids, and straws? Here are some suggestions for getting started:  Dig into the data—we recommend starting out with angles such as quantity and cost by size and type of item. 2 of 4

SPRINGFIELD BEANS - GOING GREEN K303 CAPSTONE CASE  Keep in mind that those angles might intersect in ways that are noteworthy. Pivot tables are a great, fast, easy way to explore what happens when you start out by looking at one field, and then break the results down by an additional field.  Also consider the time dimension. If we take the question of cost or quantity by type or size, or any other angle(s) you might want to consider —and also consider the possibility of trends over time, what shakes out?  Consider the situation described in the case overview and allow that to focus your efforts. STEP 3: REPORT STRUCTURE Main menu This serves as the starting point for your audience and provides links to easily navigate through the file. Explain the intended purpose of this report in the box at the top of the page. The menu has been started for you. Once you have finished your explanatory analysis and organized your workbook to support your story, add or update links to the menu to allow your user to easily read your report in the order you intend. Under the “Authors” heading, include the names and email addresses of all team members. Business outlook Take this worksheet as an example of how to tell a story by using reporting objects and written explanations. It makes the case that beverage sales are critical to Springfield, and shows the steady increase in use of disposable goods over time. Disposable items used (vol) Answer, at minimum, questions (1)-(5) here using charts and written explanations. Your reporting objects should be easy to interpret, and brief writeups should provide your insights. Remember, you are the ones doing the analysis. While your audience may arrive at their own conclusions, they have enlisted your team to bring your analytical talents to the problem that they face. Remember that the Business Outlook worksheet provides an example of presenting both reporting objects and a summary of key takeaways. Disposable items used (cost) Question (5) asks only for the total annual cost of disposable items. Answer that, in the interest of satisfying your client, but then take the cost analysis further. Provide two or three key observations about costs that may be useful to Springfield. Put yourselves in the shoes of the business owners. What else about costs might be of interest? As with the volume analysis, your cost-related reporting objects should be easy to interpret, and brief writeups should provide your insights. General guidance to consider: 3 of 4

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

SPRINGFIELD BEANS - GOING GREEN K303 CAPSTONE CASE  Interactivity features, such as slicers and form controls, are not required, but are permitted if they add value. However, your analysis should not rely heavily on the user engaging with interactivity features to draw conclusions.  Every reporting object must be accompanied by a written explanation of what you see in it.  Your workbook should be visually appealing and easy to navigate. Reporting objects and writeups should be clean, professional, and easy to understand and interpret. GRADING CRITERIA FOR PART 1 Your Part 1 report will be evaluated based on the criteria below: Criterion Description Overall Report The report as a whole demonstrates solid understanding of the business situation. Important and meaningful trends are clearly identified and interpreted, and their relevance to the decisions to be made by Springfield’s owners is made clear. Visual Analysis High quality reporting objects are provided at an appropriate level to elucidate trends. Formatting and written interpretations are skillfully used to guide the audience to an understanding of each reporting object’s main takeaways, anchored in a consideration of business value. Style: Writing and Reporting Objects The report demonstrates professional and consistent tone in writing and professional and consistent appearance of reporting objects and other information presented. No typographical, grammatical, or semantic errors present. Style: Ease of Use The workbook is thoughtfully organized to tell a meaningful story addressing the coffee shop’s questions. The workbook is easy to navigate using menus; users do not need to know how the Excel interface works. 4 of 4

Springfield-Green-Part1

Related Documents