Required

You have been contracted as a consultant by Sparkle Designs Ltd. Sparkle produces and sells specialty

Christmas ornaments. Sparkle is managed by its three sibling owners and its fiscal year end is December 31^st. 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:

1. 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:
2. Sales Budget
3. Schedule of Expected Cash Collections
4. Production Budget
5. Direct Materials Budget
6. Schedule of Expected Cash Disbursements for Materials
7. Direct Labour Budget
8. Manufacturing Overhead Budget
9. Ending Finished Goods Inventory Budget
10. Selling and Administrative Expense Budget
11. Cash Budget

Prepare the following for the 2022 fiscal year in total (these should not be quarterly):

1. Budgeted Schedule of Cost of Goods Manufactured
2. Budgeted Schedule of Cost of Goods Sold
3. Budgeted Income Statement
4. Budgeted Balance Sheet
5. 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:
6. An increase in executive salaries of $40,000 per year related to the design of new ornaments
7. An increase in advertising expense of $50,000 per year related to marketing their current and new ornaments
8. 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 outstanding short-term debt balance at year-end.

1. The owners of Sparkle have asked you to prepare a Report discussing the following:

1. The overall importance/usefulness of budgeting
2. General difficulties faced relating to preparing budgets
3. Observations and recommendations you would make based on the Cash Budget prepared in Requirement I
4. The impacts of the changes made to the Budget in Requirement II
5. 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 budge

Data

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	15,500
2023 Q2	17,000

1. The selling price for 2022 has been set at $20.00 per unit.
2. 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.
3. 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.
4. Each unit requires the following direct inputs:
   1. 250 grams of direct material which is available at a price of $0.010/gram
   2. 0.5 hours of direct labor at a rate of $15.00 per hour

1. Direct labourers are paid at the end of each month.
2. Total budgeted variable overhead costs for the 2022 year are as follows:

Indirect materials	$15,000
Indirect labor	$10,000
Utilities	$40,000
Total	$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.

1. Total budgeted fixed overhead costs for the 2022 are as follows:

Property taxes	$15,000
Insurance	$10,000
Maintenance	$25,000
Utilities	$30,000
Depreciation	$22,000
Total	$206,000

Fixed overhead is paid evenly over the four quarters of the year.

1. Variable selling and administration expenses are $2.00 per unit sold. These costs are paid for in the quarter they are incurred.
2. Annual fixed selling and administration expenses are as follows:

Advertising	$120,000
Executive salaries	$150,000
Insurance	$5,000
Property taxes	$2,000
Depreciation	$4,244
Total	$281,244

Fixed selling and administration expenses are paid evenly over the four quarters of the year.

1. Sparkle has the following financing and investing activities impacting 2022:
   1. 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.
2. Sparkle will pay $50,000 of dividends to each shareholder in the last quarter of 2022.
3. 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.
4. 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 shortterm loans. All repayments are in multiples of $1,000.

1. Sparkle's balance sheet as of December 31, 2021 is as follows:

Cash	$10,000	Accounts Payable (1)	$1,000
Accounts Receivable	$200,000	Long-term Debt	$250,000
Raw Materials Inventory	$0
Finished Goods Inventory	$0	Common share, no par	$1,727,780
Buildings and Equipment	$2,000,000	Retained Earnings	$100,000
Accumulated Depreciation	($131,220)

Total Assets $2,078,780 Total Liabilities and Shareholders' Equity $2,078,780

(1) Only used for direct materials