Revenues - $330,000 Cost of goods sold 55 % of revenues Salaries and wages 26% of revenues Additional costs: Operating Costs - $12,300 Depreciation - $2,160 Bad debts expense - 2% of 60% of revenues Interest expense - $875 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 othersYou 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 $60,000? 2. How much would the cost of goods sold percentage need to be for net income to be $75,000? 3. Assume original percentages, how much would the salaries percentage need to be for net income to be $75,000? C. GO BACK TO THE ORIGINAL DATA. Use Solver in Excel to determine the following assuming these constraints (COGS not more than 60 % or less than 40%, 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 $50,000 net income? 2. Assuming both must change, what percentages would the COGS and the salaries percentages have to be in order to get $75,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 = 55% and Salaries = 26%, how much would revenues have to be to earn $50,000 net income? 26 % , how much would 2. Assuming COGS 55% and Salaries revenues have to be to earn $75,000 net income? 3. Assuming COGS 50 % and Salaries 27 % , how much would revenues have to be to earn $75,000 net income? 4. Assuming COGS- 45% and Salaries 26 % , how much would revenues have to be to earn $75,000 net income? 5. Assuming COGS-45 % and Salaries 27 % , how much would revenues have to be to earn $75,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 50 % instead of 55 % . 4. Even Lower COGS-45 % instead of 55 % . 5. Change two- Sales and COGS: Sales increase by 30 % of original amount, COGS 50%. 6. Change three - Sales, COGS, and Salaries: Sales increase by 30 % of original amount, COGS 45 % , higher salaries 27 % . (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 $75,000. Also format your net income with another color choice for those incomes below the current net income. Check figures: B. 1. 50%, C. 1. 53 % , 26%. D. 1. $367,051. Print and turn in: 1-Income statement from Part A 2-Answers only to Parts B-D 3-Scenario Summary Report from Part E