Use the following estimated information for the Barkery to perform the analyses: Revenues $300,000 Cost of goods sold 60% of revenues Salaries and wages 22% of revenues Additional costs: Operating Costs - $12,600 Depreciation -$2,100 Bad debts expense-3 % of 70 % of revenues Interest expense - $1,125 . REQUIRED; On the basis of the facts as given above, prepare the following schedules using a spreadsheet software package: A. Prepare an income statement based on the information above. B. The Barkery would like to be more profitable. Use Goal Seek in Excel to determine the following changes, Each situation is independent of the others. You will need to reset after each situation 1. How much would the cost of goods sold percentage need to be for net income to be $40,000? 2. How much would the cost of goods sold percentage need to be for net income to be $50,000? 3. Assume original percentages, how much would the salaries percentage need to be for net income to be $50,000? C. GO BACK TO THE ORIGINAL DATA. Use Solver in Excel to determine the following assuming these constraints (COGS not more than 70% or less than 50%, Salaries not more than 30 % or less than 10 % ) : 1. Assuming both must change, what percentages would the COGS and the salaries percentages have to be in order to get $40,000 net income? 2. Assuming both must change, what percentages would the COGS and the salaries percentages have to be in order to get $50,000 net income? 3. What is the maximum net income The Barkery can achieve with the above constraints? 4. What is the minimum net income The Barkery can achieve with the above constraints? D.GO BACK TO THE ORIGINAL DATA. following: Use Solver in Excel to determine the 1. Assuming COGS = 60% and Salaries = 22%, how much would revenues have to be to earn $40,000 net income? 220%, how much would 2. Assuming COGS60% and Salaries revenues have to be to earn $50,000 net income? 3. Assuming COGS 55% and Salaries- 22% , how much would revenues have to be to earn $50,000 net income? 4. Assuming COGS-50% and Salaries-22% , how much would revenues have to be to earn $50,000 net income? 5. Assuming COGS-50 % and Salaries-25% , how much would revenues have to be to earn $50,000 net income? E. GO BACK TO THE ORIGINAL DATA. Use Scenarios in Excel. Prepare and print a Scenarios Summary Report for net income with the following options: (Each case is independent.) 1. Best case- Sales twice the original amount. 2. Worst case- Sales are as much as original amount. 3. Lower COGS 55% instead of 60 %. 4. Even Lower COGS-50 % instead of 60 % 5. Change two-Sales and COGS: Sales increase by 30 % of original amount, COGS 55%. 6. Change three Sales, COGS, and Salaries: Sales increase by 30% of original amount, COGS 50% , higher salaries 25% . (1) For your Scenarios Summary Report, be sure to format your Scenarios Summary by putting titles in for the variables. (2) Conditional format your net income by highlighting with your choice of color any incomes above $50,000. Also format your net income with another color choice for those incomes below the current net income. Check figures: B. 1. 57%. C. 1. 58%, 22%. D. 1. $351,101. Print and turn in: 1 -Income statement from Part A 2-Answers only to Parts B-D 3-Scenario Summary Report from Part E