MGMT-6005 Group Assignment #5 Refer Chapter 15 - Problems 15-10 and 15-11 (pp. 563-564) Topic: Cash Budgeting, Financial Planning and Forecasting Refer Monthly Sales (Problem 15-10 on page 563), monthly expenses, receivables and payments on page 564 Assignment Requirements: a) Prepare monthly cash budget (May 2016 - Jan 2017) showing surplus or deficits for each month b) Undertake a sensitivity analysis shown in problem 15-11 (page 564) with 10% decline monthly sales and late payment by customers leading to delay in collections (receivables). Show the impact on surplus or deficits for each month Submit your analysis in a Word Document along with Excel worksheets showing your calculations. Please Note: Excel Worksheets calculations supporting your analysis are worth 50% of the grade for this assignment. 15-10 CASH BUDGETING Helen Bowers, owner of Helen's Fashion Designs, is planning to request a line of credit from her bank. She has estimated the following sales forecasts for the firm for parts of 2016 and 2017. May 2016 June July August September October November December January 2017 $180,000 180,000 360,000 540,000 720,000 360,000 360,000 90,000 180,000 Estimates regarding payments obtained from the credit department are as follows: collected within the month of sale, 10%; collected the month following the sale, 75%; collected the second month following the sale, 15%. Payments for labor and raw materials are made the month after these services were provided. Here are the estimated costs of labor plus raw materials: May 2016 June July August September October November December $90,000 90,000 126,000 882,000 306,000 234,000 162,000 90,000 General and administrative salaries are approximately $27,000 a month. Lease payments under long-term leases are $9,000 a month. Depreciation charges are $36,000 a month. Miscellaneous expenses are $2,700 a month. Income tax payments of $63,000 are due in September and December. A progress payment of $180,000 on a new design studio must be paid in October. Cash on hand on July 1 will be $132,000, and a minimum cash balance of $90,000 should be maintained throughout the cash budget period