In October 2019, Renee finally realized her dream of opening her own business, Renee's Cakes and Ice Cream, LLC. Renee is the sole owner but she does employee part-time help. Renee's Cakes and Ice Cream, LLC offers customers a variety of cakes, including a luscious triple chocolate fudge cake (Renee's favorite) and a scrumptious carrot cake. Ice cream flavors vary month to month. Renee's store is strategically located in a retail plaza with a handful of local restaurants. Owing to the foot traffic, Renee has done very little advertising. Renee had a good quarter; demand was fueled by the holiday season. While cakes sales dominated ice cream sales Renee believes that the summer months will yield higher ice cream sales with lower cake sales. Over the 4th quarter Renee has observed the following trends: 0 85% of sales are derived from credit card sales. The gross profit per product is 60% and 70% for cakes and ice cream, respectively. Sufficient inventory should be maintained to meet 50% of the subsequent month's estimated sales. . Renee picked a great time to open her store! Halloween, Thanksgiving, Christmas, and New Year's really drove the demand for high-end cakes. But after the start of the new year, demand dropped. Renee had anticipated a drop in sales in January when everyone's goal is to lose weight but was expecting sales to pick up again around Valentine's Day. However, Renee could not have anticipated a global pandemic crippling the United States economy. Renee realized that with so much uncertainty, her top priority was to prepare a 2020 monthly forecast. Sales in 2020 are expected to be as follows: Jan Feb Mar Apr May Jun Jul Aug Sep Oct $ 41,083.05 (80% cake / 20% Ice Cream) $ 31,887.38 (80% cake / 20% Ice Cream) $ 36,336.76 (80% cake / 20% Ice Cream) $ 9,899.88 (80% cake / 20% Ice Cream) $ 10,663.78 (70% cake / 30% Ice Cream) $ 27,696.10 (60% cake / 40% Ice Cream) $ 28,250.02 (40% cake / 60% Ice Cream) $ 28,815.02 (40% cake / 60% Ice Cream) $ 29,679.47 (50% cake / 50% Ice Cream) $ 37,099.34 (60% cake / 40% Ice Cream) $ 46,374.18 (80% cake / 20% Ice Cream) $ 47,765.40 (80% cake / 20% Ice Cream) Nov Dec Sales for the early part of 2021 are estimated as follows: Jan Feb $ 45,000.00 (80% cake / 20% Ice Cream) $ 34,000.00 (80% ke / 20% Ice Cream) Renee does not believe there will be significant changes in costs in 2020. She believes that the gross margin on the cakes and ice cream should be consistent, the mix of cash to credit card purchase will remain constant, and she believes she can keep her employee full-time. Renee has gathered the following cost data: 1. Merchant fees are 3% on all sales made via credit card. 2. Lease agreement (see attachment) 3. Advertising in a local periodical is $120 monthly starting February 2020. 4. Renee expects to purchase $100 of office supplies in the last month of each quarter. 5. Renee intends on taking a salary of $4,000 per month. Any profits will be reinvested back into the business. Renee has hired a full-time employee who is paid $12 an hour and is expected to work 8 hours a day, 5 days a week (Assume 4 weeks per month). Thus far, Renee has not had any trouble meeting her payroll demands. The employee pays FICA but is exempt from federal and state income taxes. 6. Depreciation expense is expected to be $9,643 a year and is booked annually. 7. Renee signed a contract for two phone lines (one for the business line and one for the credit card machine). Each line is $35 a month. 8. The City of Tucson charges $75 a month for water and trash, Southwest Gas charges $35 a month for gas, and the TEP rate schedule is attached. Renee anticipates that the store, which utilizes a single-phase service, will use 1,500 kWh and 2,000 kWh per month during the winter and summer months, respectively. Renee knows that to calculate the cost of electricity she will need to add the basic service charge, energy charge, and the power supply charge. 9. To ensure the store had sufficient cash flow, Renee's Cakes and Ice Cream, LLC took out a line of credit in the amount of $50,000 on November 1, 2019, with an interest rate of 5% due on the 15th of the firs nonth of each quarter. No principal payment is required as long as the account is kept current. Renee used $15,000 to get the business up and running. Interest payments are made the first of the month for the interest accrued the prior month. Renee knows that some months may operate at a loss so she plans on borrowing additional funds at the start of each month that she expects to suffer a loss. She will round the loss up to the nearest thousand for simplicity. No principal will be repaid until December 31, 2020, at which point Renee will pay up to $25,000, as long as the payment doesn't result in Renee's cash balance dropping below $10,000. 10. Additional operating expenses are estimated to be $3,200 a month. 11. The 2019 balance sheet is attached. 12. Inventory is purchased on account at the start of the month, terms n/30. 13. Renee anticipates her December 31, 2020 cash balance will be $71,784.36, not including any payment on the line of credit. Required (use excel to answer the following questions. Use formulas in the cells, do not use a calculator. worksheets should be properly formatted and free from grammatical and mathematical errors. Create one tab for each question) 1. Prepare a forecasted contribution formated income statement (monthly) for the year 2020. 2. Prepare a forecasted balance sheet as of December 31, 2020. 3. Calculate the breakeven point in sales for 2020 using the estimated expenses. 4. Calculate the margin of safety ratio for the 2020. In October 2019, Renee finally realized her dream of opening her own business, Renee's Cakes and Ice Cream, LLC. Renee is the sole owner but she does employee part-time help. Renee's Cakes and Ice Cream, LLC offers customers a variety of cakes, including a luscious triple chocolate fudge cake (Renee's favorite) and a scrumptious carrot cake. Ice cream flavors vary month to month. Renee's store is strategically located in a retail plaza with a handful of local restaurants. Owing to the foot traffic, Renee has done very little advertising. Renee had a good quarter; demand was fueled by the holiday season. While cakes sales dominated ice cream sales Renee believes that the summer months will yield higher ice cream sales with lower cake sales. Over the 4th quarter Renee has observed the following trends: 0 85% of sales are derived from credit card sales. The gross profit per product is 60% and 70% for cakes and ice cream, respectively. Sufficient inventory should be maintained to meet 50% of the subsequent month's estimated sales. . Renee picked a great time to open her store! Halloween, Thanksgiving, Christmas, and New Year's really drove the demand for high-end cakes. But after the start of the new year, demand dropped. Renee had anticipated a drop in sales in January when everyone's goal is to lose weight but was expecting sales to pick up again around Valentine's Day. However, Renee could not have anticipated a global pandemic crippling the United States economy. Renee realized that with so much uncertainty, her top priority was to prepare a 2020 monthly forecast. Sales in 2020 are expected to be as follows: Jan Feb Mar Apr May Jun Jul Aug Sep Oct $ 41,083.05 (80% cake / 20% Ice Cream) $ 31,887.38 (80% cake / 20% Ice Cream) $ 36,336.76 (80% cake / 20% Ice Cream) $ 9,899.88 (80% cake / 20% Ice Cream) $ 10,663.78 (70% cake / 30% Ice Cream) $ 27,696.10 (60% cake / 40% Ice Cream) $ 28,250.02 (40% cake / 60% Ice Cream) $ 28,815.02 (40% cake / 60% Ice Cream) $ 29,679.47 (50% cake / 50% Ice Cream) $ 37,099.34 (60% cake / 40% Ice Cream) $ 46,374.18 (80% cake / 20% Ice Cream) $ 47,765.40 (80% cake / 20% Ice Cream) Nov Dec Sales for the early part of 2021 are estimated as follows: Jan Feb $ 45,000.00 (80% cake / 20% Ice Cream) $ 34,000.00 (80% ke / 20% Ice Cream) Renee does not believe there will be significant changes in costs in 2020. She believes that the gross margin on the cakes and ice cream should be consistent, the mix of cash to credit card purchase will remain constant, and she believes she can keep her employee full-time. Renee has gathered the following cost data: 1. Merchant fees are 3% on all sales made via credit card. 2. Lease agreement (see attachment) 3. Advertising in a local periodical is $120 monthly starting February 2020. 4. Renee expects to purchase $100 of office supplies in the last month of each quarter. 5. Renee intends on taking a salary of $4,000 per month. Any profits will be reinvested back into the business. Renee has hired a full-time employee who is paid $12 an hour and is expected to work 8 hours a day, 5 days a week (Assume 4 weeks per month). Thus far, Renee has not had any trouble meeting her payroll demands. The employee pays FICA but is exempt from federal and state income taxes. 6. Depreciation expense is expected to be $9,643 a year and is booked annually. 7. Renee signed a contract for two phone lines (one for the business line and one for the credit card machine). Each line is $35 a month. 8. The City of Tucson charges $75 a month for water and trash, Southwest Gas charges $35 a month for gas, and the TEP rate schedule is attached. Renee anticipates that the store, which utilizes a single-phase service, will use 1,500 kWh and 2,000 kWh per month during the winter and summer months, respectively. Renee knows that to calculate the cost of electricity she will need to add the basic service charge, energy charge, and the power supply charge. 9. To ensure the store had sufficient cash flow, Renee's Cakes and Ice Cream, LLC took out a line of credit in the amount of $50,000 on November 1, 2019, with an interest rate of 5% due on the 15th of the firs nonth of each quarter. No principal payment is required as long as the account is kept current. Renee used $15,000 to get the business up and running. Interest payments are made the first of the month for the interest accrued the prior month. Renee knows that some months may operate at a loss so she plans on borrowing additional funds at the start of each month that she expects to suffer a loss. She will round the loss up to the nearest thousand for simplicity. No principal will be repaid until December 31, 2020, at which point Renee will pay up to $25,000, as long as the payment doesn't result in Renee's cash balance dropping below $10,000. 10. Additional operating expenses are estimated to be $3,200 a month. 11. The 2019 balance sheet is attached. 12. Inventory is purchased on account at the start of the month, terms n/30. 13. Renee anticipates her December 31, 2020 cash balance will be $71,784.36, not including any payment on the line of credit. Required (use excel to answer the following questions. Use formulas in the cells, do not use a calculator. worksheets should be properly formatted and free from grammatical and mathematical errors. Create one tab for each question) 1. Prepare a forecasted contribution formated income statement (monthly) for the year 2020. 2. Prepare a forecasted balance sheet as of December 31, 2020. 3. Calculate the breakeven point in sales for 2020 using the estimated expenses. 4. Calculate the margin of safety ratio for the 2020