Problem 14: You own a pastry bakery in your hometown. You have a small product line specialting in three types of pastries. Each day you make three items quick bread such as banana bread or pumpkin bread, loaf cakes such as lemon or chocolate, and fruit strudels. People love your food, 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 shall space, you could make and sell more pastries, but you are limited to 1,000 feet of shelf space Up until now, you have just guessed at how many and what 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 shell spaces severely limited You sell a strudel for $6.50, a quick bread for 54.50, and a loaf cake for $5.50. You also know your variable expenses for each pastry - the cost of the ingredients, the cost of baking, and so on. That means you can compute your profe margin on each item fie, sales price minus variable expenses) You also know your fixed costs for baking pastries every day. In other words, you know what your expenses would be even if you made and sold no pastries. Your shelves are bolted to the wall and are about 3 feet deep therefore you have a total of 1,000 linear feet of shet space. A pastry is laid on the shelf so that its length is perpendicular to the wall. Thus, customers see the front of the pastries as they look at the shelves. For example, a quick bread takes up 75 percent of a foot as it sits on the shelf. So, it 10 loaves of quick bread were laid out on the shelf, they would take up 10*0.75 = 75 feet of linear shell space. You never put a pastry on top of another, each one actually sits on the shelt. Assume that you lay out all your day's production at once. You want to have a balanced pastry inventory, so you feel that you must make and sell at least a certain tumber of loaves of quick bread, strudel, and cake loaves each day. You do not want to overdo any one kind of pastry, so you feel that you should also have a daily production maximum for each. Your oven and your production capacity have a limit. You have never been able to produce more than 1,200 items 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 16 percent, but no taxes are paid on pre-tax losses. If gre tax profits are negative, income tax expense is zero. You want the Solver tool to tell you how of each item to produce in a day. You want to maximize net income after taxes, subject to the constraints specified More data about the problem One pastry takes up the following amounts of space as they sit on the shelf: Loaf Cake-90 percent of a foot Strudel - 110 percent of a foot Quick Bread - 75 percent of a foot Your variable expenses per loaf for each type are: loaf cake $1.73; quick bread, $1.40 strudel. 51.95. For example, costs you $1.95 out of pocket to make a strudel, but you get $6.50 each time you sell a strudel. 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 Loaf Cake Idollars) Strudel Idolas) Quick Bread dan 03 Butter Filling Cake flour 03 0.25 0.2 0.4 015 0.2 0.15 005 Unbleached Flour Baking Soda Nuts White sugar Brown Sugar Ees Vanilla Extract 025 0.08 008 0.2 0.25 01 0.05 005 03 04 04 02 0.05 01 505 Your business has fixed costs of $90 a day. These expenses are set even if you made and sold no pastries. You want to have a balanced inventory, so you feel that you must make and sell at least 150 loaves of quick bread, 150 loaves of loaf cake, and 100 strudels each day. You do not want to overdo anyone kind, so you decide not to produce more than 700 loaves of quick bread, 600 loaves of loaf cake, or 350 strudels a day. These requirements are shown in the following table: Table 2 Quick Bread Loaf Cake Strudel Minimum Production 150 150 100 Maximum Production 700 1600 350 Calculations: Total shelf space used: A function of the number of each type of pastry made and the shell space taken up by each Revenue from selling. A function of the number of pastries of each type made and the selling price Variable costs from selling: A function of the number of pastries of each type made and the variable cost of each Total of pastries produced, day. The total number of all three types of pastries produced in a day. With a future of rapidly increasing flour costs, you are thinking of different ways to increase your profit. One idea that Intrigues you is to open a small coffee and pastry 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 send you the money to get started. Using your famous pastry recipes, you could offer fantastic coffee shop for breaktas. Use Solver to help you decide to plan your pastry production. You will need to model twe situations 1. The first uses the basic cost for the pastry as described in Table 1 (the base cases rename the worksheet as Base case. Fill in the sheet and Solver (It will be nonlinear), to generate maximum Net Income in the following table Number of oat cakes Number of strudels Number of quick breads Netcome 2. The second situation models the future where the price of four both cake and bleached our) 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 pastries. You still want variety, so you are keeping the maximum for each type of pastry. Keep in mind that you need to make complete pastries, you cannot make a portion of a pastry. Copy the base case into the extended case worksheet. Use the extended case worksheet to calculate the new variable costs based on the increase in flour costs Run Solver and fill in the following table: Number of locales Number of strades Number of quick breads 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 coffee shop extension. Make a final table as follows: Base Case Extended Case Number of loaf cakes Number of strudels Number of quick breads Net Income Looking at the table, will the bank fund the coffee shop extension? What is the percent reduction formatted to 2 decimals