Lesson title
Inventory Management for a Small Coffee Shop
Subject area
Marketing
Learner description
Learners are college students majoring in marketing. These students have a foundational understanding of marketing principles and strategies, acquired through previous coursework. They have varying levels of proficiency in Microsoft Excel, ranging from basic to intermediate. Learners have diverse backgrounds, learning preferences, and career aspirations. They are generally tech-savvy and motivated to learn practical skills that can be applied in real-world business scenarios.
Lesson summary
This lesson aims to teach students the importance of effective inventory management for a small coffee shop. Using Microsoft Excel, students will engage in hands-on activities that cover various aspects of inventory management, from tracking and monitoring to cost analysis and reorder points.
Content and technology standards
Not applicable
Learning objectives
Students will identify at least 10 key inventory items for a small coffee shop. (Remember)
Students will use Excel to input initial data for at least 10 key inventory items and track their levels. (Apply)
Students will accurately calculate the cost of goods sold for at least 5 inventory items using Excel functions. (Analyze)
Students will determine the optimal reorder points for at least 3 key inventory items. (Evaluate)
Students will create an inventory management report covering at least 10 key inventory items using Microsoft Excel. (Create)
Prerequisites for teacher/trainer and learners
The instructor should have a strong grasp of marketing concepts and be well-versed in inventory management principles. They should also have a strong proficiency in Microsoft Excel and experience in facilitating group work and discussions to ensure effective collaboration among students. Additionally, the teacher/trainer should be prepared to offer extra resources and support to accommodate a diverse range of student needs and learning styles.
Learners are expected to have a basic knowledge of marketing concepts, acquired either through previous coursework or practical experience. Basic skills in Microsoft Excel are also required, including the ability to perform simple calculations and use basic functions. Familiarity with collaborative work will be advantageous, as the lesson involves group activities. Additionally, learners should be open to participating in discussions, sharing their perspectives, and reflecting on insights gained.
Resources
Chapter 19 of “Introduction to Operations Research” by Frederick Hillier and Gerald Lieberman will be assigned for pre-reading to give students foundational knowledge about inventory management
Initial inventory data. Each group will be working with different initial inventory data sets (e.g. overstocked on beverages, understocked on food items; balanced stock, but with a higher proportion of seasonal items; understocked on all items; overstocked on all items, but perishable items are close to expiration; stock includes a large amount of discontinued or sale items). but will be responding to the same scenario (e.g. the holiday season is approaching, expect increased sales).
Reference guide/cheat sheet of Excel functions
Detailed rubric outlining criteria for the Excel workbook submission and group presentation
Whiteboards and markers to jot down notes, thoughts, or key points among students
Technology tool(s) used by teacher/trainer and learners for the lesson
Microsoft Excel: Students will use Microsoft Excel to input, analyze, and manage sample inventory data for a small coffee shop. They will apply formulas to calculate inventory metrics like turnover ratio and reorder level.
PowerPoint: PowerPoint will be used by the instructor to present lesson objectives, guidelines, and key points. While students won’t directly use PowerPoint in this lesson, they will engage with the content presented by the instructor to gain a comprehensive understanding of inventory management concepts.
Learning Management System (e.g., Canvas): All digital resources will be uploaded to Canvas for easy student access. Students will access Canvas to view and download these resources and submit their completed Excel exercises for assessment.
Assistive technology
Screen reader software, such as JAWS (Job Access With Speech), is an assistive technology designed to make computers accessible to persons who are visually impaired. The software reads aloud the text displayed on the screen, including PowerPoint slides, making it easier for both the instructor/trainer and learners with special needs to engage with the lesson content. Screen reader software is considered high-tech assistive technology because it involves complex software algorithms and functionalities to convert digital text into synthesized speech.
JAWS can be procured from the Freedom Scientific website (https://www.freedomscientific.com/products/software/jaws/).
That said, the Director of Disability Resources would be the primary point of consultation, as she is qualified to provide expert advice on the most suitable assistive technologies based on the specific needs of the student. She has a dedicated budget for such technologies and can either approve the purchase directly or guide the process of securing additional funding. Collaboration with Information Technology Services (ITS) would be essential for the installation of the software and to ensure compatibility with existing systems and applications.
Grouping
The instructor will form groups based on a combination of factors, including performance on previous assignments, participation in class discussions, and observed teamwork skills.
In small groups, students will be tasked with managing the inventory of a hypothetical small coffee shop. Each group will download a set of initial inventory data from Canvas. Students begin by identifying key inventory items for their coffee shop. They can divide this responsibility by types of items: one student can focus on beverages, another on food items, and another on supplies, ensuring that they cover a broad spectrum of inventory items. Excel Online allows for real-time collaboration which enables one member to share their screen on one of the displays in the active learning classroom as they collaboratively work on the inventory data. One student in each group might concentrate on tracking and monitoring inventory levels, another on calculating the cost of goods sold, a third on determining reorder points, and a fourth can collate the information and insights gathered by the group in preparation for the presentation.
Technology serves an important role in facilitating hands-on learning and collaboration. Specifically, students will make use of Excel Online, a cloud-based version of the spreadsheet software. Excel Online allows students to work simultaneously on shared workbooks. Each group will also use the active learning classroom’s projection system to share their work and explain their decisions.
Methods for teaching social, ethical, and legal issues surrounding the responsible use of technology used in this lesson plan
With regards to the ethical aspects of using Excel, there are two things to consider: data integrity and confidentiality. When working on inventory scenarios, students could be tempted to alter the data to achieve desired results. To mitigate this, the instructor can discuss the importance of maintaining accurate data. Secondly, if the exercise were to use real-world data, there is an ethical obligation to ensure confidentiality. Clear protocols should be shared to ensure that students understand their responsibilities regarding data privacy.
Learner Assessment
The learner assessment for this lesson is a combination of an Excel workbook and a brief group presentation. The Excel file allows students to demonstrate their ability to track inventory, calculate the cost of goods sold, determine optimal reorder points, and create an inventory management report. The presentation gives students the opportunity to articulate their decision-making process and rationale behind their inventory choices.
Both will be evaluated based on a rubric. The presentation aspect will be less emphasized but will still assess the group’s ability to present their findings clearly and coherently.
Learning activities or tasks
The lesson will take place in an active classroom and will last for 90 minutes. The active learning classroom is equipped with a resident computer housed in a podium with a touch screen panel to control the projector and screen. There are movable student tables and chairs, all on wheels, as well as movable whiteboards, and additional flat-screen displays around the room and multiple outlets. Students will be grouped into teams of 3-4, with each team assuming the role of inventory managers for a small coffee shop.
The instructor will first recap the key points from the pre-assigned reading and go over the primary challenges of inventory management (balancing supply and demand, cost management data accuracy, timing, seasonal variability, vendor reliability) and the role of Excel (suitable for a one-stop shop for data organization, ease of cost calculation, forecasting tools, robust reporting) in addressing these challenges.
Next, each group will brainstorm and identify the key types of inventory costs (e.g. holding, ordering, and shortage costs), as well as basic Excel functions like SUM and AVERAGEIF. This information is inputted into a shared Excel workbook. Groups will be given 40 minutes to work. One student can focus on tracking inventory levels of beverages, another on food items, and a third on supplies like cups and napkins. This allows the teams to cover a broad range of inventory items. Students also must calculate the cost of goods sold and determine reorder points using Excel functions. They collaboratively input and analyze data based on the provided scenario (e.g. the holiday season is approaching, expect increased sales).
A student from each group shares their screen to a flat-screen display, making it easier for teams to collaborate and for the instructor to see their work. As students work, the instructor circulates the classroom to provide guidance and give feedback.
The final 20 minutes are allocated for brief group presentations. Each group projects their work to all the screens and presents their findings, specifically their inventory management decisions. Although all groups are addressing the same scenario, the unique initial data sets will result in varied approaches and decisions. Later, the instructor evaluates the presentations and workbooks based on the rubric.
In this lesson, technology enables collaboration and practical application. Students will use Excel Online to work on shared spreadsheets in real-time. This allows them to practically apply inventory management concepts while gaining experience with an industry-standard tool, thereby equipping them for their future careers.
Teaching-learning methods
This lesson employs a problem-based learning method: students are given real-world scenarios where they act as inventory managers for a small coffee shop. The focus is on practical application and group collaboration. Students are tasked with various inventory-related challenges that require critical thinking, decision-making, and the use of Excel functions.
Support learner-centered strategies
This lesson is highly learner-centered. First, the problem-based approach allows students to engage with real-world issues, making the learning context immediately relevant. The lesson also employs collaborative learning by organizing students into teams. This strategy fosters peer-to-peer interaction and allows for different perspectives to interact. Collaboration is further facilitated by technology, as students use co-editing features to work on a shared spreadsheet in real-time.
Modifications for instruction
To ensure that all students can access the lesson content, alternative formats can be provided. For instance, the PowerPoint slides and instructions can be made available in both text and audio formats. Closed-captioning can be used for any video materials and screen-readable options can be provided for visually impaired students.
The physical classroom itself should be accessible to everyone, including those with mobility issues. Online resources should be compatible with screen readers and other assistive technologies.
Improve instructional practice and maximize learning
As the university’s LMS, Canvas serves as the central platform for collecting and analyzing learner achievement data. Students will upload their Excel workbook to a Canvas assignment. Grading will be conducted using Canvas’s Speedgrader feature. After grading, students can access their individualized feedback and grades directly through Canvas.