The Fuzzy Slipper Sock Company Your Task You were recently hired as an assistant controller at the Fuzzy Slipper Sock Company (the Company). Your employer manufactures and sells a single product: super comfortable and fuzzy slipper socks! With people stuck at home because of the COVID-19 pandemic, the Company has seen a significant increase in demand for their fuzzy slipper socks. The socks pair perfectly with pajama pants, creating an extremely comfortable, stay-at-home look. The first task you have been assigned in your new role is to prepare the master budget for the quarter ended March 31st, 2021. You have assembled the following information. Information on sales The slipper socks sell for $15 each. Recent and budgeted sales (in units) are as follows: November (actual) 12,000 December (actual) 13,500 January 11,000 February 12,500 March 11,300 April 10,500 May 9,500 All sales are on credit, with no discount. The company has found that only 25% of a months sales are collected by month-end. An additional 60% is collected in the month following the sale, and the remaining 15% is collected in the second month following the sale. Bad debts have been negligible, so they can be ignored for the purposes of the budget. Information on inventory and production Inventories of finished goods on hand at the end of each month are to be equal to 70% of the following months budgeted sales. As of December 31st, 2020, the company had 7,700 pairs of fuzzy slipper socks in finished goods inventory. The company has no work-in-process (WIP) inventory. Each pair of slipper socks requires 8/10th of a yard of fabric (a fleecy, wool blend fabric), which the company purchases for $10.50 per yard. It is company policy to keep enough fabric in raw materials to meet 50% of the next months production needs. As of December 31st, the Company had 4,820 yards of fabric in raw materials inventory. Purchases of raw materials are paid for as follows: 60% in the month of purchase and the remaining 40% in the following month. Each pair of socks requires only 0.167 labour hours (10 minutes) to assemble by hand, as most of the work is done by machine. Employees making the socks are paid $14.60 per hour, and do not work overtime. The Company has enough casual/on-call workers that they can call in if additional work is required. Manufacturing overhead includes all the costs of production other than direct materials and direct labour. The variable component of manufacturing overhead is $0.35 per pair of socks in production, and the fixed component is $13,200 per month. The $13,200 of fixed manufacturing overhead includes depreciation of $3,300 per month on the sewing machines used to manufacture the socks. Direct labour hours is used as an allocation base for assigning manufacturing overhead to units produced. Information on selling and administrative expenses and cash outlays The Companys other monthly operating expenses are given below: Variable: Commission expense $0.50 per pair of slipper socks sold ** Fixed: Wages and salaries $12,000 Utilities $3,500 Insurance $900 Depreciation (S&A) $1,500 Advertising $800 **The sales team receives a commission for each pair of slipper socks sold. The Company sells primarily to retailers who place large orders. The Company introduced this incentive to its sales team last year, and it has proven to be a great motivational tool that helped to skyrocket sales. All operating expenses are paid during the month in cash, with the exception of the depreciation, as it is not a cash expense. The Company was recently involved in a lawsuit related to a customer who received a pair of socks with a dead cockroach found inside the left sock. The Company decided to settle out of court and is due to pay that customer $5,000 for emotional damages in January. This anticipated liability was accrued for in December 2020. Additionally, the Company has decided to purchase a new air conditioning unit for the manufacturing facility for $16,000. Payment for the air conditioner will be made in February, but the air conditioner will not be delivered until April 1, 2021. At the end of December 2020, the Company declared its year-end dividend to shareholder for $25,000 to be paid during the first month of the following quarter. In March 2021, the Directors plan to declare a dividend of $10,000, which will be paid to shareholders in April 2021. Balance Sheet of the Fuzzy Slipper Sock Company as at December 31st, 2020: Assets Cash $24,000 Accounts receivable 178,875 Inventory - Raw Materials 50,610 Inventory - Finished Goods 95,000 PP&E 290,000 Accumulated depreciation (85,000) 205,000 TOTAL ASSETS $553,485 Liabilities and Equity Bank loan $1,000 Accounts payable, purchases 39,984 Accrued liability (lawsuit) 5,000 Dividends payable 25,000 Capital stock, no par 275,000 Retained earnings 207,501 TOTAL LIABILITIES AND EQUITY $553,485 Additional information on cash requirements and financing Management of the Company requires a minimum ending cash balance each month of $24,000. The Company can borrow money from its bank at 6% annual interest. All borrowing must be done at the beginning of a month, and repayments must be made at the end of a month. Borrowings and repayments of principal must be in round $1,000 amounts. Interest is computed and paid when any repayments occur. Round all interest payments to the nearest whole dollar. Compute interest using whole months, not days. The company wishes to use any excess cash to pay loans off as rapidly as possible. REQUIRED: Prepare the following for the company for the quarter ending March 31st: a. Sales Budget and Schedule of Expected Cash Collections b. Production Budget c. Direct Materials Budget and Schedule of Expected Cash Disbursements d. Direct Labour Budget (inclusive of expected labour disbursements) e. Manufacturing Overhead Budget (inclusive of expected overhead disbursements) f. Ending Finished Goods Inventory Budget g. Selling and Administrative Budget h. Cash Budget i. Schedule of Cost of Goods Manufactured j. Budgeted Income Statement (with the cost of goods sold computation included in the income statement) You may ignore income taxes for the purposes of this budget k. Budgeted Retained Earnings Statement l. Budgeted Balance Sheet at March 31st, 2021 NOTES Preparing the schedules For items (a) through (h) show each of the 3 months and a total column for the whole quarter (ended March 31). It may be helpful to prepare (a) through (g) on one individual worksheet in Excel, to make building and referencing easier. For items (i) through (k), show only for the quarter ended March 31st, 2021. Please prepare the full budget on one excel worksheet. Do not create a new excel worksheet/tab for each budget or financial statement. All budgets and statements should be formatted professionally. Use Excel to do the work. Use cell references and formulas in your schedules you will thank yourself when/if you need to make any changes. You will be able to update certain numbers and have the changes instantly flow through the rest of the schedules. Think about this have you already entered that number once before, or was it calculated somewhere else? If it was calculated elsewhere, use a cell reference to the first place it shows up. Do not just type it in. Cheating and plagiarism This is an individual assignment. You are to prepare the Master Budget using Excel. Start your project with a blank Excel spreadsheet. The spreadsheet should not be someone elses or downloaded from anywhere on the internet. Sharing spreadsheets is considered cheating. Allowing another student to use or copy your spreadsheet is plagiarism. ALL instances of cheating or plagiarism will be reported to the Deans office and may result in significant academic penalties as determined by the Business Department Deans office. Submission Upload your excel file to the dropbox in the Master Budget Project folder on Moodle, prior to 11:55 PM on Friday, March 19th, 2021. Check figures: Check figure Amount Schedule Total cash collections for the quarter $ 545,625 (Expected cash collections) Total required production for the quarter (units) 34,450 (Production) Raw materials to purchase for the quarter (total yards) 26,660 (Direct materials) Ending cash balance $42,325 (Cash budget) Total cost of goods manufactured for the quarter $425,033 (Schedule of COGM) Total assets $536,017 (Balance sheet).