9 069... w Master Budget Case Fall 2023 MH - Saved to my Mac Home Insert Draw Design Layout References Mailings Review View ? Tell me Comments Editing Share Bradley Ha... v 36 A" A Aa Ap AaBbCcDdEe AaBbCcDdEe AaBbCcDc AaBbCcDdEE AaBb( , Paste BIUvab X X A DAY Normal No Spacing Heading 1 Heading 2 Title Styles Dictate Sensitivity Editor Pane The Hairy Yak Yarn Company Your Task You were recently hired as an assistant controller at the Hairy Yak Yarn Company ("the Company"). Your employer manufactures and sells a single product: balls of yarn! With people learning to knit because of the COVID-19 pandemic, the Company has seen a significant increase in demand for their yarn. The first task you have been assigned in your new role is to prepare the master budget for the quarter ended March 31st, 2023. You have assembled the following information. Information on sales The yarn balls sell for $25 each. Recent and budgeted sales (in units) are as follows: November (actual 10,000 December (actual) 11,00 January 12,000 February 11,000 March 11,000 April 9,000 May 10,000 All sales are on credit, with no discount. The company has found that only 25% of a month's sales are collected by month-end. An additional 70% is collected in the month following the sale, and the remaining 5% 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 10% of the following months budgeted sales. As of December 31st, 2022, the company had 1,200 balls of yarn in finished goods inventory. The company has no work-in-process (WIP) inventory. Each ball of yarn requires 2 yards of fleece, which the company purchases for $5.00 per yard. It is company policy to keep enough fleece in raw materials to meet 50% of the next month's production needs. As of December 31st, the Company had 11,900 yards of fleece in raw materials inventory. Page 1 of 4 1127 words X English (United States) Focus E + 101%9 069... w Master Budget Case Fall 2023 MH - Saved to my Mac Home Insert Draw Design Layout References Mailings Review w ? Tell me Comments Editing Share Bradley Ha... v 36 A" A Aa Ap AaBbCcDdEe AaBbCcDdEe AaBbCcDc AaBbCcDdEE AaBb( , Paste BIUvab X X A DAY Normal No Spacing Heading 1 Heading 2 Title Styles Dictate Sensitivity Editor Pane Purchases of raw materials are paid for as follows: 50% in the month of purchase and the remaining 50% in the following month. Each ball of yarn requires only 0.5 labour hours (30 minutes) to assemble by hand, as most of the work is done by machine. Employees making the yarn are paid $15.00 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 $1.00 per ball of yarn in production, and the fixed component is $17,000 per month. The $17,000 of fixed manufacturing overhead includes depreciation of $2,000 per month on the machines used to manufacture the yarn. 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 Company's other monthly operating expenses are given below: Variable Colour licensing 0.50 per ball of yarn sold * * Fixed : Wages and salaries $10,000 Utilities $2,500 Insurance $800 Depreciation (S&A) $1,200 Misc $1,000 **The Company pays "yarn colour experts" a licensing fee per ball of yarn to use their cool, wacky colorways. All operating expenses are paid during the month in cash, with the exception of the depreciation, as it is not a cash expense. Page 2 of 4 1127 words x English (United States) Focus E + 101%9 069... w Master Budget Case Fall 2023 MH - Saved to my Mac Home Insert Draw Design Layout References Mailings Review w ? Tell me Comments Editing Share Bradley Ha... v 36 A A Aa Ap EVENEEVEE AaBbCcDdEe AaBbCcDdEe AaBbCcDc AaBbCcDdEE AaBb( , Paste BIUvab X X A DAY Normal No Spacing Heading 1 Heading 2 Title Styles Dictate Sensitivity Editor Pane + Balance Sheet as at December 31st, 2022: Assets Cash 22,500 Accounts receivable 18,750 Inventory - Raw Materials 59,500 Inventory - Finished Goods 24,016 PP&E 254,000 Accumulated depreciation (71,000) Total assets 507,766 Liabilities and Equity Financing Accounts payable, purchases 57,500 Dividends payable Capital stock, no par 150,000 Retained earnings (see below) 300,266 Total liabilities and equity 507,766 Additional information on cash requirements and financing Management of the Company requires a minimum ending cash balance each month of $10,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 1 - 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 i. 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 1. Budgeted Balance Sheet 2 - Prepare a 2-3 minute video walkthrough of your master budget, explaining your findings. Page 3 of 4 1127 words x English (United States) Focus E + 101%9 069... w Master Budget Case Fall 2023 MH - Saved to my Mac Home Insert Draw Design Layout References Mailings Review w ? Tell me Comments Editing Share Bradley Ha... v 36 A" A Aa Ap AaBbCcDdEe AaBbCcDdEe AaBbCcDc AaBbCcDdEE AaBb( , Paste BIUvab X X A DAY Normal No Spacing Heading 1 Heading 2 Title Styles Dictate Sensitivity Editor Pane 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, 2023. All schedule 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. Don't 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 else's 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 Dean's office and may result in significant academic penalties as determined by the Business Department Dean's office. Submission Upload BOTH your excel file AND your video explainer file to the dropbox in the Master Budget Project folder on Moodle. Check figures: Check figure Amount Schedule Total cash collections for the quarter S 848,750 (Expected cash collections) Total required production for the quarter (units) 33,700 (Production) Raw materials to purchase for the quarter (total yards) 64,600 (Direct materials) Ending cash balance $149,150 (Cash budget) Cost of Goods Sold $680,454 (Income Statement) Total assets $606,062 (Balance sheet) Page 4 of 4 1127 words X English (United States) Focus E + 101%