Angelina Dunkin, owner of Angelina?s Scents, is planning to request a line of credit from her bank.
Question:
Angelina Dunkin, owner of Angelina?s Scents, is planning to request a line of credit from her bank. She has estimated the following sales forecasts for the firm for parts of 2019 and 2020:
Mo/yr | Sales |
May-19 | $180,000 |
Jun-19 | $180,000 |
Jul-19 | $360,000 |
Aug-19 | $540,000 |
Sep-19 | $720,000 |
Oct-19 | $360,000 |
Nov-19 | $360,000 |
Dec-19 | $90,000 |
Jan-20 | $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 salaries:
Mo/yr | Costs of Labor and Raw Material |
May-19 | $90,000 |
Jun-19 | $90,000 |
Jul-19 | $126,000 |
Aug-19 | $882,000 |
Sep-19 | $306,000 |
Oct-19 | $234,000 |
Nov-19 | $162,000 |
Dec-19 | $90,000 |
General and administrative salaries are approximately $27,000 a month. Lease payments under long-term leases are $9,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.
In Excel, prepare a monthly cash budget for the last 6 months of 2019.
Finance Applications and Theory
ISBN: 978-0077861681
3rd edition
Authors: Marcia Cornett, Troy Adair