If its customers began to pay late, this would slow down collections and thus increase the required loan amount. Also, if sales dropped off, this would have an effect on the required loan amount. Perform a sensitivity analysis that shows the effects of these two factors on the maximum loan requirement.
| Input Data | | | | | | | | |
| Collections during month of sale | | 20% | | | | |
| Collections during month after sale | | 60% | | | | |
| Collections during second month after sale | | 25% | | | | |
| | | | | |
| Sales, labor, and RM adjustment factor | | 0% |
| Surplus cash or loan needed: | | $14,750 | $33,500 | $36,750 | ($43,000) | ($8,000) | ($66,250) |
| | | | | | | | | | | |
| | | Max. Loan | | $66,250 | Found by using MIN of cash surplus/loan | | | | | |
I have used What IF Analysis to create this table multiple times but this answer seems incorrect. I used collections during second month for my row input and sales, labor adjustment factor for my column input. 66,250 is the max loan and I showed how that was found for data calculation. What is the proper what-if analysis for this data?
Building a Model Marvel Renovations - Excel A Libby Stenger ta File Home Insert Page Layout Formulas Data Review View Help Acrobat Tell me what you want to do 2. Share 2 Solver From Access From Web From Other From Text Sources - Ge External Data * * EE 212 Sort Show Queries errom Table New Query- Do Recent Sources Get Transform Connections E Properties Refresh All- Edit links Connections y Clear Taneapply Advanced Existing Connections Filter Flash Fill Consolidate 5 Remove Duplicates Relationships Text to Columns Data Validation - Manage Data Model Dale Tanks Group Ungroup Subtotal What if Forecast Analysis - Sheet Forecast Sort & Fiter Cutline K69 E M N 0 P P 0 R A B D 52 Surplus cash or loan needed: Cum NCF - Target cash G $33,500 $14,750 H $36,750 1 (543,000) J ($8,000) ($66,250) Max. Loan $66,250 55 56 57 58 39 60 61 e. If its customers began to pay late, this would slow down collections and thus increase the required loan amount. Also, if sales dropped off, this would have an effect on the required loan. Do a sensitivity analysis that shows the effects of these two factors on the max loan requirement. Assume the purchases of labor and raw material also vary by the sales adjustment factor. . Answer: 64 65 66 Change In Sales $66.250 -100% -75% -50% -25% 0% 25% 60% 75% 100% 67 68 69 70 71 72 73 74 75 76 72 78 79 0% $ $ 90,000 3 90.000 $ 90,000 $ 90,000 $ 90,000 $ 90,000 $ 90,000 $ 90,000 $ 90,000 15% $ 75,750 5 $ 75,750 $ 75.750 5 $ 75,750 $ $ 75,750 $ 75,750 $ 75,750 $75,790 $ $ 75,750 Loan Amount Collections 30% 45% 60% $ 61,500 $ $ 47,250 $ 33,000 $ 61,500 $ $ 47.250 $ 33,000 $ 61,500 $ 47,250 $ 33,000 $ 61,500 $ 47.250 5 33.000 $ $ 61,500 $ 47,250 $ 33.000 $ 61,500 $ 47,790 $ 33.000 $ 5 61,500 $ 47.250 $ 33,000 $ 61,900 $ 47,290 $ 33.000 $ 61,500 $ 47.250 $ 33,000 90% $4,500 $ 4,500 $ 4,500 $ 4,500 $ 4,500 75% $ 10,750 $ 18,750 $ 10,750 $ 18,750 $10.750 $ 18,750 $ $ 18,750 $18.750 $ 18,750 $ 4.500 $ 4,500 $ 4,500 54.500 81 82 Build a Model 1009 Type here to search OBI P 10:56 PM 4/12/2021 Building a Model Marvel Renovations - Excel A Libby Stenger ta File Home Insert Page Layout Formulas Data Review View Help Acrobat Tell me what you want to do 2. Share 2 Solver From Access From Web From Other From Text Sources - Ge External Data * * EE 212 Sort Show Queries errom Table New Query- Do Recent Sources Get Transform Connections E Properties Refresh All- Edit links Connections y Clear Taneapply Advanced Existing Connections Filter Flash Fill Consolidate 5 Remove Duplicates Relationships Text to Columns Data Validation - Manage Data Model Dale Tanks Group Ungroup Subtotal What if Forecast Analysis - Sheet Forecast Sort & Fiter Cutline K69 E M N 0 P P 0 R A B D 52 Surplus cash or loan needed: Cum NCF - Target cash G $33,500 $14,750 H $36,750 1 (543,000) J ($8,000) ($66,250) Max. Loan $66,250 55 56 57 58 39 60 61 e. If its customers began to pay late, this would slow down collections and thus increase the required loan amount. Also, if sales dropped off, this would have an effect on the required loan. Do a sensitivity analysis that shows the effects of these two factors on the max loan requirement. Assume the purchases of labor and raw material also vary by the sales adjustment factor. . Answer: 64 65 66 Change In Sales $66.250 -100% -75% -50% -25% 0% 25% 60% 75% 100% 67 68 69 70 71 72 73 74 75 76 72 78 79 0% $ $ 90,000 3 90.000 $ 90,000 $ 90,000 $ 90,000 $ 90,000 $ 90,000 $ 90,000 $ 90,000 15% $ 75,750 5 $ 75,750 $ 75.750 5 $ 75,750 $ $ 75,750 $ 75,750 $ 75,750 $75,790 $ $ 75,750 Loan Amount Collections 30% 45% 60% $ 61,500 $ $ 47,250 $ 33,000 $ 61,500 $ $ 47.250 $ 33,000 $ 61,500 $ 47,250 $ 33,000 $ 61,500 $ 47.250 5 33.000 $ $ 61,500 $ 47,250 $ 33.000 $ 61,500 $ 47,790 $ 33.000 $ 5 61,500 $ 47.250 $ 33,000 $ 61,900 $ 47,290 $ 33.000 $ 61,500 $ 47.250 $ 33,000 90% $4,500 $ 4,500 $ 4,500 $ 4,500 $ 4,500 75% $ 10,750 $ 18,750 $ 10,750 $ 18,750 $10.750 $ 18,750 $ $ 18,750 $18.750 $ 18,750 $ 4.500 $ 4,500 $ 4,500 54.500 81 82 Build a Model 1009 Type here to search OBI P 10:56 PM 4/12/2021
