You own a bakery in your hometown. You have a small product line- bread and pastries, for the most part. Each day you make three kinds of loaves of bread: rye, wheat, and pumpernickel. People love your fresh bread, and you always sell out of your daily production, so you want to do a better job of production planning. Your bakery is small and cannot be expanded. If you had more shelf space, you could make and sell more bread, but you are limited to 800 feet of shelf space. Up until now, you have just guessed at how many loaves of each type of bread you should bake each day. However, you have heard that Excel Solver can help you determine these daily amounts and maximize net income, even though your shelf space is severely limited. You sell a loaf of rye bread for $3.25, a loaf of wheat for $3.50, and a loaf of pumpernickel for $4.00. You also know your variable expenses for each kind of bread - the cost of the ingredients, the cost of baking, and so on. That means you can compute your profit margin on a loaf of each kind of bread (i.e., sales price less variable expenses) You also know your fixed costs for baking bread every day. In other words, you know what your expenses would be even if you made and sold no bread. Your shelves are bolted to the wall and are about 3 feet deep. You have 800 linear feet of shelf space. A loaf of bread is laid on the shelf so that its length is perpendicular to the wall. Thus, customers see the end of the loaves as they look at the shelves. For example, a loaf of pumpernickel takes up 60 percent of a foot as it sits on the shelf. So, if 10 loaves of pumpernickel were laid out on the shelf, they would take up 10*0.6 = 6 feet of linear shelf space. You never put a loaf of bread on top of another, each loaf actually sits on the shelf. Assume that you lay out all your day's production at once. You want to have a balanced bread inventory, so you feel that you must make (and sell) at least a certain number of loaves of rye, wheat and pumpernickel bread each day. You do not want to overdo any one kind of bread, so you feel that you should also have a daily production maximum for each. Your oven and your productive capacity have a limit. You have never been able to produce more than 900 loaves of bread in a day, so you think that is a practical limit on total daily production. You are profitable and want to stay profitable. Taxes are charged on pre-tax profits at the rate of 26 percent, but no taxes are paid on pre-tax losses. If pre-tax profits are negative, income tax expense is zero. You want Solver to tell you how many loaves of bread to produce in a day. You want to maximize net income after taxes, subject to the constraints specified. More data about the problem: One loaf of various types of bread take up the following amounts of space as they sit on the shelf: Rye - 9 inches or 75 percent of a foot Wheat -90 percent of a foot Pumpernickel - 60 percent of a foot Your variable expenses per loaf for each type are: rye, $1.28; wheat, $1.13; pumpernickel, $1.65. For example, it costs you $1.28 out of pocket to make a loaf of rye, but you get $3.25 each time you sell a loaf of rye bread. The actual cost of ingredients is shown in the following Table. This mix is important because the cost of flour is slated is rise dramatically over the next year. Table 1: Cost of bread ingredients . Yeast Molasses Bread Flour Rye Flour Salt Oil Milk Cornmeal Brown Sugar Honey Rye (dollars) 0.1 0.1 0.5 0.45 0.05 0.08 Rye Wheat Pumpernickel Pumpernickel (dollars) Minimum Production 125 125 75 0.1 0.1 0.5 0.45 0.05 .08 0.05 0.12 0.2 Wheat (dollars) 0.1 0.8 0.05 The bread making side of your business has fixed costs of $110 a day. These expenses are set even if you made and sold no loaves of bread. You want to have a balanced bread inventory, so you feel that you must make and sell at least 125 loaves of rye, 125 loaves of wheat, and 75 loaves of pumpernickel each day. You do not want to overdo any one kind, so you decide not to produce more than 500 loaves of rye, 500 loaves of wheat, or 300 loaves of pumpernickel a day. These requirements are shown in the following table: Table 2 0.08 .05 0.05 Maximum Production 500 500 300 Calculations: Total shelf space used: A function of the number of each type of loaf made and the shelf space taken up by each Revenue from selling: A function of the number of loaves of each type made and the selling price Variable costs from selling: A function of the number of loaves of each type made and the variable cost of each Total # of loaves produced, day: The total number of all three types of loaves produced in a day. You want to stay profitable. You feel that your ratio of net income to total bread revenue should be at least .15. With the future price flour rising, you are thinking of different ways to increase your profit. One idea that intrigues you is to open a small sandwich shop across the street. The business that currently occupies the shop has gone into bankruptcy and is closing. You have an opportunity to take it over, if the banker will lend you the money to get started. Using your famous bread recipes, you could offer fantastic sandwiches for lunch. Using your knowledge of Excel and Solver (it will be nonlinear), you decide to plan your bread production. You will model two situations: 1. The first uses the basic cost for the bread as described in Table 1 (the base case); rename the worksheet as Base case. Fill in the sheet and run the solver to generate maximum Net Income. Fill in the following table: Number of loaves of Rye Number of loaves of Wheat Number of loaves of Pumpernickel Net Income 2. The second situation models the future where the price of flour (both rye and bread flour) is expected to rise 25% (the extension case). Because this increase is quite high, you are willing to drop the minimum constraint on making the loaves. In other words, you will make any number of types of loaves to maximize income. Keep in mind that you need to make complete loaves; you cannot make a portion of a loaf of a bread. Copy the base case into a new worksheet and name it as extended case. Find the variable costs of each bread by increasing the flour costs by 25% and make the corresponding changes in the extended case. Run the solver and fill in the following table: Number of loaves of Rye Number of loaves of Wheat Number of loaves of Pumpernickel Net Income 3. Comparing the results of the two cases will help you plan your future production and justify your requests to the banker. The banker says that if net income drops more than 10 percent from the base case with the increased cost of the flour ingredients, the bank cannot fund the sandwich shop extension. Make a final table as follows: Base Case Extended Case Number of loaves of Rye Number of loaves of Wheat Number of loaves of Pumpernickel Net Income Looking at the table, will the bank fund the sandwich shop extension? What is the percent reduction formatted to 2 decimals? A 1 THE BAKERY SHELF SPACE PROBLEM 2 Changing Cells: 3 Number of loaves of Rye 4 Number of loaves of Wheat 5 Number of loaves of Pumpemickel 6 7 Constants: 8 Total shelf space available (feet) 9 Tax rate 10 Selling Prices, per loaf: 11 Rye 12 Wheat 13 Pumpemickel 14 Variable Costs, per loaf: 15 Rye Wheat 16 17 Pumpemickel 18 Min Production (day): 19 Rye 20 Wheat 21 Pumpemickel 22 22 Max Production (day): 23 Rye 24 Wheat 25 Pumpemickel 26 Fixed Costs (day): 27 Net Income to Revenue ratio target Shelf space used per loaf. 28 (decimal portion of a foot): 29 Rye. 30 Wheat 31 Pumpemickel 32 Max Loaves that could be made, all types 33 34 Calculations: 35 Net Income to Revenue ratio 36 Total Shelf space used 37 Revenue from selling: 38 Rye 39 Wheat 40 Pumpemickel 41 Variable Costs from selling: 42 Rye 43 45 Wheat 44 Pumpemickel 45 Total # of loaves produced, day: 46 47 48 49 One day Income Statement: 50 Revenue - all loaves 51 Variable Costs - all loaves 52 Fixed Costs 53 Total Costs 54 Pre-Tax revenue 55 Income tax expense 56 Net Income 57 58 B C D E F H 1 J L M 1 THE BAKERY SHELF SPACE PROBLEM 2 Changing Cells: 3 Number of loaves of Rye 4 Number of loaves of Wheat 5 Constants: 8 Total shelf space available (feet) 9 Tax rate 10 Selling Prices, per loaf: 11 Rye 12 Wheat Number of loaves of Pumpemickel 13 Pumpemickel 14 Variable Costs, per loaf: 15 Rye 16 Wheat 17 Pumpemickel 18 Min Production (day): 19 Rye 20 2 21 23 24 Pumpemickel 22 Max 22 Max Production (day) Wheat 29 30 Rye Wheat 25 Pumpemickel 26 Fixed Costs (day): 27 Net Income to Revenue ratio target Shelf space used per loaf 28 (decimal portion of a foot): Rye Wheat 31 31 Pumpemickel 32 Max Loaves that could be made, all types 33 34 Calculations: 35 Net Income to Revenue ratio 36 Total Shelf space used 37 Revenue from selling: 38 Rye 39 Wheat 40 Pumpemickel 41 Variable Costs from selling: 42 Rye 43 Wheat 44 Pumpemickel 45 Total # of loaves produced, day: 46 47 48 49 One day Income Statement: 50 Revenue- all loaves 51 Variable Costs - all loaves 52 Fixed Costs 53 Total Costs 54 Pre-Tax revenue 55 Income tax expense 56 Net Income 57 58 Percent Income Reduction between the two scenarions TO B C D E F G H 1 J K L M N LE34500 E A 22 23 24 25 Base Flour Increase Yeast Molasses Bread Flour Rye Flour Salt Oil 10 Milk 11 Cornmeal 12 Brown Sugar 13 Honey 14 15 16 17 18 19 20 21 B 25% Rye (dollars) Pumpernickel (dollars) 0.1 0.1 0.5 0.45 0.05 0.08 C 1.28 0.1 0.1 0.5 0.45 0.05 0.08 0.05 0.12 0.2 1.65 Wheat (dollars) 0.1 0.8 0.08 0.05 0.05 0.05 1.13 E F Extended Yeast Molasses Bread Flour Rye Flour Salt Oil Milk Cornmeal Brown Sugar Honey Rye (dollars) H Pumpernickel (do Wheat (dollars) J K L