AutoSave (C off) H Building a Model - Marvel Renovations (1) - Excel Search File Home Insert Draw Page Layout Formulas Data Review View Help Acrobat La From Text/CSV [ Recent Sources Queries & Connections III & Clear LEEFrom Web Get La Existing Connections Refresh Properties Stock Sort Filter Reapply Currencies Text to Data From Table/Range EV All Edit Links Advanced Columns COB Get & Transform Data Queries & Connections Data Types Sort & Filter Data Tools A64 X V fx e. If its customers began to pay late, this would slow down collections and thus increase the required loan amount. Also, if sales dropped of B C D E F G H K L M 2 Input Data Collections during month of sale 20% Collections during month after sale 60% Note: When the percent collected during Collections during second month after sale 25% the second month after sale is changed, the 6 Lease payments $7,000 percent for collections during month after Target cash balance $30,000 sale is automatically changed so that 100% 8 General and administrative salaries $25,000 of sales are collected during the three- 9 Depreciation charges $8,000 month period. 10 Income tax payments (Sep & Dec) $30,000 11 Miscellaneous expenses $5,000 12 New office suite payment (Oct) $95,000 13 Cash on hand July 1 $70,000 14 15 Sales, labor, and RM adjustment factor 0% 16 17 a. Prepare a monthly cash budget for the last six months of the year. 18 19 May June July August September October November December January 20 Original sales estimates $75,000 $1 15,000 $145,000 $125,000 $120,000 $95,000 $75,000 $55,000 $45,000 21 Original labor and raw mat. estimates $80,000 $75,000 $105,000 $85,00 $65,000 $70,000 $30,000 $35,000 22 23 Forecasted Sales 24 Sales (gross) $75,000 $115,000 $145,000 $125,000 $120,000 $95,000 $75,000 $55,000 $45,000 25 26 Collections 27 During month of sale 29000 25000 24000 5000 15000 11000 28 During 1st month after sale 69000 87000 75000 78000 6370 48750 29 During 2nd month after sale 18750 28750 36250 31250 30000 23750 30 Total collections $116,750 $140,750 $135,250 $114,250 $108,70 $83,500 31 27 Purchases Scenario Summary | Scenario Summary 2 Build a Model + Select destination and press ENTER or choose Paste Average: 3988.420588 Count: 23 Sum: 67803.15 we're starting the ad Type here to search D W N XV% v Building a Model - Marvel Renovations (1) - Excel File Home Insert Draw Page Layout Formulas Data Review View Help Acrobat Ell $0\" Arial v 10 v A" AV E E E 59' V 33 Wrap Text General v w @ @ Paste COPY v rr: Conditional Format as Cell V Format Painter B I Q V if: 01 V A v E E E E E E Merge 8! Center v $ v % 9 '33 '93 Formatting V Table V Styles V Clipboard Er Font Er Alignment Er Number 5 Styles L65 v r A A | B | C | D | E | F | G | H | r l J l K | L Zi Collections 2; During month of sale 29000 25000 24000 5000 15000 11000 2i During 1st month after sale 69000 87000 75000 78000 63700 48750 21 During 2nd month after sale 18750 28750 36250 31250 30000 23750 31 Total collections $116,750 $140,750 $135,250 $114,250 $108,700 $83,500 3i Bi Purchases Bi Labor and raw materials $80,000 $75,000 $105,000 $85,000 $65,000 $70,000 $30,000 $35,000 3i Payments for labor and raw materials $75,000 $105,000 $65,000 $65,000 $70,000 $30,000 35 3: Payments Si Payments for labor and raw materials $75,000 $105,000 $85,000 $65,000 $70,000 $30,000 31 General and administrative salaries 25,000 25,000 25,000 25,000 25,000 25,000 3: Lease payments 7,000 7,000 7,000 7,000 7,000 7,000 43 Miscellaneous expenses 5,000 5,000 5,000 5,000 5,000 5,000 4i Income tax payments 30,000 0 30,000 g Design studio payment 95,000 g Total payments $112,000 $142,000 $152,000 $197,000 $107,000 $97,000 4: 43 Net Caslr Flows 43 Cash on hand at start of forecast period $70,000 $74,750 $73,500 $56,750 ($26,000) ($24,300) Net cash ow (NCF): Total collections Total payments $4,750 ($1,250) ($16,750) ($82,750) $1,700 ($13,500) 4E Cumulative NCF: Prior month cumulative + this month's NCF $74,750 $73,500 $56,750 ($26,000) ($24,300) ($37,800) Ag 51 Cash Surplus (or Loan Requirement) 1 Target cash balance $30,000 $30,000 $30,000 $30,000 $30,000 $30,000 51 Surplus cash or loan needed: Cum NCF Target cash $44,750 $43,500 $26,750 ($56,000) ($54,300) ($67,800) 53 5: Max- Loan Si 53 b. Prepare an estimate of the required nancing (or excess tunds)that is, the amount of money | Scenario Summary 2 l Scenario Summary 3 l Scenario PivotTable Build a Model @ 1|: Ready ' AutoSave . Off ) Building a Model - Marvel Renovations (1) - Excel Search File Home Insert Draw Page Layout Formulas Data Review View Help Acrobat & Cut Arial * 10 YA A ae Wrap Text General Paste [ Copy EE Z E Merge & Center % " Conditional Format Painter BIUTY Av $ 100 Formatting Clipboard Font Alignment Number Sty K77 X Y fix A B C D E F G H I J 54 Max. Loan $67,800 55 56 b. Prepare an estimate of the required financing (or excess funds)-that is, the amount of money 57 Marvel's Renovations will need to borrow (or will have available to invest)-for each month during that period. 58 59 TIME FRAME May June July August September October November December January 60 $ BORROWED $44,750 $43,500 $26,750 $56,000 $54,300 $67,800 61 62 63 64 e. If its customers began to pay late, this would slow down collections and thus increase the required loan amount. Also, if sales 65 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 66 max loan requirement. Assume the purchases of labor and raw material also vary by the sales adjustment factor. 67 68 69 Answer: 70 71 Change MAXIMUM Loan Required 72 in Sales % Collections in 2nd Month 73 $67,800 74 -100% 75 .75%% 76 -50% 77 -25% 78 0% 79 25% 80 50% 81 75% 82 100% 83 84 Scenario Summary 2 Scenario Summary 3 Scenario PivotTable Build a Model + Ready Type here to search O 9 D W