I need to replicate part h by changing these numbers:
- 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.
This is the answer for question h, without changing the numbers. Please replicate this using the numbers as mentioned above. Provide a screenshot of the Excel Spreadsheet, formulas, and step-by-step explanation please. Thank you!
I have worked on it but I am unsure if this is correct? Please provide a screenshot as above. Thanks!
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 100 63 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 A x fx Sales Cost of sales Gross profit Before Plan After Plan Change Formulas (Replicated) (After plan-Before plan) $10,000,000.00 $896,000.00 $11,200,000.00 1200000.00 $6,300,000.00 $564,480.00 $7,056,000.00 756000.00 $3,700,000.00 -$2,352,000.00 $4,144,000.00 444000.00 Employee salaries Profit before fixed charge $800,000.00 2900000.00 $71,680.00 -3036320.00 $896,000.00 3248000.00 $96,000.00 348000.00 $ $ $ $ Less Implicit inventory costs* Profit after fixed charges 189,000.00 2711000 211,680.00 3036320 22,680.00 325320 -11200000 10000000 63% 6300000 896000 0.12 189000 0 52920 11200000 63% 7056000 Inventory cost calculation Sales Cost of sale % cost of sale $ Inventory turnover Total inventory Inventory fixed charge % Inventory fixed charge $ 1200000 63% 756000 24 :4 -4 1575000 12% 189000 1764000 12% 211680 189000 12% 22680 0
