California National University Cost & Menu Analysis of In N Out Burger Worksheet

User Generated

vojlyvrpblbgr

Writing

California National University for Advanced Studies

Description

You are required to research costs items at www.costcobusinessdelivery.com and breakdown all bulk items down to a unit cost. Each Excel worksheet provided in the attached workbook contains blank, yellow highlighted cells that require you to completely fill in costs or link costs through Excel formulas. Amount totals for sales (# sold) and payroll for the P&L will be based on "what-if" analysis.

You are required to use the worksheet below:

Unformatted Attachment Preview

This analysis will assist you in computing the costs and revenues necessary to make critical production decisions. Menu Items Double-Double Instructions Cheeseburger Hamburger French Fries This tab is asking you just to input the menu price for each of the items above. Later on, we'll see these menu prices transferred forward automatically, so we can do some analyses. Food Inventory Tip: Use the Costco business site to approximate the costs below. The yellow cells require you to enter the data and/or calculate the cost. This will flow into the Cost Breakdown tab. Meat Inventory Item Beef Recipe Sandwiches Amount lb Cost Amount lb lb lb lb Cost Amount ct Cost Amount ct gal lb Cost Produce Inventory Item Lettuce Potatoes Tomatoes Yellow Onions Recipe Sandwiches French Fries Sandwiches Sandwiches Item Cheese Recipe Sandwiches Dairy Inventory Other Inventory Item Buns Dressing (Sauce) Frying Oil Recipe Sandwiches Sandwiches French Fries Tip: Notice that we're focusing on the food related inventory. In-N-Out does have beverages, including soft drinks and milkshakes. They also have paper bags, napkins, utensils, condiments, salt, pepper, etc… There is also additional options like "animal style" and "carmelized onions." For sake of our high-level analysis, we're going to focus on the food related products here and ordering the burgers without any of those enhancements. We will touch on the condiments, pepper and salt in the Menu Analysis. Also, for this exercise, let's assume that we can use "Thousand Island dressing" as an approximation of In-N-Out's sauce. Cost Breakdown Tip: The Inventory data will populate the blue cells automatically. You calculate the yellow fields, which shows you the unit cost per that unit's measurement (ounce or count, as appropriate). Later, we'll actually make real In-N-Out burgers. Burgers Ground Beef Cheese Bun Lettuce Tomato Onion Dressing French Fries $ $ $ $ $ $ $ Costco - Bulk Costs lb ct 0 0 0 0 0 0 gal 0 Unit Cost in oz/ct Bulk Costs Costco lb Potatoes $ 0 Frying Oil $ 0 Pounds to Ounces Gallons to Ounces ct Unit Cost in oz/ct Conversion Note 1 lb = 16 ounces 1 gal = 128 ounces Tip: The burger section includes all ingredients that are used in different combinations, or not at all, for the burger variations (e.g. no cheese is put on a hamburger, or a double-double has two slices of cheese + two beef patties). Remember to convert pounds to ounces, and gallons to ounces. Menu Analysis Tip: Fill in the yellow sections below. As before, the blue cells will populate from the prior tabs. The goal is to understand approximately how much each menu item costs In-N-Out to make. This will help us understand our approximated margin per unit sold (how much money we make on each item sold). For "Number of Portions-Yield," let's keep this at 1 item sold. Ingredients Ground Beef Cheese Bun Lettuce Tomato Onion Dressing Potatoes Frying Oil Unit oz ct ct oz oz oz oz oz oz Cost per Unit $ $ $ $ $ $ $ $ $ Recipe Cost Incidental Cost -- "Q" Factor = 2% Total Recipe Cost Number of Portions - yield Food Cost % = 28% Calulated Menu Cost Actual Menu Price Profit Margin per Unit Sold Profit Margin per Unit Sold as Percentage of Revenue Double-Double Sub-Totals Quantity $ $ $ $ $ $ $ $ $ - Cheeseburger Sub-Totals Quantity $ $ $ $ $ $ $ $ $ - Hamburger Sub-Totals Quantity $ $ $ $ $ $ $ $ $ - French Fries Sub-Totals Quantity $ $ $ $ $ $ $ $ $ - $ $ $ $ $ $ $ $ 1 28.0% $ $ 1 28.0% - $ $ 1 28.0% - $ $ 1 28.0% - $ $ - Tip: The cost per unit section will populate based on what you did on the earlier tab. You need to just put the quantity per menu item. Remember that the double double has TWO beef patties and TWO pieces of cheese, so you will want to put 2 for each. The hamburger has no cheese. "Q Factor" is an approximation of some the incidental food items customers may add, like salt, pepper, condiments, etc... For this purpose we're assuming 2%. To calculate the minimum menu price, we need to charge in order to make profit and remember that In-N-Out has other costs, like lease payments, labor payments, marketing, insurance, information technology, etc.. That's where the "Food Cost %" comes in. For example, if the food cost is only 25% of the total cost we need to allocate, then Calculated Menu Cost 4-times the food cost to approximate our "all in" cost. Ingredient Beef patty Cheese Bun Lettuce Tomato Onion Dressing Potatoes Frying oil Assumptions you can make Approximate unit / menu item 2 ounces / burger 1 slice (or count) 1 bun / burger 1 ounce / burger 0.5 ounces / burger 0.5 ounces / burger 0.5 ounces / burger 5 ounces / order of fries 5 ounces / order of fries What If Analysis - Menu Mix Tip: This is a high-level "What if Analysis" that allows us to play with how many units an In-N-Out location sells. As before, you can input different numbers in the yellow section, and the blue cells will automatically populate. As sales increase, you'll need more workers. Check the final P&L statement to see the impact! Menu Item Number sold per day Total Receipe Cost Menu Price Food Cost % Total Costs (based on estimated Operating Costs) Total Sales (Revenue) Sandwiches Double-Double Cheeseburger Hamburger $ $ $ - $ $ $ - 28.0% 28.0% 28.0% $ $ $ - $ $ $ - Sides French Fries $ - $ - 28.0% $ - $ - $ - $ - Totals Labor Costs Direct Labor Managers Front-line employees Totals Number required Wage rate ($ per Shift per hour) Number of Total Labor Cost hours per per Day person per shift Notes on Managers and Workers: - requires 2 shifts for 12 hour days - 8 hour shifts include open/close duties, leaving only 6 operating hours per person - requires minimum of 1 manager per shift - if sales < 5 burgers per minutes, then 1 managers per shift - if sales =>5 and 10 and 15 burgers per minute - managers can supervise a maximum of 5 employees during a shift - to cover counter, dinning room, cooking, cleaning and prep duties, corporate Computations: 0 0 0.0 0.0 burgers per day burgers per hour burgers per minute burgers per second Costs versus Sales $1 $1 $1 $1 $1 $1 $0 $0 $0 $0 $Double-Double Total Costs Cheeseburger Hamburger (based on estimated Operating Costs) French Fries Total Sales (Revenue) Profit & Loss Tip: This helps us get an idea of what In-N-Out's PNL (Profit & Loss) could be for an individual store. In reality there are many more inputs required, but this gives us a directionally accurate understanding! As before, you must input values in the yellow section, and the blue cells will automatically populate. Food-Only Cost Daily Totals $ # of Days of Sales Montly Totals $ Gross Profit Total Sales - $ - - $ - Operating Expenses Monthly Category Rent & Common Area Maintenance (CAM) Utilities Insurance Payroll / Labor $ Fringe Benefits Maintenance Permits Marketing Misc. Sub-Totals Net Profit / Net operating Margin Monthly Bill Rate - Percentage of Revenue #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached. Please let me know if you have any questions or need revisions.

Menu Items

Double-Double

Cheeseburger

$3.45

$2.40

Instructions

This tab is asking you just to input the menu price for each of the items above. Later on, w
automatically, so we can do some analyses.

This analysis will assist you in computing the costs and revenues necessary to
make critical production decisions.

Hamburger

French Fries

$2.10

$1.60

h of the items above. Later on, we'll see these menu prices transferred forward

Food Inventory

Tip: Use the Costco business site to approximate the costs below. The yellow cells
you to enter the data and/or calculate the cost. This will flow into the Cost Breakd
tab.

Meat Inventory
Item
Beef

Recipe
Sandwiches

Amount
4 lb

Produce Inventory
Item
Lettuce
Potatoes
Tomatoes
Yellow Onions

Recipe
Sandwiches
French Fries
Sandwiches
Sandwiches

Amount
5 lb
50 lb
25 lb
10 lb

Dairy Inventory
Item
Cheese

Recipe
Sandwiches

Amount
60 ct

Other Inventory
Item
Buns
Dressing (Sauce)
Frying Oil

Recipe
Sandwiches
Sandwiches
French Fries

Amount
24 ct
1 gal
35 lb

Tip: Notice that we're focusing on the food related inventory. In-N-Out does have beverages, including soft drinks and
milkshakes. They also have paper bags, napkins, utensils, condiments, salt, pepper, etc… There is also additional options l
"animal style" and "carmelized onions." For sake of our high-level analysis, we're going to focus on the food related produ
and ordering the burgers without any of those enhancements. We will touch on the condiments, pepper and salt in the M
Analysis. Also, for this exercise, let's assume that we can use "Thousa...

Similar Content

Related Tags