please complete case question #4 with excel. also if you can send a seperste pic of the excel work showing formulas as well, that would be GOLDEN and worth 5 thumbs up. :))

Scandinavian Styles Peter Nielsen and Jens Andersen moved to the United States as sales represen- tatives for a Danish furniture manufacturer. Nielsen had the southeast territory and Andersen had the midwest territory, but they met several times a year and talked frequently on the phone to coordinate shipments. After several years of selling to retail stores that carried numerous styles, the two decided to start their own store specializing in Scandinavian furniture. Nielsen and Andersen continued in their jobs for another year while they or ganized the business. They knew the demographic characteristics of Scandina- vian furniture buyers from company studies and their own experience. Upper middle income and under forty-five best described the group. Thus, they chose to locate in an upper-middle-income section of Springfield. Knowing that furni ture was hardly a convenience good, and knowing they needed adequate display shopping center that had fallen on hard times due to the opening of a large shop ping center several blocks away. The rent was low, and the space was adequate for their display needs. They thought they could generate customer traffic through The major decisions of Nielsen and Andersen proved to be right. Despite advertising more effectively than by counting on an expensive location, few mistakes and rocky periods, the business thrived. Within two years, was business essary to place large orders. Most of the furniture came "knocked down" and Case Problem 399 required final assembly, Thus, both storage and work space was needed. They handled the space needs temporarily by renting a small warehouse. This, how- ever, was unsatisfactory as many customers wanted to take their purchases with them. A separate warehouse also created control problems. By the time they had been in business 5 years, the partners decided to build a new store, giving them adequate display, storage, and assembly space at one location. Again, the deci- sion proved to be profitable. Looking toward further growth, Nielsen and Andersen decided they would have to expand outside of the Springfield area. They decided on Oak Hill, a sub- urb in Andersen's old sales territory. The primary appeal of this location was that Andersen knew the area and market better than any other. Andersen would run the new store while Nielsen would stay in the old store. They decided to evalu- ate the expansion opportunity using a 10-year horizon; style changes or balance of payments problems could end their business. A developer was in the process of building some store space that would be within the right rent range for a furniture store. Space could be had for $10 per square foot per year, and a 10-year lease was required. Nielsen and Andersen could cancel the lease at any time, but there would be a penalty of 20 percent of the remaining lease payments. The location looked good, but the question was how much space to rent. Nielsen and Andersen agreed that 10,000 square feet was the optimal sales space. Andersen was in favor of taking 15,000 square feet of space so they would have 5,000 square feet for a storage and assembly area on site. Nielsen wanted to take a more conservative approach, using weekly drop-shipments from the Springfield location to deliver inventory to Oak Hill. The distance was over 500 miles, and this would add approximately 15 percent to the cost of the furniture, but risk would be reduced substantially, and the need for 5,000 square feet of space could be eliminated. Andersen pointed out that if the store was successful, they would quite likely find themselves facing the necessity of buying their way out of the lease within 2 years to get warehouse and assembly space. An 8-year lease on 15,000 square feet would probably cost $12 a square foot by then. Nielsen was more concerned about buying out of a 15,000-square-foot lease after 1 year if the store was un successful. Both partners agreed to study the alternatives some more over the weekend and to make a decision on Monday. From their past experience and observations, Nielsen and Andersen believed the big risks in opening a store of this type occurred in the first year. They pro- jected sales in the second year to be double those in the first year and predicted little growth beyond that. For purposes of analysis, the partners decided to con centrate on two cases with regard to first-year sales: weak and successful. Weak sales would be $250,000 the first year, and successful sales would be $600,000 The probabilities were estimated to be 7 for success and 3 for weak sales The primary up-front costs were promotion and miscellaneous expenses of $30,000 without the warehouse/assembly space and $50,000 with the ware- house/assembly space. These expenses would result in an immediate 25 percent tax savings. Inventory would cost $200,000 with the warehouse'assembly area and $100,000 without. It was estimated that the inventory could be liquidated at cost if or when the store was closed. There would be no accounts receivable because most customers used credit cards, and arrangements would be made with a finance company for those needing credit. Other current assets and cur- rent liabilities would also be negligible. Depreciation and noncash expenses would be minimal, so income and cash flow would be the same. As a general guideline, Nielsen and Andersen estimated a cost of goods sold with on-site assembly at 60 percent of sales. Other variable costs would be 10 percent of sales. They estimated fixed costs other than rent of $4 a year for every square foot of space in either sales space or warehouse/assembly space. The partners faced 28 percent tax rates and used a 10 percent required return in their analysis. CASE QUESTIONS 1. Compute a net income break-even point for the smaller and larger facilities. 2. Find the sales level (after the first year) that will result in a net present value of so. Remember that sales the first year will be half of those after the first year. 3. Prepare a decision tree analysis of the alternatives. 4. Prepare a graphical sensitivity analysis showing the relationship between sales level and net present value for each size alternative. 5. Should they lease space of 10,000 or 15,000 square feet? Why? Scandinavian Styles Peter Nielsen and Jens Andersen moved to the United States as sales represen- tatives for a Danish furniture manufacturer. Nielsen had the southeast territory and Andersen had the midwest territory, but they met several times a year and talked frequently on the phone to coordinate shipments. After several years of selling to retail stores that carried numerous styles, the two decided to start their own store specializing in Scandinavian furniture. Nielsen and Andersen continued in their jobs for another year while they or ganized the business. They knew the demographic characteristics of Scandina- vian furniture buyers from company studies and their own experience. Upper middle income and under forty-five best described the group. Thus, they chose to locate in an upper-middle-income section of Springfield. Knowing that furni ture was hardly a convenience good, and knowing they needed adequate display shopping center that had fallen on hard times due to the opening of a large shop ping center several blocks away. The rent was low, and the space was adequate for their display needs. They thought they could generate customer traffic through The major decisions of Nielsen and Andersen proved to be right. Despite advertising more effectively than by counting on an expensive location, few mistakes and rocky periods, the business thrived. Within two years, was business essary to place large orders. Most of the furniture came "knocked down" and Case Problem 399 required final assembly, Thus, both storage and work space was needed. They handled the space needs temporarily by renting a small warehouse. This, how- ever, was unsatisfactory as many customers wanted to take their purchases with them. A separate warehouse also created control problems. By the time they had been in business 5 years, the partners decided to build a new store, giving them adequate display, storage, and assembly space at one location. Again, the deci- sion proved to be profitable. Looking toward further growth, Nielsen and Andersen decided they would have to expand outside of the Springfield area. They decided on Oak Hill, a sub- urb in Andersen's old sales territory. The primary appeal of this location was that Andersen knew the area and market better than any other. Andersen would run the new store while Nielsen would stay in the old store. They decided to evalu- ate the expansion opportunity using a 10-year horizon; style changes or balance of payments problems could end their business. A developer was in the process of building some store space that would be within the right rent range for a furniture store. Space could be had for $10 per square foot per year, and a 10-year lease was required. Nielsen and Andersen could cancel the lease at any time, but there would be a penalty of 20 percent of the remaining lease payments. The location looked good, but the question was how much space to rent. Nielsen and Andersen agreed that 10,000 square feet was the optimal sales space. Andersen was in favor of taking 15,000 square feet of space so they would have 5,000 square feet for a storage and assembly area on site. Nielsen wanted to take a more conservative approach, using weekly drop-shipments from the Springfield location to deliver inventory to Oak Hill. The distance was over 500 miles, and this would add approximately 15 percent to the cost of the furniture, but risk would be reduced substantially, and the need for 5,000 square feet of space could be eliminated. Andersen pointed out that if the store was successful, they would quite likely find themselves facing the necessity of buying their way out of the lease within 2 years to get warehouse and assembly space. An 8-year lease on 15,000 square feet would probably cost $12 a square foot by then. Nielsen was more concerned about buying out of a 15,000-square-foot lease after 1 year if the store was un successful. Both partners agreed to study the alternatives some more over the weekend and to make a decision on Monday. From their past experience and observations, Nielsen and Andersen believed the big risks in opening a store of this type occurred in the first year. They pro- jected sales in the second year to be double those in the first year and predicted little growth beyond that. For purposes of analysis, the partners decided to con centrate on two cases with regard to first-year sales: weak and successful. Weak sales would be $250,000 the first year, and successful sales would be $600,000 The probabilities were estimated to be 7 for success and 3 for weak sales The primary up-front costs were promotion and miscellaneous expenses of $30,000 without the warehouse/assembly space and $50,000 with the ware- house/assembly space. These expenses would result in an immediate 25 percent tax savings. Inventory would cost $200,000 with the warehouse'assembly area and $100,000 without. It was estimated that the inventory could be liquidated at cost if or when the store was closed. There would be no accounts receivable because most customers used credit cards, and arrangements would be made with a finance company for those needing credit. Other current assets and cur- rent liabilities would also be negligible. Depreciation and noncash expenses would be minimal, so income and cash flow would be the same. As a general guideline, Nielsen and Andersen estimated a cost of goods sold with on-site assembly at 60 percent of sales. Other variable costs would be 10 percent of sales. They estimated fixed costs other than rent of $4 a year for every square foot of space in either sales space or warehouse/assembly space. The partners faced 28 percent tax rates and used a 10 percent required return in their analysis. CASE QUESTIONS 1. Compute a net income break-even point for the smaller and larger facilities. 2. Find the sales level (after the first year) that will result in a net present value of so. Remember that sales the first year will be half of those after the first year. 3. Prepare a decision tree analysis of the alternatives. 4. Prepare a graphical sensitivity analysis showing the relationship between sales level and net present value for each size alternative. 5. Should they lease space of 10,000 or 15,000 square feet? Why