Required You have been contracted as a consultant by Sparkle Designs Ltd. Sparkle produces and sells speciality Christmas ornaments. Sparkle is managed by its three sibling owners and its fiscal year end is December 31st. It is January 4, 2022, and Sparkle just completed its fifth year-end (December 31, 2021). The owners of Sparkle are considering using a detailed Master Budget for the 2022 fiscal year to plan and control operations. You have been contracted to complete the following: I. Prepare a Master Budget for Sparkle for each quarter of 2022 and for the 2022 fiscal year in total using the information in the "Data" section. The Master Budget will detail each quarter's activity and the activity for the 2022 fiscal year in total. The following component budgets must be included: II. i. Sales Budget ii. iii. iv. V. vi. vii. Schedule of Expected Cash Collections Production Budget Direct Materials Budget Schedule of Expected Cash Disbursements for Materials Direct Labour Budget Manufacturing Overhead Budget viii. Ending Finished Goods Inventory Budget ix. X. Selling and Administrative Expense Budget Cash Budget Prepare the following for the 2022 fiscal year in total (these should not be quarterly): xi. Budgeted Schedule of Cost of Goods Manufactured xii. Budgeted Schedule of Cost of Goods Sold xiii. Budgeted Income Statement xiv. Budgeted Balance Sheet The owners of Sparkle would like to expand operations and increase cash flows. They have excess capacity in their current facility that they would like to maximize before expanding further. To accomplish this goal, they are considering the following changes: ornaments i. An increase in executive salaries of $40,000 per year related to the design of new ii. iii. An increase in advertising expense of $50,000 per year related to marketing their current and new ornaments The impact of the changes in (i) and (ii) is an anticipated increase in sales volume of 10% compared to the original estimates for every quarter for 2022 and 2023 Although they have the operating line available to them, their preference it to have a $0 ratiouicy have in upriamily like avant, MI PRITI I outstanding short-term debt balance at year-end. III. The owners of Sparkle have asked you to prepare a Report discussing the following: i. ii. iii. iv. V. The overall importance/usefulness of budgeting General difficulties faced relating to preparing budgets Observations and recommendations you would make based on the Cash Budget prepared in Requirement I The impacts of the changes made to the Budget in Requirement II Additional recommendations would you make to Sparkle to improve its current situation a. You do not necessarily need to constrain your answer to include only those areas dealing with budgets Data 2 COMM 210 INTRODUCTION TO MANAGERIAL ACCOUNTING - Section 12 & 20 BUDGET ASSIGNMENT Sparkle will base the 2022 Master Budget on the following information: 1. Expected sales, in units, for the four quarters of 2022 and the first two quarters of 2023 are as follows: 2022 Q1 15,000 2022 Q2 16,000 2022 Q3 25,000 2022 Q4 40,000 2023 Q1 2023 Q2 15,500 17,000 2. The selling price for 2022 has been set at $20.00 per unit. 3. All sales are on account. 70% of sales on account are collected in the quarter of sale and 30% of sales on account are collected in the following quarter. Assume that all the balance in Accounts 2023 Q2 17,000 2. The selling price for 2022 has been set at $20.00 per unit. 3. All sales are on account. 70% of sales on account are collected in the quarter of sale and 30% of sales on account are collected in the following quarter. Assume that all the balance in Accounts Receivable at December 31, 2021 will be collected in the first quarter of 2022. Assume no bad debts are incurred. 4. Sparkle has a policy of keeping ending finished goods inventory equal to 15% of next quarter's forecasted sales. Sparkle has a policy of maintaining direct material ending inventory equal to 15% of direct materials needed for the next quarter's production requirements. All raw materials are purchased on account. 50% of the quarter's purchases are paid for in the quarter of purchase and the remaining 50% in the following quarter. There is no beginning or ending work-in-process inventory. 5. Each unit requires the following direct inputs: a. 250 grams of direct material which is available at a price of $0.010/gram b. 0.5 hours of direct labor at a rate of $15.00 per hour 6. Direct labourers are paid at the end of each month. 7. Total budgeted variable overhead costs for the 2022 year are as follows: Indirect materials Indirect labor Utilities Total $15,000 $10,000 $40,000 $65,000 Variable overhead is applied using a predetermined overhead rate based on annual direct labor hours. All variable overhead items are paid for in the quarter incurred. 8. Total budgeted fixed overhead costs for the 2022 are as follows: Property taxes Insurance Maintenance Utilities Depreciation Total $15,000 $10,000 $25,000 $30,000 $22,000 $206,000 Fixed overhead is paid evenly over the four quarters of the year. 9. Variable selling and administration expenses are $2.00 per unit sold. These costs are paid for in the quarter they are incurred. 10. Annual fixed selling and administration expenses are as follows: Advertising Executive salaries Insurance Property taxes $120,000 $150,000 $5,000 Depreciation Total $2,000 $4,244 $281,244 Fixed selling and administration expenses are paid evenly over the four quarters of the year. 11. Sparkle has the following financing and investing activities impacting 2022: a. Sparkle is planning on purchasing a piece of land, costing $100,000, in the last quarter of 2022. This piece of land will be held for future plant expansion. They will pay cash for the land and will finance any resulting cash shortfall by drawing on their operating line of credit. C. b. Sparkle will pay $50,000 of dividends to each shareholder in the last quarter of 2022. Sparkle currently has a $250,000 outstanding long-term loan with an annual interest rate of 5% and makes quarterly interest only payments at the end of each quarter. d. Sparkle has an operating line established with its bank. This allows them to borrow in multiples of $5,000 to cover any cash shortfalls. All borrowing is assumed to occur at the beginning of the quarter in which the funds are required, and repayment is assumed to be made at the end of the quarter in which funds are available for repayment. Simple interest at the rate of 5% per annum is paid on a quarterly basis on all outstanding short- term loans. All repayments are in multiples of $1,000. 12. Sparkle's balance sheet as of December 31, 2021 is as follows: Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory Buildings and Equipment Accumulated Depreciation Total Assets $10,000 $200,000 Accounts Payable (1) Long-term Debt $0 $0 Common share, no par $2,000,000 ($131,220) Retained Earnings $2,078,780 (1) Only used for direct materials $1,000 $250,000 $1,727,780 $100,000 Total Liabilities and Shareholders' Equity $2,078,780 File Structure CUIVIIVI 210-IIIODUCTIONIMIANACULIIAL AUUUUITING OCCIO 12 UZU BUDGET ASSIGNMENT Excel Workbook A. Develop your entire Master Budget (Requirement 1) on the "I-MASTER BUDGET" worksheet tab in the "COMM 210 Budget Assignment Winter 2022 Section 12 & 20" workbook provided B. When you have finalized the Master Budget, start work on the analyses for Requirement II. a. First, copy the "I-MASTER BUDGET" worksheet tab to create a new worksheet tab labelled "II-UPDATED BUDGET" in the "COMM 210 Budget Assignment Winter 2022 Section 12 & 20" workbook provided b. Second, make any necessary changes on the "II-UPDATED BUDGET" worksheet tab i. If you have fully programmed the first worksheet, the results of your changes will be immediately calculated NOTE: Part of your mark will depend on how well you link the parts in your worksheets Business Report A. Your report should use the following formatting: a. Times New Roman Font b. 12-Point Font Size C. 1-inch Margins d. Double Spacing B. Your report should include the following sections (in this order) with the maximum number of page numbers for each section identified: a. Title Page (1 page) b. i. Includes the title of the report, author, and date Table of Contents (1 page) 1. List of sections (and any subheadings) and the applicable page numbers C. Executive Summary (1 page) i. Condensed version of the report which includes an overview of the topics and any recommendations d. Body (3 pages) e. i. The main discussion of the topic Recommendation (1 page) i. Recommendation based on the information contained in the report f. Appendices i. Your excel worksheets will be the appendices 1. You must refer to the appendices in the body of your report C. The report will be evaluated on content, writing, and presentation (grammar, spelling, conciseness, logical structure, understandability, and persuasiveness).