Angelina Dunkin, owner of Angelinas 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. PLEASE SHOW WORK

1 luput Data 2 Collections during month of sale 3 Collections during month after sale 4 Collections during second month after sale 5 Lease payments 6 General and administrative salaries 7 Depreciation charges 8 Income tax payments (Sep & Dec) 9 Miscellaneous expenses 10 New design studio payment 11 Cash on hand July 1 12 Minimum cash balance 13 Sales adjustment factor ++ 0% 14 Sales 15 Labor & Jaw materials B 16 17 18 Sales (gross) May Juue July Augnst September October November December January 20 21 22 Prepare a monthly cash budget for the last 6 months of 2 THE SECTION BELOW SHOULD CONTAIN ONLY FORMULAS & CELL REFERENCES 23 The Cash Budget 24 May Junc July August September October November December January 25 Collections and purchases worksheer 26 Sales (gross) $360,000 $540,000 $720,000 $360,000 $360,000 $90,000 27 Collections 28 During mouth of sale 29 During 1st month after sale 30 During 2nd mouth after sale 31 Total collections 32 Purchases Sheet1 cacy Acessibility: Investigate -- TE! B 2:49 PM F G L M NO R $360,000 $540,000 $720,000 $360,000 $360,000 $90,000 A B D 25 Collections and purchases worksheet 26 Sales (gross) 27 Collections 28 During month of sale 29 During ist month after sale 30 During 2nd mouth after sule 31 Total collections 32 Purchases 33 Labor and raw materials 34 Payments for labor and ruw materials 35 36 Caxh gain or loss for month 37 Collections 38 Payments for labor and raw materials 39 General and administrative swartes 40 Lease payments 41 Miscellaneous expenses Sheet1 CI 40 Lease payments 41 Miscellaneous expenses 42 Income tax payments 43 Design studio payment 44 Total payments 45 Net cash gain (loss) during month 46 47 Loan requirement or cash surplus 48 Cash at start of month 49 Cumulative cash 50 Target cash balance 51 Cumulative surplus cash or loans 52 outstanding to maintain $90,000 target cash balance 53 54 Max. Loan SO 55