Please use parts 1 and 2 as your reference. Please answer only part 3 and part 4 including an Excel model.

Leslie Presley Bookcase Company A case in four parts When Leslie Presley started building bookcases for friends in their garage, they didn't imagine that one day they'd own and run a company with a global supply chain and a dozen employees. When asked to explain this success, Leslie always replies by saying it is a combination of a passion for excellent woodworking and a background in operations that together helped create the Leslie Presley Bookcase Company (LPBC). LPBC manufactures bookcases in two sizes: short and tall. They come in three finishes: pine, maple, and white enamel. The white enamel bookcases are built out of pine and finished with a waterborne alkyd paint; the other two are finished with boiled linseed oil. The combination of sizes and finishes means there are six products manufactured by the company. Each bookshelf is constructed using 1x12 lumber. A 1x12 board is a piece of wood 1 thick and 12" wide, and can be varying lengths. The total amount of lumber required to construct a bookcase is given in board feet, which describes the total length of boards needed. Leslie finds that demand for these products is strong, and that as long as there are enough tall versions, anything LPBC can manufacture will end up selling. Getting quality lumber materials, however, is sometimes problematic. As well, having enough hours of skilled labour in the Vancouver-based shop can constrain production. Part I: Staring out the window at the rainy Vancouver weather and thinking about production for the upcoming month, Leslie realizes that an optimization model might help increase profits. Tables 1, 2, and 3 provide the data required to create a linear program to figure out how many of each product to manufacture next month. Assume that a non-integer solution is not a concern for LPBC (e.g., perhaps non-integer values could simply be rounded up or down when it comes time to implement the plan. Table 1. The material and labour requirements for one unit of each shelf. Pine short Pine tall Maple Maple tall Enamel short short Pine 30 43 30 Maple 30 43 Labour 2.5 3.5 3 4 4.5 Enamel Units tall 43 | board feet board feet 5.5 hours Table 2. Resources available. Pine 21510 Maple 2525 Labour 2800 Table 3. Gross profit; volume requirements. Profit per bookcase Min. number to build Pine short $ 148.25 Pine tall $ 153.50 50 Maple short $ 134.50 Maple tall $ 120.00 35 Enamel short $ 196.25 Enamel tall $ 200.50 220 Gross profit, for the purposes of this optimization problem, is calculated by taking revenue minus the costs of lumber, other materials, and labour. The gross profit for the last three months averaged just over $100K per month. How much gross profit can LPBC make next month? And what is the optimal production plan? Part II Leslie wishes to augment the optimization model to include the costs of lumber, labour, and other materials, as well as the selling prices. Tables 4 and 5 provide this information. Table 4. Cost/unit for lumber and labour. Pine $ 2.25 Maple $ 13.00 Labour $ 22.50 Table 5. Cost of other materials; selling price per unit. Pine Pine tall Maple short short Cost other mat. $ 8.00 $ 11.00 $ 8.00 Selling price $280.00 $ 340.00 $ 600.00 Maple tall $ 11.00 $ 780.00 Enamel short $ 15.00 $ 380.00 Enamel tall $ 19.00 $ 440.00 2 Assuming Leslie used the correct figures, they should get the same answer as in Part I. If not, where does the discrepancy come from? Lumber prices have been fluctuating recently. What happens if the price for a board foot of pine decreases by $0.40? What if the price of maple simultaneously increases by $2.40? Part III Market research indicates that the there's potential demand for enough additional bookcases to justify expansion. Table 6 shows projected demand for each product. Leslie is planning on opening manufacturing facilities in Abbotsford and Prince George to help satisfy this demand. Because there are differences in labour costs and raw material costs at the Vancouver, Abbotsford, and Prince George facilities, the unit profit is not consistent across facilities. Table 7 includes the anticipated gross profit per bookcase, by location, that Leslie has calculated. Table 6. Projected maximum demand. Demand Pine short 390 Pine tall 450 Maple short 140 Maple tall 120 Enamel short 340 Enamel tall 380 Table 7. Gross profit per bookcase by location. Vancouver Abbotsford Pine short $ 148.25 $ 133.25 Pine tall $ 153.50 $ 145.50 Maple short $ 134.50 $ 131.50 Maple tall $ 120.00 $ 118.50 Enamel short $ 196.25 $ 205.75 Enamel tall $ 200.50 $ 210.50 Prince George $ 158.00 $ 163.25 $ 145.50 $ 130.00 $ 181.75 $ 185.75 The Prince George facility will include a special paint room that allows the enamel bookcases to be completed in less time (and therefore at a lower cost). Specifically, it will take 3.25 hours of labour for a short enamel bookcase and 3.75 hours for a tall enamel bookcase in Prince George. In order to support increased production, Leslie has found a way to source more materials, as shown in Table 8 (along with the labour available in each facility. Table 8. Resources available. Pine 60000 Maple 4800 Labour Vancouver 2800 Labour Abbotsford 2500 Labour Prince George 700 Leslie would like to create an optimal monthly production plan that includes the two new facilities in order to get a better idea of what production will look like if and when the two new facilities are operating. In order to allow the new facilities to work through any kinks in production during the first year of operations, Leslie has decided to limit Abbotsford to 750 units per month and Prince George to 180 units per month. What is the optimal production plan for a typical month, assuming the new facilities are operating and assuming that all bookcases manufactured can be sold (up to the projected maximum demand)? Again, assume a non-integer solution is not a concern. Part IV After some additional research Leslie has realized they can also expand the Vancouver facility. For a one-time capital expenditure of $3 million, Vancouver could handle up to 4000 labour hours per month but would be limited to 1100 units/month. Or for an expenditure of $4.5 million, Vancouver could handle up to 5000 labour hours per month and 1300 units/month. The expansion would involve taking over existing space on a neighbouring lot that is already set up for similar manufacturing. Leslie has estimated the cost of opening the Abbotsford facility at between $5 and $5.5 million. The cost of opening the Prince George facility is between $2 and $2.1 million. These costs are for purchasing existing facilities that are mostly tooled appropriately, and include bringing in additional equipment (which would take 1-2 weeks). Unless otherwise specified, use the projected demand and the resources available (from Part III). Leslie would like to invest in one of the Vancouver expansion options, the Abbotsford facility, the Prince George facility, or any possible combination of these options. They would like to pay off the investment in 5 years assuming a monthly interest rate of 5% divided by 12. What are the options, and what should Leslie do? Do you have any other recommendations for Leslie that pertain to these possible expansions