I need to replicate part h using these numbers (do not use any other numbers) but these:
- Sales increase by 12%
- Wage expense increased from an average of about 8% to about 11% of sales.
- Inventory fixed charge 15%
- Everything else is the same.
I already have the correct answer for part h. Please provide an Excel Spreadsheet and the formulas needed in a step-by-step explanation method. Thanks!
Below is information about this problem -->
This is the correct answer for part H using the case numbers, please replicate it using the above numbers -->
h. Using the information on sales and expenses for a typical store, provide an analysis of the additional store profit contributed by the plan. Assume that it costs 12% a year to carry the added inventory. 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 tumover ratio is 4. it suggests a monthly tumover 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 100 Sales...... . Cost of goods sold... Gross profit. Employee salaries............ Profit before fixed charges...... 10,000,000 6,300,000 3,700,000 800,000 2,900,000 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 Sales Cost of sales Gross profit Before Plan $10,000,000 6,300,000 3,700,000 After Plan $10,800,000 6,804,000 3,996,000 Change $800,000 504,000 296,000 Employee salaries Profit before fixed charges 800,000 2,900,000 1,080,000 2,916,000 280,000 16,000 Less implicit inventory costs* Profit after fixed charges 189,000 $2.711,000 204,120 $ 2,711,880 15,120 880 $ Inventory costs calculation: Sales Cost of sales % Cost of sales $ Inventory turnover Total inventory Inventory fixed charge % Inventory fixed charge $ $10,000,000 63% $ 6,300,000 4 $ 1,575,000 12% $ 189,000 $10,800,000 63% $ 6,804,000 +4 $ 1,701,000 12% $ 204.120 $800,000 63% $504,000 +4 $126,000 12% $ 15,120
