Old MathJax webview

Attached are the problem statement in the first pic , the second pic is a clear shot for the table in the first pic, and the last pic is for the discounts formulas that needs to be calculated.

The first photo shows the problem statement and the required outputs which are ( NPV, IRR, Payback period and ROI) to divide if the initiative worth investment or not.

THE second pic shows an Excel file that represent the needed parameters with their amounts to do the right formulas that will lead to find the requirements in the problem statement ( NPV, IRR, Payback period and ROI) to divide if the initiative worth investment or not.

THE following picture is an extra information about the problem statements

Here are some clear photos from the excel file ( the parameters table, some formulas for discount factor, comulative cash flow)

Currently the company has an average demand to deliver 2000 packages everyday which is expected to grow at 10% per month. The blue-collar workers can currently manage 20 packages each (sorting and loading) which can be increased to 30 packages per worker with the new facility and sorting machine. Further, the mailman at the company currently spends 3hrs in the warehouse due to sorting and loading activities and only 4hrs delivering packages. This leads to not all packages being delivered on the same day and delayed packages attract a penalty for the company at $10 per delayed package. This can be reduced to Owaiting time in the facility and full 7 hours can be utilized on delivering the packages with the introduction of the new system. However, the benefits come with following cost: 1. Extra rent for the new warehouse 2. Machine installation and monthly running cost 3. Route Optimization software cost Full benefit and value to be realized in 5 months. Net gain to be calculated based on the benefits in the next 5 months Improvement initiative related KPI's baseline: No. of packages delayed at present (total penalty cost) 800 packages delayed daily at present out of 2000 average daily packages (total monthly penalty - $240,000) Total workers employed and wages required at present - 100 workers required at present for 2000 average daily packages (Total monthly wage - $200,000) Based on the provided data, develop a business case and calculate the NPV, IRR, Payback period and ROI to decide whether the initiative is worth investment or not? Use the following data to solve the problem and remember it is just for 5 months. Unit Packages per day Parameter Average Orders per day Growth Blue-Colar Worker Current capacity (no of packages handled by 1 worker) After implementation- Blue-cofar worker capacity Cost of each blue colar worker Penalty per packet No. of Packagaes delivered per hour by the malman No. of mailman Number of hours mailman spend sorting and loading their veicules Number of hours delivering Work Journey Cost of capital (WACC) Amount 2000 10% 20 30 $2,000 $10 Packages per day Packages per day Dollar Dollar Packages per hour 50 3 4 7 hours hours hours 1096 Old Rent New Rent Sortation Machine Machine running Cost Optimization analytics tool $500,000 550.000 $30,000 per month $60.000 per month Jone time) per month $25.000 per month Unit Packages per day Parameter Average Orders per day Growth Blue-Collar Worker Current capacity (no. of packages handied by 1 worker) After implementation-Blue-collar worker capacity Cost of each blue collar worker Penalty per packet No. of Packagaes delivered per hour by the mailman No. of mailman Number of hours mailman spend sorting and loading their veicules Number of hours delivering Work Journey Cost of capital (WACC) Amount 2000 10% 20 30 $2,000 $10 6 50 Packages per day Packages per day Dollar Dollar Packages per hour 3 4 7 hours hours hours 10% Costs Old Rent New Rent Sortation Machine Machine running Cost Optimization analytics tool $500,000 $50,000 $30,000 per month $60,000 per month (one time) per month $25.000 per month Discount Factor 1/wace year 0.91 0.83 0.75 0.88 0.62 For NPV Calculation but can directly be done using inbuilt excel function for NPV For NPV Calculation but can directly be done using inbuilt excel function for NPV cash flow of the year discount factor Discounted Cash Flow Cumulative Net Cash Flow Payback Period Calculation $0 SO SO SO SO so so SO $0 SO 2.00 Currently the company has an average demand to deliver 2000 packages everyday which is expected to grow at 10% per month. The blue-collar workers can currently manage 20 packages each (sorting and loading) which can be increased to 30 packages per worker with the new facility and sorting machine. Further, the mailman at the company currently spends 3hrs in the warehouse due to sorting and loading activities and only 4hrs delivering packages. This leads to not all packages being delivered on the same day and delayed packages attract a penalty for the company at $10 per delayed package. This can be reduced to Owaiting time in the facility and full 7 hours can be utilized on delivering the packages with the introduction of the new system. However, the benefits come with following cost: 1. Extra rent for the new warehouse 2. Machine installation and monthly running cost 3. Route Optimization software cost Based, on the provided data, develop a business case and calculate the NPV, IRR, Payback period and ROI to decide whether the initiative is worth investment or not? Use the following data to solve the problem and remember it is just for 5 months. Unit Paoages per day Parameter Average Orders per day Growth Blue-Collar Worker Current capacity (no. of pedages handled by 1 work After implementation-Blue-collar worker capacity Cost of each blue collar worker Penalty per packet No. of Papagaes delivered per hour by the mailman No. of mailman Number of hours mailman spend sorting and loading their veicules Number of houn delivering Work Journey Cost of capital ( WACC) Amount 2000 10% 20 30 $2.000 $10 6 50 Pedages per day Padages per day Dollar Dollar Packages per hour 3 4 7 hours hours hours 109 Costs Old Rent New Rent Sortation Machine Machine running Cost Ootimization analytics tool $500.000 550.000 $30.000 per month $60,000 per month one time) per month $25.000 per month alender month Parameter Formula Month Month 1 1 Month 2 Month 3 Month Months Total Discount Factor 1/(wace year 0.91 0.83 For NPV Calculation but can directly be done using inbuilt excel function for NPV For NPV Calouation but can directly be done using inbuilt excel function for NPV 0.75 0.00 0.42 cash flow of the year discount factor Discounted Cash Flow Cumulative Net Cash Flow Payback Period Calculation $0 50 $0 $0 0 $0 50 0 50 $0 0 50 $0 0 50 $0 0.00 Extra information Required Yes . Yes Filing Area Improvement initiative Objective Description of the change components for the Improvement initiative Yes Value added from the Improvement initiative Request Improve the last mile delivery Improve worker & mailman's efficiency 1. Move to a new warehouse with better layout facility (This will result in increased rent but improve the sorting and loading efficiency for workers) 2. Install a sorting machine that will help reduce the sorting time for packages 3. Use a route optimization software that will help increase the efficiency of the mailman and allow him to utilize full 7 hours of his duty time to deliver letters 1. Reduced cost due to less blue-collar worker requirement with increased efficiency 2. Improved last mile delivery with more packages being delivered on time (hence attracting lower penalty) 1. Extra rent for moving to new warehouse facility 2. Investment in sorting machine ($500k) and monthly maintenance cost 3. Monthly subscription of a route optimization software Full benefit and value to be realized in 5 months. Net gain to be calculated based on the benefits in the next 5 months Reduced no. of delayed packages (lower penalty) Wage reduction due to less blue collar workers requirement Customer satisfaction (Qualitative) No. of packages delayed at present (total penalty cost) - 800 packages delayed daily at present out of 2000 average daily packages (total monthly penalty - $240,000) Total workers employed and wages required at present - 100 workers required at present for 2000 average daily packages (Total monthly wage - $200,000) Improvement initiative requirements Yes Yes Yes Improvement initiative timelines Improvement initiative related KPI's Improvement initiative related KPI's baseline Yes Unit Packages per day Parameter Average Orders per day Growth Blue-Collar Worker Current capacity (no. of packages handled by 1 worker) After implementation-Blue-collar worker capacity Cost of each blue collar worker Penalty per packet No. of Packagaes delivered per hour by the mailman No. of mailman Number of hours mailman spend sorting and loading their veicules Number of hours delivering Work Journey Cost of capital (WACC) Amount 2000 10% 20 30 $2.000 $10 6 50 Packages per day Packages per day Dollar Dollar Packages per hour 3 4 7 hours hours hours 10% Old Rent New Rent Sortation Machine Machine running Cost Optimization analytics tool $30,000 per month $60,000 per month (one time) Costs $500,000 $50,000 per month $25,000 per month Discount Factor 1/Waco year 0.91 0.83 0.75 0.88 0.62 For NPV Calculation but can directly be done using inbuilt excel function for NPV For NF r NPV Caloulation but can directly be done using inbuilt excel function for NPV cash flow of the year discount factor Discounted Cash Flow Cumulative Net Cash Flow Payback Period Calculation SO SO SO SO SO so $0 SO SO SO 2.00