FRZN Rubber Inc. has hired your employer to provide some professional consulting. Your group has been assigned to advise the management of FRZN Rubber on some alternative strategies for their coming fiscal year. A major portion of this consulting will include presenting FRZN Rubber’s management with a coming year budget if they continue on with their current business model and to contrast this budget with alternative budgets if the company makes some changes to their future operations.

You must use an Excel workbook to complete this assignment. Within the workbook there must be separate spreadsheets presenting a budget for the current business model, any alternative business model you are requested to present management with, and any additional alternative business models you desire to present to management. Each of the spreadsheets should draw from a given data section using cell referencing, formulas, functions, and linking (i.e. aside from the given data amounts there should be no hard numbers in you budget). There may be hard numbers in any short-term borrowing and repayment cells if need be, but there is an additional up to five bonus marks for groups that are able to compute short-term borrowing and repayment using cell referencing.

By the due date each group must submit, by email to the instructor, one digital copy of the workbook and one digital copy of a written report to management. The workbook is to be in Excel format and the written report is to be in Word format. 

FRZN Rubber Inc. (a Canadian company with manufacturing facilities in Saskatoon, SK) is a leading manufacturer of hockey pucks. FRZN Rubber currently provides pucks to the NHL, AHL, all of the leagues in the CHL (WHL, OHL, QMJHL), University leagues in Canada and the United States, and retailers throughout North America. Additionally, this year FRZN Rubber will supply pucks for the Winter Olympics. With a growing business and an increasing market share, the management at FRZN Rubber has hired your company to provide them with a detailed master budget plan for the coming year broken down into quarters. Along with this base budget, FRZN Rubber’s management has asked that you also prepare alternative budgets based on some changes to their operating plans they have been discussing implementing. Management is also interested in seeing budgets involving any other changes in operating plans your company would like to present. For each of the budgets, you will present information for each quarter and for the year as a whole. FRZN Rubber’s fiscal year runs from February 1^st to January 31^st. The base budget for fiscal year 2018 will be based on FRZN Rubber’s current business model using the following information:

1. Expected sales, in units, for the four quarters of fiscal 2018 and the first and second quarter of fiscal 2019 are as follows. Notice that there is an expected seasonality related to hockey puck sales where during peak hockey season there are greater sales then in off season.

Quarter One 2018			106,000
Quarter Two 2018			48,000
Quarter Three 2018			94,000
Quarter Four 2018			120,000
Quarter One 2019			114,000
Quarter Two 2019			52,000

Pucks are sold for $1.00 each.

All sales are on account. Sixty percent of sales are collected in the quarter of sale, thirty-five percent of sales are collected in the quarter following the sale, and the final five percent of sales are collected in second quarter after the sale. Assume that 90% of the current accounts receivable will be collected in the first quarter of fiscal 2018 and the remaining 10% will be collected in the second quarter of 2018. Assume that there are no bad debts incurred.

2. The process for making pucks requires a special rubber compound, which is formed into cylinders a meter long and 76 millimeters in diameter. These cylinders are then cut into 25-millimeter-thick slices turning a meter long cylinder into 40 hockey puck sized portions. Each meter long cylinder requires:

6.8 Kilograms of special rubber compound, which the company purchases for $1.50 per kilogram.

The formation of the cylinders and cutting requires 15 minutes direct labour. The company pays its manufacturing personnel $20 per hour.

After the raw pucks are cut, a machine places them in molds for the final heat and pressure processing treatment. After the pucks are heat and pressure treated they are painted with the special logos customers require. The painting procedure requires two applications (an undercoat and a finish coat) which uses:

Each painting coat requires an hour of direct labour to paint 400 pucks. The company pays the manufacturing personnel $20 per hour.

The company is able to produce 10,000 pucks a week, which is equal to 130,000 pucks in a quarter or 520,000 pucks a year. Production beyond these upper limits would require a significant investment in additional assets in terms of a larger facility and additional equipment.

The current company policy is to maintain an ending rubber compound raw material inventory equal to 20% of the next quarter’s production needs. All the rubber compound is purchased on account. The company pays for 45% of the quarter’s purchases in the quarter of purchase and the remaining 55% in the following quarter. FRZN Rubber will pay all of its remaining accounts payable on their balance sheet for January 31^st, 2018 in the first quarter of fiscal 2018.

The company also has a policy of maintaining in finished goods inventory 7.5% of the next quarter’s anticipated sales. The company’s current business plan does not allow for any beginning or ending works in process inventory.

Direct labour costs are paid at the end of each month.

3. Total estimated variable overhead costs for fiscal 2018 (for the amount of sales estimated in part 1 above) are as follows:

