Alysa's Yummy Yogurt

Alysa's Yummy Yogurt Ltd. is a small but successful manufacturer of premium yogurt and operates in Kelowna and its surrounding areas. It sell cartons of its yogurt to retail outlets in its service area. You have assembled the following information:

The selling price is $24 per carton.Projected sales for each quarter in cartons for 2017 are as follows:

Quarter 1 (January, February, March) 10,000
Quarter 2 (April, May, June) 20,000
Quarter 3 (July, August, September) 30,000
Quarter 4 (October, November, December) 20,000

All sales are on credit, with no discount, and payable within 15 days. The company has found 60% of each quarter's sales are collected by end of the quarter. The balance, 40%, is collected in the quarter after the sales. Bad debts have been negligible.

Inventories of finished goods on hand at the end of each month, in cartons, are to be equal to 15% of the following quarter'sbudgeted sales. As of December 31, 2017the company had 2,900 cartons in inventory.

Each carton requires eight kilograms of raw materials, which the company purchases for $0.25 per kilogram. Alysa's Yummy Yogurt keeps an ending inventory of raw materials at the end of each quarter equal to 20% of the next quarter's production needs. As of December 31, 2017 the company had 30,000 kilos of raw materials on hand.

Purchases of raw materials are paid for as follows: 70% in the quarter of purchase and the remaining 30% in the following quarter.

Each carton requires 48minutes to finish. Employees who make the cartons of yogurt are paid $9.50 per hour and never work overtime (i.e. the company has enough casual 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 labor. The variable component is $3 per direct labor hourin production and the fixed component is $32,400 per quarter (this amount includes depreciation of $14,000 per quarter).

Alysa's Yummy Yogurt's monthly operating expenses are given below:

Variable:

         Selling and administrative expense                 $3 per carton of sales

Fixed:

         Executive salaries per quarter                                                    $55,000

'        Advertising per quarter                                                  20,000

         Insurance, non-manufacturing

             Paid in 2^nd quarter                                          $  1,900

             Paid in 3^rd quarter                                           37,750   39,650

         Depreciation, selling and administrative

         equipment per quarter                                                                9,000

         Property tax paid in 4^th quarter                                                      18,150        

All operating expenses are paid during the quarter in cash, with the exception of the depreciation and insurance and property tax expenses. Management has decided to release a new line of yogurt. To accommodate the expected increase in demand the company will be purchasing a new equipment in the first quarter for $60,000 and new equipment in the second quarter for $70,000.Alysa's Yummy Yogurt declares and pays a dividend of $10,000 on the last day of each quarter.

The balance sheet at December 31, 2016 is given below:

Assets

Current Assets

Cash                                                                                      $ 39,800

Accounts receivable                                                                95,000

Inventory, raw materials (18,500 kg)                                      4,625

Inventory, finished goods (2,074 cartons @ $13.60 each) 28,200

Total current assets                                                                                         $167,625

Property, Plant and Equipment

Land                                                                        $80,000

Building and equipment                                       $700,000

  Accumulated depreciation                                  ( 292,000)  408,000

Total property, plant and equipment                                                               488,000

Total Assets                                                                                                    $ 655,625

Liabilities

Current Liabilities

Accounts payable, raw materials (103,200 kg)                       $25,800

Equity

Capital shares                                                           175000

Retained earnings                                                   454,825

Total equity                                                                             629,825

Total liabilities and shareholders' equity                                                        $ 655,625       

Management of Alysa's Yummy Yogurt requires a minimum ending cash balance each quarter of $40,000. The company can borrow money from its bank at 10% annual interest. All borrowing must be done at the beginning of a quarter, and repayments must be made at the end of a quarter. Repayments of principal must be in round $1,000 amounts. Borrowing is also in round $1,000 amounts. Interest is paid when the financing is repaid and only on the amount repaid. Round all interest payments to the nearest whole dollar. Compute interest using whole months. The company wishes to use any excess cash to pay loans off as rapidly as possible.

REQUIRED:

Using excel, on one worksheet, prepare the following:
- Opening balance sheet, December 31, 2016 in good format
- Sales budget
- Schedule of expected cash collections
- Accounts receivable, December 31, 2017
- Production budget
- Direct materials budget
- Schedule of expected cash disbursements for materials
- Accounts payable, December 31, 2017
- Manufacturing overhead budget
- Ending finished goods inventory budget. When calculating the predetermined overhead rate and the cost of a carton, use 2 decimal places. E.g. $6.15
- Selling and administration expense budget
- Cash budget. Below the cash budget, show the balance in the loan account.
- Schedule of cost of goods manufactured for the year ended December 31, 2017 in good form. This is one schedule for the full year.
- Income statement for the year ended December 31, 2017 in good form. Prepare one statement for the full year.
- Balance sheet, December 31, 2017 in good form. This one statement for the full year.

All schedules and statements should be formatted professionally.
- For statements, including schedule of cost of goods manufactured you must have headings.
- $ signs go at the top of the column and at the end of column beside the final total.
- For other schedules only put $ signs beside dollar amounts and have $ signs at the top of the column and at the end beside the final total.
- Ensure you have yearly totals for the schedules and budgets.
- Except when calculating the predetermined overhead rate and the cost per carton, round all answers to the nearest $1.

Use excel to do the work. Use cell references and formulas in your schedules.

If you're having difficulty you can always email me a copy of your excel file and I'll point you in the right direction.

Check figures: