S33 A B fx C D E F H K L M N P 2 A retail chain operates five stores in different locations. The chain has collected the following information from each of its stores last month (see the table below). Additionally, the chain has indicated that: 3 (1) $50,000 in monthly corporate overhead (fixed cost) should be distributed evenly among the five stores (i.e., $10,000 each); 4 5 (2) Labor cost per employee a month = $15,000; (3) Monthly rent cost per square foot of floor area = $5. 6 7 8 Answer the questions below, Q1 Q3. Calculations along with Excel formulas MUST be shown within cells to earn the points. You will NOT get points by simply showing the numbers for the answer even if they are correct!! 9 10 11 Store 12 Store 1 Sales Revenue ($) $102,000 Number of Employees 4 Floor Area (sq. ft) 1900 Inventory Cost ($) $25,000 Rent Cost ($) $9,500 Overhead Expense $10,000 Labor Cost/employee/month Monthly rent/sqft $15,000 $5 13 Store 2 $120,000 4 1800 $25,000 $9,000 $10,000 Labor Productivity Labor Cost $25,500 $30,000 Total Input Cost $60,000 $104,500 $60,000 $104,000 Multifactor Productivity 0.97608 1.15385 Threshold for Monthly Rent/sqft $7,000 $3.68 14 Store 3 $150,000 6 2200 $35,000 $11,000 $10,000 $25,000 $90,000 $146,000 1.02740 15 Store 4 $128,000 6 $130,000 4 2600 1900 $18,000 $32,000 $13,000 $9,500 $10,000 $10,000 $21,333 $32,500 $90,000 $131,000 $60,000 $111,500 0.97710 $10,000 $3.85 1.16592 16 Store 5 17 18 19 20 Q1 (1 pts): Alice, the HR manager, is interested in evaluating labor productivity across stores for last month. Labor productivity is defined by Alice, using sales revenue as output and the number of employees as input. Identify the store with the best labor productivity and calculate its labor productivity (rounded to five decimal places if not a whole number). 21 Store 1, 2, 3, 4, or 5? 22 23 Store 5 Labor productivity 32,500.00 Answer 24 25 26 27 28 29 30 31 Q2 (1 pts): Bob, the financial analyst, aims to assess multifactor productivity across stores for last month. Multifactor productivity is defined by using sales revenue for output as well as labor cost, rent cost for the floor area, overhead expense, and inventory cost for inputs. Identify the store with the best multifactor productivity and calculate its multifactor productivity (rounded to five decimal places if not a whole number). 32 Multifactor Store 1, 2, 3, 4, or 5? 33 productivity 34 Store 5 1.16592 Answer 35 36 37 38 39 40 41 42 43 Q3 (2 pts): The bottom line for the management is every store should at least achieve break-even, meaning one dollar input cost generates at least one dollar revenue (i.e., a multifactor productivity of at least 1). If this bottom line was not met last month, the management plans to negotiate the rent for floor area with the landlord of these 5 stores to sign a longer term lease because their lease expires very soon. 44 Assuming sale revenues and labor, inventory, and overhead costs for the near future will remain about the same as last month, calculate the threshold (precise to the first decimal place) of the monthly rent per square foot for floor area for meeting the above bottom line. That is, as long as the floor rent is negotiated and lowered to this threshold rent, the above bottom line just starts to be met. If all stores met the above bottom line last month already, no calculation is needed and just answer "N/A" below. 45 46 47 48 Threshold for monthly rent per square foot for Answer 49 floor are a $3.69 and $3.85 Explan how you calculated the 50 51 52 53 54 55 56 57 58 The "Threshold for monthly rent per square foot for floor area" refers to the maximum rent per square foot that allows a store to achieve a multifactor productivity (MFP) of exactly 1, meaning the store breaks even with $1 of revenue for each $1 of input cost. Since Stores 1 and 4 are below this threshold, they require rent adjustments to bring their MFP to 1. The other stores (2, 3, and 5) already meet or exceed the bottom line, so no rent adjustment calculations are necessary for them. Therefore, the focus on Stores 1 and 4 is correct, as these are the only stores where a rent negotiation would be needed to meet the management's bottom line.