James Lock, Inc., a small manufacturing company, produces a specialized sealant lock for hazardous chemical containers. It was founded by an innovative entrepreneur, Arthur James. Prior to starting James Lock, Inc., James had a long career in the petroleum industry. He had first-hand knowledge and experience working with hazardous chemicals and understood the need for safe handling, storage, and transportation of these materials. After retiring from his position with one of the largest petroleum processing companies in the country, James spent a few years experimenting with creating a spill- and leak-proof lock that is safe, efficient, and cost-effective and fits commercial chemical containers.

2015, his efforts paid off when he was granted a patent for his invention, Superlock. Superlock is a specialized, single-use sealant lock for commercial chemical containers that prevents liquid chemicals from spilling and fumes from escaping. After receiving the patent, James started his company, James Lock, Inc. Ted Hendrick, a trusted former colleague, joined James as his business partner. Being a former chemical industry insider, James had enough connections to spread word-of-mouth about his product. Additionally, his patented product met an unfilled demand that existed across many chemical industries, including petroleum processing, chemical manufacturing, and metal production and fabrication. Therefore, from its inception, James Lock, Inc. has been on a steady growth trajectory.

do most small, privately held manufacturing companies, James Lock, Inc. focused on its core operations and outsourced some of its other value-chain functions like human resources, IT, and sales and marketing. James managed the manufacturing aspects of the business as well as the continuous improvement efforts. Hendrick handled the financial aspects and customer service. They jointly handled strategic planning and governance.

PLANNING FOR 2020

Today is November 1, 2019. A month ago, soon after he started working on the 2020 operating budgets, Hendrick had a medical emergency and has since been on long-term disability leave. He is expected to return to work in February 2020. Before Hendrick fell ill, he had prepared the sales forecast for the first six months of 2020. During the past summer, Hendrick attended an Excel for Business workshop.

Based on what he learned, Hendrick intended to create a dynamic operating budget, which can be used for sensitivity analysis and decision-making purposes. He had just started the Excel template for the operating budget when he fell ill.

CURRENT SITUATION

You have been working as a business co-op student at James Lock, Inc. since September, and you have done projects for both James and Hendrick. Today, James called you to his office and asked about your understanding of operating budgets and Excel. Based on your answers, he was convinced that you are competent to create an operating budget for the first quarter of 2020. James gave you the Excel template that Hendrick created and the following information:

Expected sales in units for December 2019 and the first six months of 2020 are as follows:

December = 15,200

January = 15,800

February = 16,500

March = 16,600

April = 17,800

May = 17,700

June = 17,800

The company expects to sell each Superlock for US$88.

Superlock requires two types of direct materialsLD-paste and Chem-glue. Two pounds of LD- paste and 350 ml of Chem-glue are required to produce one Superlock.

At the end of each month, the ending inventory of Superlock must be equal to 10% of the following months budgeted sales, or 1,600 (whichever is greater). James is strict on this safety-stock policy; if any of his regular customers have an unexpected need for Superlock, James wants to fulfil that need.

The inventory policy for raw materials is to maintain the monthly ending inventory at 5% of the production needs for the next month. Chem-glue tends to dry up over time. Therefore, the monthly ending inventory of Chem-glue should not exceed 300 litres.

James Lock, Inc. expects all inventories at the end of December to be within these guidelines.

The cost of LD-paste and Chem-glue are $10.25 per pound and $50 per litre, respectively.

The production process requires two types of direct labourLB075 and LH075. LB075 represents machinists who operate moulding machines. Moulding is done in batches; each batch contains 50 Superlocks. One hour of LB075 labour is required to process one batch of Superlock. LH075 represents manual finishing labour. Each Superlock requires one-tenth of an hour of LH075 labour. Hourly wage rates for LB075 and LH075 are $30 and $15 per hour, respectively. Both groups of employees are paid on an hourly basis.

Variable manufacturing overhead is caused by batch-level and unit-level activities. The predetermined variable overhead rate at the batch level is $1,000 per batch; at the unit level, it is $50 per direct labour hour (based on total labour hours). Fixed factory overhead is $40,000 per month. Hendrick had been applying fixed factory overhead based on the budgeted production volume, and James instructed you to do the same.

The budgeted fixed selling and administrative expenses for the year are $600,000; this amount is to be uniformly divided across 12 months. Variable selling expenses for 2020 are expected to be 3% of the revenue.

James Lock, Inc. uses the first-in-first-out (FIFO) cost-flow assumption.

EXPECTATION

James explained that the companys product differentiation strategy had worked well in the past, and further growth can be expected if the macroeconomic conditions stay stable. But he is concerned about a predicted slowdown in the U.S. economy and potential turmoil in the oil markets. These uncertainties were not factored in the sales forecast that Hendrick prepared but could negatively impact the companys sales volume. There are also reports of a viral epidemic in other parts of the world. Some health experts say it might spread in this country soon.

Given these uncertainties, James is interested in actively pursuing cost-reduction strategies to protect the companys profit margin. He expressed the need to critically evaluate the current manufacturing process against possible alternatives. Specifically, he raised concerns about the efficiency of the current production schedule, labour utilization, inventory policy, and supply chain. While he has many ideas, he needs assistance in evaluating their financial viability.

James believes that a dynamic budget will be an effective tool for making informed decisions. It may especially be helpful in the upcoming year, as it can be used to evaluate the financial impact of various cost-reduction options. Given the uncertainties the company is facing, James also wants to use it to carry out sensitivity analyses. Further, James expressed his intention to hire you as a full-time financial analyst after graduation. He ended the meeting with this remark: You may have considered the operating budget merely as a planning tool. However, in addition to planning, I intend to use it heavily for decision-making and risk analysis, especially given the uncertainties we have regarding 2020. You are the owner of this tool, and after you create the tool, please be prepared to answer any what-if questions that I throw at you.

Prepare an operating budget (using the Excel template) that includes the following -

Standard Cost Sheet

Sales Budget

Production Budget

Direct Material Purchase Budget

Direct Labour Budget

Manufacturing Overhead Budget

Selling and Administrative Budget

Income Statement