Financial Management

MC Pvt. Ltd. has the following:

Actual Sales: Nov, 2020: $60,000 Dec, 2020: $64,000 Cash Balance Dec 31, 2020: $4000 The firm forecasts the following monthly sales ($): Jan, 2021 65,000 Feb, 2021 70,000 Mar, 2021 72,500 Apr, 2021 May, 2021 80,000 Jun, 2021 78,750 76,250 For A/C Receivables on goods sold, for the firm: 50% of monthly credit sales are collected within the same month of sale 45% of monthly credit sales are collected in the following month of sale 5% of monthly credit sales are uncollectible The goods have a 25% sales mark-up and are purchased one month prior based on expected sales For A/C Payables on goods purchased, for the firm: 40% of purchases are paid within the same month of purchase 60% of purchases are paid in the following month of purchase Salaries and wages are $12,000 per month Annual Rent for Warehouse is $60,000 payable quarterly in advance in Jan. Apr, Jul, and Oct Need to keep a minimum monthly cash balance as safety net of $2,000 Monthly cash shortages are met by cash borrowings @ 3% per month Monthly cash surplus (excess) are invested @ 1% per month Requirements: Prepare monthly cash budget using Excel for the six-month period Jan-Jun, 2021 Submit your work with your summary analysis