Please provide all calculations in a format that can be translated to Excel. I h
ID: 2396926 • Letter: P
Question
Please provide all calculations in a format that can be translated to Excel. I have the cash budget figured out, however, I cannot figure out the operating reserve or liquidity.
ASSIGNMENT 3.2. DEVELOPING A CASH BUDGET Develop a cash budget based on the following information about a preschool: Revenue Tuition fee: $100,000 paid monthly Event income: $50,000 by Thanksgiving Foundation support: $200,000 semiannually, May and November Copyrighted mate Liquidity and Managing Cash Flow 67 Expense Salary and wages: $80,000 monthly Rent and insurance: $100,000 in January Supply and food: $10,000 monthly Administration and fundraising: $10,000 monthly Assume the board of directors does not allow for short-term borrowing and that the cash safety margin is $80,000. How much operating reserve or liquidity should the school have to embark on the preschool program?Explanation / Answer
CASH BUDGET January February March April May June July August September October November December TOTAL Cash receipts; Tuition fees 100,000 100,000 100,000 100,000 100,000 100,000 100,000 100,000 100,000 100,000 100,000 100,000 1,200,000 Event income 50,000 - - - - - - - - - - - 50,000 Foundation support - - - - 200,000 - - - - - 200,000 - 400,000 Total cash receipts ( A) 150,000 100,000 100,000 100,000 300,000 100,000 100,000 100,000 100,000 100,000 300,000 100,000 1,650,000 Cash payments; Salary and wages 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000 960,000 Rent and Insurance 100,000 - - - - - - - - - - - 100,000 Supply and food 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 120,000 Administration and fundraising 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 120,000 Total cash payments (B) 200,000 100,000 100,000 100,000 100,000 100,000 100,000 100,000 100,000 100,000 100,000 100,000 1,300,000 Net cash flow ( C ) = ( A ) - (B) (50,000) - - - 200,000 - - - - - 200,000 - 350,000 Cash safety margin 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000 Exess/(shortage) of cash (130,000) (80,000) (80,000) (80,000) 120,000 (80,000) (80,000) (80,000) (80,000) (80,000) 120,000 (80,000) cash shortage in the month of january is $ 130,000. If operating reserve available at the beginning of january, cash shortage of the entire year can be avoided Therefore, operating reserve shall be embarked by the school at the beginning of the year is $ 130,000 to maintain cash safety. So the begenning balance of cash in january is $ 130,000 Beginning balance of cash ( D ) 130,000 80,000 80,000 80,000 80,000 280,000 280,000 280,000 280,000 280,000 280,000 480,000 480,000 Ending Balance of cash (E ) = ( C ) + ( D ) 80,000 80,000 80,000 80,000 280,000 280,000 280,000 280,000 280,000 280,000 480,000 480,000 830,000 Note: here it is assumed that event income of $ 50,000 is received in the month of january.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.