You own a bakery in your hometown. You have a small product line- bread and pastries,...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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 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
Expert Answer:
Answer rating: 100% (QA)
Answer First of all you will have to add Solver Add in to your Microso... View the full answer
Related Book For
Smith and Roberson Business Law
ISBN: 978-0538473637
15th Edition
Authors: Richard A. Mann, Barry S. Roberts
Posted Date:
Students also viewed these accounting questions
-
How could you use big data to do a better job at work? Give some specific examples of how you might use big data to gain valuable new insights.
-
Your bakery produces loaves of bread with 1 pound written on the label. Here are weights of randomly sampled loaves from todays production: a. Find the 95% confidence interval for the mean weight of...
-
A bakery produces a certain type of bread in an oven that stacks trays nine high. The specification for the moisture content of finished loaves is 11% + 2%. Samples are taken by selecting a loaf at...
-
Tony acquired 1,000 shares in X Co (a resident public company) for $10 each in August 2000. In January this year X Co returned $7 of capital to its shareholder in respect to each share they held. The...
-
What differences exist between ARMs and FRMs?
-
Identify at least two situations in which important changes in value are not reported in the income statement.
-
What are the different types of consulting and litigation support activities for fraud and forensic accounting professionals?
-
Lovell Computer Parts Inc. is in the process of setting a selling price on a new component it has just designed and developed. The following cost estimates for this new component have been provided...
-
The specific heat of a certain type of cooking oil is 1.75 J/(g.C). How much heat energy is needed to raise the temperature of 2.92 kg of this oil from 23 C to 191 C? 9 = J
-
On May 1, Soriano Co. reported the following account balances along with their estimated fair values: On that day, Zambrano paid cash to acquire all of the assets and liabilities of Soriano, which...
-
a-For the truss shown design two members in tension and two in compression, if fy =250MPa and FU = 400 MPa > b- design joint connection by bolts and welding Pd=20kN PL= 15 kN P P P -6@3-18 P P
-
Write an Critique Essay on Reader's Digest | How Much to Tip in 2022: Hairdressers, Movers, Delivery, and Drivers (More than two sources are required and word limit up to 800or850)
-
Given data below. Answer the questions. Stockholders' equity: Preferred stock ($100 par) $200,000 Common stock ($0.10 par) 10,000 Additional paid in capital 2,506,000 Total paid in capital 2,716,000...
-
need help for this java program. I already have an actual code but it needs a revision for this program. Kindly check the details below. Required: - Some comments in the code. - Screenshots of the...
-
You are planning to start a trucking business with a fleet of high-efficiency electric vehicles. You believe your firm will face market risk similar to ABC Co. ABC's beta is 1.2. Estimate the cost of...
-
1A.What does it mean if a nation has a feminine communication style? B.How would you feel if you lived in that kind of culture? C.What is the difference between a patriarchal/matriarchal society and...
-
s) Consider the function f(x) = x2/5(x - 5). This function has two critical numbers AB Then A = For each of the following intervals, tell whether f(x) is increasing or decreasing. (-, A]: ? [A, B]: ?...
-
A company pledges their receivables so they may Multiple Choice Charge a factoring fee. Increase sales. Recognize a sale. Collect a pledge fee. Borrow money. Failure by a promissory notes' maker to...
-
This is a stocklist case arising under 220(b) of our [Delaware] General Corporation Law. The issue is whether a shareholder states a proper purpose for inspection under our statute in seeking to...
-
The Racketeer Influenced and Corrupt Organizations Act (RICO) is directed at racketeering activitydefined to encompass acts indictable under specific Federal criminal provisions, including mail and...
-
Identify and describe the sources of law.
-
Whenever Aliza buys a flashlight, she also always buys exactly two batteries along with it. Any more than two batteries will serve no purpose, because she will not be able to use them in the...
-
State governments in India levy a value-added tax on the sale of goods. The rate of this sales tax varies by type of good and by state. In Maharashtra, the general rate of sales tax is \(12.5 \%\),...
-
Divit enjoys watching team sports and considers tickets to kabaddi and badminton games to be perfect substitutes. Show his preference map. What is his utility function?
Study smarter with the SolutionInn App