Indirect Materials		$ 7,550
Indirect Labor		3,120
Employee Benefits		8,500
Utilities		5,700
Quality Inspections		380
Total		$ 25,250

Variable overhead is allocated according to a predetermined overhead rate based on number of units produced for the year. All variable overhead is paid in the quarter incurred.

4. Total estimated fixed overhead costs of fiscal 2018 are as follows:

Insurance		$ 2,500
Logo Design		1,790
Utilities		5,000
Depreciation		10,000
Property Taxes		4,800
Engineering		1,360
Total		$ 25,450

For budgeting purposes all fixed manufacturing overhead costs are spread evenly across the four quarters of fiscal 2018. Aside from property taxes, fixed manufacturing overhead is paid for in the quarter that it is incurred. Property taxes are paid in full at the end of the first quarter. All fixed manufacturing overhead is allocated according to a predetermined overhead rate based on annual direct labor hours.

5. Variable selling and administration expenses include shipping costs and other administration costs. It costs FRZN Rubber $5 to ship 1,000 pucks. Variable administration costs are $0.025 per puck. The variable selling and administration costs are paid in the quarter incurred.

Estimated fixed selling and administrative expenses for fiscal 2018 are as follows:

Salaries		$ 50,000
Travel		10,000
Depreciation		5,000
Marketing		5,000
Other		1,550
Total		$ 71,550

Fixed selling and administrative expenses are spread evenly across the 4 quarters for budgeting purposes. Applicable fixed selling and administrative expenses are paid equally across the quarters.

6. FRZN Rubber makes quarterly income tax payments during the year based on projected yearly net income. The company is subject to a 15% income tax rate.

7. FRZN Rubber Inc. plans the following investing and financing activities for the coming fiscal year (2018):

At the end of the 2^nd quarter, the company will open a new storage warehouse. This storage warehouse cost $50,000 and total payment is due at the end of the 2^nd quarter. The warehouse has and expected useful life of 25 years and no salvage value. The company plans on amortizing the cost of the warehouse using the straight-line method.

The company has an operating line of credit established with its bank. The line of credit permits FRZN Rubber to borrow in increments of $5,000 to cover any cash shortfalls. FRZN Rubber has an internal operating policy of maintaining a minimum quarterly ending cash balance of $10,000. Assume that all borrowing occurs at the beginning of the quarter in which the funds are required, and all repayment occurs at the end of the quarter in which funds are available for repayment. Simple interest at the rate of 8% per annum must be paid at the end of each quarter on all outstanding short-term loans. All repayments must be in multiples of $1,000, and no repayments are permitted in a quarter in which any short-term borrowing occurs.

The company currently has $150,000 in an outstanding long-term loan with an annual interest rate of 6% and makes semi-annual interest only payments with the principle due at the end of the loan. The loan is due in 2028.

8. The company’s unclassified balance sheet for January 31, 2018 is expected to be:

Cash			$ 11,000
Accounts Receivable			41,000
Raw Materials Inventory (1) (4)			5,180
Finished Goods Inventory (2) (4)			4,920
Plant, Property, and Equipment			482,900
Accumulated Depreciation			45,000
Total Assets			$ 500,000
Accounts Payable (3)			$ 15,800
Long-Term Debt			150,000
Common Stock			155,000
Retained Earnings			179,200
Total Liabilities and Equity			$ 500,000

These balance sheet figures must be taken as given.

(1) 3,500 kilograms of rubber compound at a cost of $1.48 per kilogram.

(2) 8,000 units (pucks) at a cost of $0.615 per unit (puck).

(3) Used for direct material purchases only.

(4) Existing inventory will be used or sold in the 1^st quarter of 2018.

Required

Begin by preparing a Base Case master budget (and label this Excel spreadsheet Base Case) for FRZN Rubber Inc. for each quarter of 2018 and for the year in total. The following component budgets must be included (also see the Marking Rubric):

Beginning balance sheet (classified).

Sales budget

Schedule of receipts

Production budget

Direct materials purchases budget

Schedule of disbursements for materials

Direct labour budget

Overhead budget (be sure to show disbursements for overhead).

Selling and administrative budget (be sure to show disbursements for selling and administrative expenses).

Cash budget

Prepare the following (in good form) for the year, 2018, in total (these do not need to be quarterly).

Cost of goods manufactured budget

Cost of goods sold budget

Pro forma income statement (using absorption costing)

Pro forma classified balance sheet

Answer rating: 100% (QA)

Lets assume the following information Sales Budget Quarterly sales figures Q1 25000 pucks Q2 35000 p... View the full answer