I need to answer this using Excel. I have answered an identical problem, but I need to work with new numbers now (essentially I must replicate it with new numbers).

Here is the information --

are reported in Figure 1 page 17-36. Month 25 is denoted as the rollout month, the month the incen- tive plan began. Expense Analysis: You then plotted wage expense/sales, cost of goods sold/sales, and inventory turnover for the twenty stores for the 24 months preceding the plan and the first 24 months after plan implementation. After pulling out seasonal effects these monthly series are presented in Figures 2, 3 and 4. If the plan has no impact on these expenses then you would expect no dramatic change in the series around month 25. Figure 2 plots (wage expense in month t/sales in month t) Figure 3 plots (cost of goods sold in month t/sales in month t) Figure 4 plots "annual" turnover computed as (12 x cost of goods sold in month t/inventory at beginning of month t) For example, if monthly cost of sales is $100 and the annual inventory turnover ratio is 4. it suggests a monthly turnover of 0.333 with the firm holding an average inventory of $300 throughout the year. (Note that a monthly inventory turnover of .333 implies an annual turnover of 4 (from 12 x 0.333). Financial Report for Store: A typical annual income statement for a pre-plan Ladbreck store before fixed charges, taxes and incidentals looks as follows. Total Percent Sales. ............ .. . 10,000,000 100 Cost of goods sold . . 6,300,000 63 Gross profit. . . .. . . . .. . . . ...... 3,700,000 37 Employee salaries. ...... 800,000 8 Profit before fixed charges. . ..... ... ... ... .... .. 2,900,000 29 A store also has substantial charges for rent, management salaries, insurance, etc. but they are fixed with respect to the incentive plan.MA17-35. continued h. Before Plan After Plan Change Sales $10,000,000 $10,800,000 $800,000 Cost of sales 6,300,000 6,804,000 504,000 Gross profit 3,700,000 3,996,000 296,000 Employee salaries 800,000 1,080,000 280,000 Profit before fixed charges 2,900,000 2,916,000 16,000 Less implicit inventory costs* 189,000 204, 120 15,120 Profit after fixed charges $2,711,000 $ 2,711,880 $ 880 Inventory costs calculation: Sales $10,000,000 $10,800,000 $800,000 Cost of sales % 63% 63% 63% Cost of sales $ $ 6,300,000 $ 6,804,000 $504,000 Inventory turnover + 4 + 4 + 4 Total inventory $ 1,575,000 $ 1,701,000 $126,000 Inventory fixed charge % 12% 12% 12% Inventory fixed charge $ $ 189,000 $ 204,120 $ 15,12017-35. Assessing the Impact of an Incentive Plan Overview Ladbrecks is a major department store with fifty retail outlets. The company's stores compete with outlets run by companies such as Nordstrom, Macys, Bloomingdales, and Saks Fifth Avenue. During the early nineties the company decided that providing excellent customer service was the key ingredi- ent for success in the retail industry. Therefore, during the mid 1990s the company implemented an incentive plan for its sales associates in twenty of its stores. Your job is to assess the financial impact of the plan and to provide a recommendation to management to continue or discontinue the plan based on your findings. Incentives in Retail The past decade has evidenced a concerted effort by many firms to empower and motivate employees to improve performance. A recent New York Times article reported that more and more firms are of- fering bonus plans to hourly workers. An Ernst and Young survey of the retail industry indicates that virtually all department stores currently offer incentive programs such as straight commissions, base salary plus commission, and quota bonus programs. Although these programs can add to payroll costs, the survey respondents indicated that they believe these plans have contributed to major improve- ments in customer service.Company's Background Ladbrecks was founded by members of the Ladbreck family in the 1880s. The first store opened under the name Ladbreck Dry Goods. Growth was fueled through acquisitions as the industry consolidated during the 1960s. Over this hundred-year period, sales associates were paid a fixed hourly wage. Raises were based on seniority. Sales associates were expected to be neat and courteous to customers. The advent of specialty stores and the stated intention of an upscale west coast retailer to begin opening stores in the Midwest concerned Ladbreck's management. Building on its history of excellence in cus- tomer service, the company initiated its performance-based incentive plan to support its stated firm-wide strategy of "customer emphasis" with "employee empowerment." Management expected it to result in further enhancement of customer service and, consequently, in an increase in sales generated at its stores. Incentive Plan The plan was implemented in stores sequentially as company managers intended to examine and evaluate the plan's impact on sales and profitability. Initially, the firm selected one store from a group of similar stores in the same general area to begin the implementation. By the end of 1994, ten stores had implemented the plan. In 1995, ten more stores implemented the plan, bringing the total to 20 out of a total of 50. The performance-based incentive plan is best described as a bonus program. At the time of the plan's implementation, sales associates received little in the form of annual merit increases, and promotions were rare. The bonus payment became the only significant reward for high performance. Each week sales associates are paid a base hourly rate times hours worked. In addition, under the plan sales asso- ciates could increase their compensation by receiving a bonus at the end of each quarter. The contract provides sales-force personnel with a cash bonus only if the actual quarterly sales generated by the employee exceed a quarterly sales goal. Individualized pre-specified sales goals were established for each employee based only on the individual's base hourly rate, hours worked and a multiplier (mul- tiplier = 1/bonus rate). The bonus is computed as a fixed percentage of the excess sales (actual sales minus a pre-specified sales goal) by the employee in a quarter (see Exhibit 1). Employee's Bonus = 0.08 (Employee's actual sales for quarter - Where employee's Employee's employee's targeted sales for quarter) targeted sales for quarter hourly wage X Hours worked in quarter x 12.5 Senior managers regarded the incentive plan as a major change for the firm and its sales force. Man- agement expected that the new incentive scheme would motivate many changes in employee behavior that would enhance customer service. Sales associates were now expected to build a client base to generate repeat sales. Actions consistent with this approach include developing and updating customer address lists (including details of their needs and preferences), writing thank you notes and contacting customers about upcoming sales and new merchandise that matched their preferences.Consultant's Task Management decided to call you in to provide an independent assessment. While the company thought that sales had increased with the plan's implementation, the human resource department did not know exactly how to quantify the plan's impact on sales and expenses. It suspected that employee salaries, cost of goods sold, and inventory carrying costs, as well as sales, may have changed due to the plan's implementation. You, therefore, requested information on these financial variables. Sales Analysis: Because each of the twenty stores implemented the plan at different dates, and store sales fluctuated greatly with the seasons and the economy, you could not simply plot store sales. In- stead, for each of the twenty stores, you picked another Ladbreck store as a control and computed for 48 months the following series of monthly sales: [(Plan Store Sales in Month t + Plan Store Sales in Month t-24) - Percent Change in Sales = (Control Store Sales in Month t + Control Store Sales in Month t-24)] X 100 The plan's implementation was denoted as month 25, so you had 24 months prior to the plan and 24 months after the plan. Averages were then taken for the twenty stores. If the control procedure worked then you expected that the first 24 months of the series would fluctuate around zero. The actual results