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 2nd quarter $ 1,900
Paid in 3rd quarter 37,750 39,650
Depreciation, selling and administrative
equipment per quarter 9,000
Property tax paid in 4th 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:
|
|
Quarter 1
|
Yearly Total
|
|
Sales budget
|
$240,000
|
$1,920,000
|
|
Schedule of expected cash collections
|
$239,000
|
$1,823,000
|
|
Production budget
|
10,926
|
80,826
|
|
Direct materials budget
|
$25,827
|
$164,527
|
|
Schedule of expected cash disbursements
|
$43,879
|
$178,525
|
|
Direct labor budget
|
$83,038
|
$614,278
|
|
Manufacturing overhead budget, cash disbursements
|
$44,622
|
$$267,582
|
|
Selling and Administration Expense budget, cash disbursements
|
$105,000
|
$597,800
|
|
Cash balance
|
$40,261
|
$40,815
|
|
Cost of goods manufacture for the year
|
|
$1,099,512
|
|
Net income for the year
|
|
$182,138
|
|
Total assets, December 31, 2017
|
|
$805,765
|