Frapp Delicious Ltd was established in 2018, it produces premium ice lollies using only natural ingredients. The companys business is highly seasonal, with most of the sales occurring in summertime. Prior to 2020 its business had been smooth until a major cash crunch in 2020 and 2021 when the COVID-19 disease spread across the world, that almost forced the company into bankruptcy.

After that event, having realized the role of budgeting in organization, Greenspan the owner and CEO of the company decided to carry out the practice of budgets preparation within his company. And as the newly hired CFO, you are assigned this important task of preparing budgets for the next fiscal year beginning January 1^st, 2022.

With the assistance of your staff and people from other departments, you have gathered following necessary information for the preparation of budgets.

Sales data

The budgeting team led by you comes up with following estimations of sales for the next year (Year 2022):

	Quarter 1	Quarter 2	Quarter 3	Quarter 4	2023 1st Q 8
Sales in cases	12,000	36,000	48,000	24,000	18,000

Selling price per case is predicted at $20 throughout the next year and during the first quarter of 2023.

At Frapp Delicious Ltd, all sales are on credit; furthermore, experience has shown that 70% of sales are collected in the quarter in which the sale is made and the remaining 30% are collected in the following quarter.

Production data

The experience shows that company needs to have a level of ending finished goods inventory equal to 20% of the next quarters sales to ensure the continuity of sales. The desired ending inventory of finished goods on December 31, 2022 is 3,600 cases (20 % of estimated 2023s first quarter sales of 18,000 cases).

And the company has no W.I.P, all products are started and completed during the quarter of production.

Direct materials information

The product specification requires 15 pounds of ingredients for each case of the product. Currently the average cost of ingredients is $0.2 per pound and due to the contract with supplier this purchasing price will be unchanged throughout the next 18 months.

The firms policy is to have in ending materials inventory each quarter an amount equal to 10 percent of the next quarters production needs. The ending inventory for the fourth quarter of 2022 is estimated at the amount of 27,000 pounds.

At Frapp Delicious Ltd, the payment terms agreed with suppliers is to pay for 50% of purchases in the quarter in which the purchase is made and 50% in the following quarter. The balance of payable at the end of 2021 will be paid in full in the first quarter of 2022.

Direct labor information

The production process at Frapp Delicious Ltd uses 0.4 direct labor hour for each case of product. The hourly wage rate at the company is $15. Workers will be paid for the actual working hours and be paid in full in the month of working with no overtime pay.

Manufacturing overhead estimation

At Frapp Delicious Ltd manufacturing overhead is separated into variable and fixed components. The variable component is $4 per direct labor-hour and the fixed component is $72,720 per quarter. All manufacturing overheads will be paid for cash in the quarter in which they incur, except depreciation costs which amount to $18,000 per quarter.

Selling and administrative expenses

Like the manufacturing overhead budget, the selling and administrative expense budget of the company is divided into variable and fixed cost components with the variable selling and administrative expense is estimated at $1.80 per case.

The fixed selling and administrative expense budget is compiled from business units budgets as follows:

	Quarter 1	Quarter 2	Quarter 3	Quarter 4
Advertising	24,000	24,000	24,000	24,000
Executive salaries	66,000	66,000	66,000	66,000
Insurance	12,000	12,000	12,000	12,000
Property taxes	4,800	4,800	4,800	4,800
Depreciation	12,000	12,000	12,000	12,000

All selling and administrative expenses are about to be paid for cash in the quarter of incurrence except for the depreciation expenses.

You have also obtained the following additional assumptions that are needed for the preparation of cash budget:

• The beginning cash balance is expected to be $51,000 on January 1^st, 2022
• Greenspan plans to spend $156,000 during 2022 on equipment purchases: $60,000 in the first quarter; $48,000 in the second quarter; $24,000 in the third quarter; and $24,000 in the fourth quarter.
• The board of directors has approved cash dividends of $9,600 per quarter.
• The company has a policy to have a cash balance of at least $30,000 at the beginning of each quarter for contingencies.
• A current agreement with a local bank allows the company to borrow in increments of $10,000 at the beginning of each quarter, up to a total loan balance of $250,000. The interest rate on these loans is 1% per month, and interest is not compounded. The borrowings (if any) must be made on the first day of the quarter and the repayment of the loan (if any) plus accumulated interest can be made at the end of the year only.

Required:

1. On spreadsheet, prepare all the relevant budgets for the next fiscal year 2022, including:
   1. Sales budget (5 marks)
   2. Production budget (5 marks)
   3. Direct materials budget (10 marks)
   4. Direct labor budget (5 marks)
   5. Manufacturing overhead budget (5 marks)
   6. Ending finished goods inventory budget (10 marks)
   7. Selling and administrative expense budget (5 marks)

FILE TOOLS VIEW BAC5010-spreadsheet-assessment_2021 (1) - Word (Product Activation Failed) - X Plant and equipment: Land $ 96,000 840,000 (350.400) 585,600 $ 780,840 2. On the same spreadsheet, produce cash budget for the year 2022 (25 marks) 3. Produce budgeted Income Statement (15 marks) and budgeted Balance Sheet (15 marks) as of December 31, 2022 on separate pages of the same spreadsheet. All figures on these financial statements must be formulae. (No figures may be directly input into these pages.). The Balance sheet of the company as of December 31, 2021 is shown in appendix 1 Buildings and equipment, gross Less: Accumulated depreciation Total plant and equipment (net) Total assets Liabilities and Stockholders' Equity Current liabilities: Accounts payable Stockholders' equity: Common stock $ 30,960 $210,000 539,880 Retained earnings Total stockholders' equity Appendix 1: Balance sheet of company as of December 31, 2021 749,880 $780,840 Total liabilities and stockholders' equity FRAPP DELICIOUS LTD Balance Sheet December 31, 2021 Assets Current assets: $ 51,000 108,000 Cash Accounts receivable (net) Raw materials inventory Finished goods inventory Total current assets 5,040 31,200 $195,240 SCREENS 8-9 OF 15 ES Type here to search EI + 118 % 10:52 CH 16/12/2021 dh ENG e