Can I get some assistance?

Woodson's Hand Care and Cleaner is owned and operated by husband and wife, Eric and Heather Woodson. The company has been in operation for several years and manufacturers a special quality hand sanitizer that not only cleans but moisturizes. The company has been quite successful selling the hand sanitizer in a 10-fluid ounce pump over the past several months but is experiencing a slow decline in profits for the past quarter, according to reports generated by the administrative staff. Heather serves as the Chief Financial Officer for the Company but has only worked in the business for 1 years once their youngest child started first grade. Eric takes care of operations, logistics, etc. A business manager/accountant was hired part-time to assist the staff in carrying out the accounting and financial duties. The company had been consistently reporting net profits over the past several quarters but a decline in profits over the past couple of quarters is quite noticeable. The Woodsons are troubled by this performance and believe they need an expert to review the company's records and operations. Consequently, they have retained your consulting firm to perform research and evaluation and provide recommendations for improvement and recovery of profitable operations. There is also a need for an improved cash flow management strategy. Two distinct areas that you are investigating are manufacturing operations and cash flow management. Details are provided below for both areas. PART ONE: Manufacturing and Budgeting: The newly hired business manager/accountant prepared the following income statement for the quarter ended December 31, 2020: Woodson's Hand Care and Cleaner Income Statement For the Fourth Quarter Ended December 31, 2020 Sales Revenue (58,500 10-oz bottles @ $8/Bottle $ 468,000 Operating Expenses: Raw Material Purchases $ 158,400 Advertising expenses 64,000 Indirect labor 16,800 Direct Labor 114,000 Selling and Admin. Salaries 85,000 Utilities expense 9,600 Rent-Factory Building 36,000 Insurance expense 6,600 Depreciation-Factory Equipment 18,200 Depreciation Sales Equipment 27,000 Total Operating Expenses (485,600) NET INCOME (LOSS) $67,600) The Woodsons are greatly disturbed by these reported results. The company had been consistently reporting profit in prior quarters. Consequently, they have retained you as a consultant and has asked you to review the income statement and recommend any needed corrections and adjustments. You inquired and obtained the following additional data: A. Inventory Data October 1 December 31 Raw Material Work-in-Progress Finished Goods $ 9,600 19,200 18,000 $ 40,800 16,800 57,600 B. Seventy-five percent (75%) of the utility expense and fifty-five percent (55%) of the insurance expense are to be applied to the factory. The remaining amounts are to be considered selling and administrative expenses. Based on your assessment, the following schedule and financial statement are to be prepared and revised, respectively, in the proper format. 1. A detailed schedule of goods sold for the quarter ended December 31, 2020. 2. A corrected multi-step income statement for the quarter ended December 31, 2020. (See format in Exhibit #1) PART TWO: Cash flow and Financing Eric and Heather agree that there also is a need for better forecasting regarding the company's cash inflows and outflows. After a series of questions, you determined that (1) the company had not prepared a cash budget for several quarters and (2) is not up to date on its payables and receivables. You are going to prepare a forecasted income statement and a forecasted cash budget for the fourth quarter. Pertinent information needed has been collected and is outlined below. Pertinent Information: The business manager/accountant and Heather provided projections for the 1st quarter 2021 (January through March 2021) and other information outlined below: 1. Total sales 4th quarter 2020: 58,500 10-oz bottles; Sales price is $8/bottle 2. Total Sales for the 1st quarter 2021 are projected to increase 5% above the 4th quarter 2020 total sales due to an aggressive marketing program that began December 1, 2020. Total quarter sales per month are expected to be realized as follows: 40% in January, 35% in February, and 25% in March. The sales budgets expressed in sales dollars and in the number the number of bottles sold are as follows: Oct Nov Dec TOTAL QTR PROJECTED SALES REVENUE SALES $126,945 $177,723 $203,112 $507,780 SALES: PRICE X QUANTITY $126.945 $177,723 $203,112 $507,780 Number of Bottles Sold Oct Nov Dec TOTAL QTR Number of Bottles Sold 15.356 21,499 24,570 61,425 3. Monthly Sales are classified as follows and realized in corresponding percentage: a. Cash sales....... ..30% of total sales b. Credit sales.... ..70% of total sales 4. Monthly credit sales are collected as follows and in the corresponding proportion: a. Collected in the month of the sale, 50% b. Collected one month after the sale month, 30% C. Collected the second month after the sale month, 20% 5. Cost Classifications based on 4th quarter information: Costs Fixed Costs Variable Costs Raw Materials used in Manufacturing (See corrected COGS Schedule in Part 1, Required #1) $ ? $ 36,000 Actual Costs on 4th Quarter Income Statement Direct Labor 114,000 Factory Overhead: Rent-Factory Indirect Labor 16,800 Insurance - Factory (66 2/3%) Utility-Factory (75%) 5,400 Depreciation-factory building 3,630 1,800 18,200 General Administrative Advertising expense Selling & Administrative Wages and Salaries Utility-Selling & Administrative (25%) Insurance-Administrative (33 1/3%) Depreciation-Administrative 18,000 1,680 64,000 67,000 720 2,970 27,000 6. All factory overhead and administrative expenses (except depreciation) are paid in cash in the month the cost is incurred. 7. Direct labor is paid at the end of the month 8. Raw material purchases are paid as follows: 50% in the month of the purchase, 30% the month after the purchase, and 20% in the second month following the purchase. The raw materials' budget is as follows: Oct Nov Dec TOTAL QTR PROJECTED RAW MATERIAL PURCHASES $138,012 NEEDED FOR SALES DESIRED ENDING LESS: BEGINNING PURCHASES $34,503 8,626 (40,800) $2,329 $48,304 12,076 (8,626) $51,755 $55,205 13,801 (12,076) $56,930 $111,013 9. Additional monthly obligation paid in cash include: a. Property taxes, due February 27, 2021: $2,790 b. Employee payroll taxes due March 15, 2021: $900. You have determined that you are going to provide the following information to the Woodsons: a. Compute the unit cost of goods sold using 4th quarter information and the corrected cost of goods sold statement you prepared in Part One b. Prepare a projected traditional multi-step income statement for the 1st quarter using information in "a," above and the 4th quarter information on the table in "5" above (see the previous page). For proper formatting, see Exhibit 1 at the end of this document. C. A schedule of collections from credit sales for each month of the 1st quarter 2021. Previous months' credit sales are: November 2020 credit sales, $ 91,550 December 2020 credit sales, $86,210 d. A schedule of payment for raw materials for each month of the 4th quarter. Previous months' credit (on account) purchases are: November 2020 purchases on account, $56,560 December 2020 purchases on account, $52,800 e. A projected Cash Flow Budget for (a) the total 1st quarter 2021 and (b) for each month in the 1st h quarter 2021 based on the information provided above. Beginning cash balance on January 1, 2021 is $242,800. Additional presentation requirements: 1. Prepare a formal report for the Woodsons. You should use ONLY WORD and EXCEL. 2. Be sure the report is ORGANIZED. That is, be sure the report is clearly presently and EASY for the Woodsons to ready and understand. Do not submit any pdf files. 3. You can have a separate WORD file for narrative (i.e., discussion and small tables) and a separate EXCEL file for large tables, schedules, statement, etc. Be sure that all tables, schedules, statements, etc., are clearly identified (e.g., name and/or number) and adequately referenced in the WORD file and letter when/if discussed. 4. Your reports should have a cover page. 5. A brief letter indicating what is being submitted should be attached. The letter SHOULD NOT BE A PART OF THE REPORT. In the WORD file, put a blank page between the letter and the report. 6. You have the option to work with one business associate (classmate). Together you should provide the Woodsons with a high-quality report and recommendations. 7. You indicated to the Woodsons that the reports will be submitted by Friday, May 1, 2021, at the end of the day, 11:59 p.m. APPENDIX EXHIBIT #1: Sales Cost of Goods Sold Gross Profit Selling and Administrative: Advertising Selling/Admin. Salaries Utility-Adm Insurance Depreciation- Admin. Total Sell/Admin Expenses Net Income (Loss) Woodson's Hand Care and Cleaner is owned and operated by husband and wife, Eric and Heather Woodson. The company has been in operation for several years and manufacturers a special quality hand sanitizer that not only cleans but moisturizes. The company has been quite successful selling the hand sanitizer in a 10-fluid ounce pump over the past several months but is experiencing a slow decline in profits for the past quarter, according to reports generated by the administrative staff. Heather serves as the Chief Financial Officer for the Company but has only worked in the business for 1 years once their youngest child started first grade. Eric takes care of operations, logistics, etc. A business manager/accountant was hired part-time to assist the staff in carrying out the accounting and financial duties. The company had been consistently reporting net profits over the past several quarters but a decline in profits over the past couple of quarters is quite noticeable. The Woodsons are troubled by this performance and believe they need an expert to review the company's records and operations. Consequently, they have retained your consulting firm to perform research and evaluation and provide recommendations for improvement and recovery of profitable operations. There is also a need for an improved cash flow management strategy. Two distinct areas that you are investigating are manufacturing operations and cash flow management. Details are provided below for both areas. PART ONE: Manufacturing and Budgeting: The newly hired business manager/accountant prepared the following income statement for the quarter ended December 31, 2020: Woodson's Hand Care and Cleaner Income Statement For the Fourth Quarter Ended December 31, 2020 Sales Revenue (58,500 10-oz bottles @ $8/Bottle $ 468,000 Operating Expenses: Raw Material Purchases $ 158,400 Advertising expenses 64,000 Indirect labor 16,800 Direct Labor 114,000 Selling and Admin. Salaries 85,000 Utilities expense 9,600 Rent-Factory Building 36,000 Insurance expense 6,600 Depreciation-Factory Equipment 18,200 Depreciation Sales Equipment 27,000 Total Operating Expenses (485,600) NET INCOME (LOSS) $67,600) The Woodsons are greatly disturbed by these reported results. The company had been consistently reporting profit in prior quarters. Consequently, they have retained you as a consultant and has asked you to review the income statement and recommend any needed corrections and adjustments. You inquired and obtained the following additional data: A. Inventory Data October 1 December 31 Raw Material Work-in-Progress Finished Goods $ 9,600 19,200 18,000 $ 40,800 16,800 57,600 B. Seventy-five percent (75%) of the utility expense and fifty-five percent (55%) of the insurance expense are to be applied to the factory. The remaining amounts are to be considered selling and administrative expenses. Based on your assessment, the following schedule and financial statement are to be prepared and revised, respectively, in the proper format. 1. A detailed schedule of goods sold for the quarter ended December 31, 2020. 2. A corrected multi-step income statement for the quarter ended December 31, 2020. (See format in Exhibit #1) PART TWO: Cash flow and Financing Eric and Heather agree that there also is a need for better forecasting regarding the company's cash inflows and outflows. After a series of questions, you determined that (1) the company had not prepared a cash budget for several quarters and (2) is not up to date on its payables and receivables. You are going to prepare a forecasted income statement and a forecasted cash budget for the fourth quarter. Pertinent information needed has been collected and is outlined below. Pertinent Information: The business manager/accountant and Heather provided projections for the 1st quarter 2021 (January through March 2021) and other information outlined below: 1. Total sales 4th quarter 2020: 58,500 10-oz bottles; Sales price is $8/bottle 2. Total Sales for the 1st quarter 2021 are projected to increase 5% above the 4th quarter 2020 total sales due to an aggressive marketing program that began December 1, 2020. Total quarter sales per month are expected to be realized as follows: 40% in January, 35% in February, and 25% in March. The sales budgets expressed in sales dollars and in the number the number of bottles sold are as follows: Oct Nov Dec TOTAL QTR PROJECTED SALES REVENUE SALES $126,945 $177,723 $203,112 $507,780 SALES: PRICE X QUANTITY $126.945 $177,723 $203,112 $507,780 Number of Bottles Sold Oct Nov Dec TOTAL QTR Number of Bottles Sold 15.356 21,499 24,570 61,425 3. Monthly Sales are classified as follows and realized in corresponding percentage: a. Cash sales....... ..30% of total sales b. Credit sales.... ..70% of total sales 4. Monthly credit sales are collected as follows and in the corresponding proportion: a. Collected in the month of the sale, 50% b. Collected one month after the sale month, 30% C. Collected the second month after the sale month, 20% 5. Cost Classifications based on 4th quarter information: Costs Fixed Costs Variable Costs Raw Materials used in Manufacturing (See corrected COGS Schedule in Part 1, Required #1) $ ? $ 36,000 Actual Costs on 4th Quarter Income Statement Direct Labor 114,000 Factory Overhead: Rent-Factory Indirect Labor 16,800 Insurance - Factory (66 2/3%) Utility-Factory (75%) 5,400 Depreciation-factory building 3,630 1,800 18,200 General Administrative Advertising expense Selling & Administrative Wages and Salaries Utility-Selling & Administrative (25%) Insurance-Administrative (33 1/3%) Depreciation-Administrative 18,000 1,680 64,000 67,000 720 2,970 27,000 6. All factory overhead and administrative expenses (except depreciation) are paid in cash in the month the cost is incurred. 7. Direct labor is paid at the end of the month 8. Raw material purchases are paid as follows: 50% in the month of the purchase, 30% the month after the purchase, and 20% in the second month following the purchase. The raw materials' budget is as follows: Oct Nov Dec TOTAL QTR PROJECTED RAW MATERIAL PURCHASES $138,012 NEEDED FOR SALES DESIRED ENDING LESS: BEGINNING PURCHASES $34,503 8,626 (40,800) $2,329 $48,304 12,076 (8,626) $51,755 $55,205 13,801 (12,076) $56,930 $111,013 9. Additional monthly obligation paid in cash include: a. Property taxes, due February 27, 2021: $2,790 b. Employee payroll taxes due March 15, 2021: $900. You have determined that you are going to provide the following information to the Woodsons: a. Compute the unit cost of goods sold using 4th quarter information and the corrected cost of goods sold statement you prepared in Part One b. Prepare a projected traditional multi-step income statement for the 1st quarter using information in "a," above and the 4th quarter information on the table in "5" above (see the previous page). For proper formatting, see Exhibit 1 at the end of this document. C. A schedule of collections from credit sales for each month of the 1st quarter 2021. Previous months' credit sales are: November 2020 credit sales, $ 91,550 December 2020 credit sales, $86,210 d. A schedule of payment for raw materials for each month of the 4th quarter. Previous months' credit (on account) purchases are: November 2020 purchases on account, $56,560 December 2020 purchases on account, $52,800 e. A projected Cash Flow Budget for (a) the total 1st quarter 2021 and (b) for each month in the 1st h quarter 2021 based on the information provided above. Beginning cash balance on January 1, 2021 is $242,800. Additional presentation requirements: 1. Prepare a formal report for the Woodsons. You should use ONLY WORD and EXCEL. 2. Be sure the report is ORGANIZED. That is, be sure the report is clearly presently and EASY for the Woodsons to ready and understand. Do not submit any pdf files. 3. You can have a separate WORD file for narrative (i.e., discussion and small tables) and a separate EXCEL file for large tables, schedules, statement, etc. Be sure that all tables, schedules, statements, etc., are clearly identified (e.g., name and/or number) and adequately referenced in the WORD file and letter when/if discussed. 4. Your reports should have a cover page. 5. A brief letter indicating what is being submitted should be attached. The letter SHOULD NOT BE A PART OF THE REPORT. In the WORD file, put a blank page between the letter and the report. 6. You have the option to work with one business associate (classmate). Together you should provide the Woodsons with a high-quality report and recommendations. 7. You indicated to the Woodsons that the reports will be submitted by Friday, May 1, 2021, at the end of the day, 11:59 p.m. APPENDIX EXHIBIT #1: Sales Cost of Goods Sold Gross Profit Selling and Administrative: Advertising Selling/Admin. Salaries Utility-Adm Insurance Depreciation- Admin. Total Sell/Admin Expenses Net Income (Loss)