Start with the partial model in the file attached.
Calculation 2 Auto Sum Linked call Fill Paste Format 16 8 . Merge & Center $ % 200 20 Conditional Format Check Cell Explanatory Input Insert Delete Format Sort & Formatting as Table Clear Filter P28 + XV fx C D E F G H M S 1, Sales, labor, and KM adjustment factor 0 70 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 $115,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,000 $65,000 $70,000 $30,000 $35,000 22 23 Forecasted Sales 24 Sales (gross) 25 26 Collections 27 During month of sale 28 During 1st month after sale 29 During 2nd month after sale 30 Total collections 31 32 Purchases 33 Labor and raw materials 34 Payments for labor and raw materials 35 36 Payments 37 Payments for labor and raw materials 38 General and administrative salaries 39 Lease payments 40 Miscellaneous expenses 41 Income tax payments 42 Design studio payment 43 Total payments 44 45 Net Cash Flows 46 Cash on hand at start of forecast period 47 Net cash flow (NCF): Total collections - Total payments 48 Cumulative NCF: Prior month cumulative + this month's NCF Build a Model Ready 182%Calculation 2 AutoSum Merge & Center Conditional F Check Cell Explanatory - Linked Cell Note Fill Formatting as Table Delete Format Clear Sort & P28 Filter D E S 45 Net Cash Flows 46 Cash on hand at start of forecast period 47 Net cash flow (NCF): Total collections - Total payments 48 Cumulative NCF: Prior month cumulative + this month's NCF 49 50 Cash Surplus (or Loan Requirement) Target cash balance 52 Surplus cash or loan needed: Cum NCF - Target cash 53 54 Max. Loan 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 60 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 65 sales dropped off, this would have an effect on the required loan. Do a sensitivity analysis that shows the effects of these two 66 factors on the max loan requirement. Assume the purchases of labor and raw material also vary by the sales adjustment 67 factor. 68 69 Answer: 70 71 72 73 74 75 76 77 78 Build a Model 182% ReadyExcel File Edit View Insert Format Tools Data Window Help 4 4 1X 100% [27 Sat 13:46 Q Workbook1 Q . Search Sheet Home Insert Page Layout Formulas Data Review View '+ Share K Cut Calibri (Body) * 12 . A- A = = = 29 30 Wrap Text General Normal Bad Good Neutral Calculation AutoSum L Copy Fill Paste Format BIUTY S. A. Merge & Center $ % 2 08 08 Conditional Format Check Cell Explanatory ... Input Linked Cell Note Insert Delete Format Sort & Formatting as Table Clear Filter X fx C D E F G H K L M Sales Labor and Raw materials May, 2020 $75,000 80,000 June, 2020 $115,000 $75,000 July, 2020 $145,000 $105,000 August, 2020 $125,000 $85,000 September, 2020 $120,000 $65,000 October, 2020 $95,000 $70,000 November, 2020 $75,000 $30,000 December, 2020 $55,000 $35,000 January, 2021 $45,000 N/A 22 23 24 Sheet1 + Ready Average: 82058.82353 Count: 29 Sum: 1395000 0 - - + 207%A B C D E F G H J K L M 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, Lease payments $7,000 the percent for collections during month Target cash balance $30,000 after sale is automatically changed so General and administrative salaries $25,000 that 100% of sales are collected during Depreciation charges $8,000 the three-month period. Income tax payments (Sep & Dec) $30,000 Miscellaneous expenses $5,000 New office suite payment (Oct) $95,000 Cash on hand July 1 $70,000 Sales, labor, and RM adjustment factor 0% a. . Prepare a monthly cash budget for the last six months of the year. May June July August September October November December, January Original sales estimates $75,000 $115,000 $145,000 $125,000 $120,000 $95,000 $75,000 $55,000 $45,000 Original labor and raw mat. estimates $80,000 $75,000 $105,000 $85,000 $65,000 $70,000 $30,000 $35,000 Forecasted Sales
