PLEASE SHOW EXCEL FORMULAS
PLEASE SHOW EXCEL FORMULAS
A B C D E F I J K L . M N 0 P Leather Goods LLC produces and sells leather goods. Their current product mix consists of wallets, weekender bags and backpacks. They sell their products out of oneretail location but are looking to expand in the coming years. Leather Goods LLC has been approached by their landlord and offered a pop-up space in a popular area for the holiday season for $8,000/month for 3 months. Leather Goods managers are deciding to offer their standard products or premium products at the pop-up space. The company accountant has provided the following information. Per Item Standard Premium 78.00 $ 134.00 32.00 $ 53.00 Average Sales Price Average Variable Costs Average Contribution Margin $ $ Additional fixed costs (excluding rent) are $1,500/month for 3 months. In the square footage of the pop-up, Leather Goods LLC can dispay and sell 540 standard products per month or 360 premium products per month. Calculate the average contribution margin and determine the expected operating income for each scenario. To maximize operating income, what type of products do you recommend Leather Goods sell in the pop-up? 3-Month Period Standard Premium 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 Expected Sales Expected Variable Costs Contribution Margin Fixed Costs Operating Income Recommend Product for Pop-Up ? Leather Goods LLC's 2020 income statement is shown below. Sales Variable Costs Contribution Margin Fixed Costs Operating Income Weekender Bags Wallets Backpacks Total $ 6,800,500 $ 2,140,000 $ 5,500,450 $ 14,440,950 2,720,200 1,348,200 2,805,230 6,873,630 4,080,300 791,800 2,695,221 7,567,321 2,025,000 1,077,000 1,890,900 4,992,900 $ 2,055,300 $ (285,200) $ 804,321 $ 2,574,421 Prepare a differential analysis below if Leather Goods LLC were to drop the wallets product line. Total fixed costs will not change if the company stops selling wallets. Weekender Bags Wallets Backpacks Total Sales Variable Costs Contribution Margin Fixed Costs Operating Income 48 49 Leather Goods short-term goal in this decision is to maximize operating income. Do you recommend the company drop wallets from their product mix? ? A B C D E F G I J K L M N 0 3 4 5 6 Leather Goods LLC uses a 9% rate of return to evaluate capital projects. The cash flows for the first 10 years of each project are shown below. Project A-Leather Goods LLC managers are considering offering purses. To offer purses they will need to invest $88,000 in a new machinery to produce and sell purses. The annual expected cash inflows from the sales of purses is $10,000. Project B - Leather Goods LLC managers are considering expanding the production of weekender bags. To expand the volume of weekender bags they will need to invest $94,000 to expand their current assembly line. The annual expected cash inflows from the additional sales of weekender bags is $15,000. Project C-Leather Goods LLC managers are considering opening a new retail space. The retail space will require a capital outlay of $228,000. The annual expected cash inflows from the opening of the retail space is $32,000. Project A Project B Project 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Capital Outlay 12/31/22 12/31/23 12/31/24 12/31/25 12/31/26 12/31/27 12/31/28 12/31/29 12/31/30 12/31/31 Payback (Round to One Decimal Place) IRR (Show as a %) 32 33 34 35 36 Which project do you recommend Leather Goods LLC managers invest in? ? 37 38 39 40 41 42 43 44 45 46 47 48 49 50 Product Mix Capital Projects + A B C D E F I J K L . M N 0 P Leather Goods LLC produces and sells leather goods. Their current product mix consists of wallets, weekender bags and backpacks. They sell their products out of oneretail location but are looking to expand in the coming years. Leather Goods LLC has been approached by their landlord and offered a pop-up space in a popular area for the holiday season for $8,000/month for 3 months. Leather Goods managers are deciding to offer their standard products or premium products at the pop-up space. The company accountant has provided the following information. Per Item Standard Premium 78.00 $ 134.00 32.00 $ 53.00 Average Sales Price Average Variable Costs Average Contribution Margin $ $ Additional fixed costs (excluding rent) are $1,500/month for 3 months. In the square footage of the pop-up, Leather Goods LLC can dispay and sell 540 standard products per month or 360 premium products per month. Calculate the average contribution margin and determine the expected operating income for each scenario. To maximize operating income, what type of products do you recommend Leather Goods sell in the pop-up? 3-Month Period Standard Premium 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 Expected Sales Expected Variable Costs Contribution Margin Fixed Costs Operating Income Recommend Product for Pop-Up ? Leather Goods LLC's 2020 income statement is shown below. Sales Variable Costs Contribution Margin Fixed Costs Operating Income Weekender Bags Wallets Backpacks Total $ 6,800,500 $ 2,140,000 $ 5,500,450 $ 14,440,950 2,720,200 1,348,200 2,805,230 6,873,630 4,080,300 791,800 2,695,221 7,567,321 2,025,000 1,077,000 1,890,900 4,992,900 $ 2,055,300 $ (285,200) $ 804,321 $ 2,574,421 Prepare a differential analysis below if Leather Goods LLC were to drop the wallets product line. Total fixed costs will not change if the company stops selling wallets. Weekender Bags Wallets Backpacks Total Sales Variable Costs Contribution Margin Fixed Costs Operating Income 48 49 Leather Goods short-term goal in this decision is to maximize operating income. Do you recommend the company drop wallets from their product mix? ? A B C D E F G I J K L M N 0 3 4 5 6 Leather Goods LLC uses a 9% rate of return to evaluate capital projects. The cash flows for the first 10 years of each project are shown below. Project A-Leather Goods LLC managers are considering offering purses. To offer purses they will need to invest $88,000 in a new machinery to produce and sell purses. The annual expected cash inflows from the sales of purses is $10,000. Project B - Leather Goods LLC managers are considering expanding the production of weekender bags. To expand the volume of weekender bags they will need to invest $94,000 to expand their current assembly line. The annual expected cash inflows from the additional sales of weekender bags is $15,000. Project C-Leather Goods LLC managers are considering opening a new retail space. The retail space will require a capital outlay of $228,000. The annual expected cash inflows from the opening of the retail space is $32,000. Project A Project B Project 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Capital Outlay 12/31/22 12/31/23 12/31/24 12/31/25 12/31/26 12/31/27 12/31/28 12/31/29 12/31/30 12/31/31 Payback (Round to One Decimal Place) IRR (Show as a %) 32 33 34 35 36 Which project do you recommend Leather Goods LLC managers invest in? ? 37 38 39 40 41 42 43 44 45 46 47 48 49 50 Product Mix Capital Projects +
