Case Study

Duralock: Budgeting for Decision-Making

BACKGROUND

Lockitt, Inc., a small manufacturing company, produces a specialized sealant lock for hazardous chemical containers. It was founded by an innovative entrepreneur, Brett Lockden. Prior to starting Lockitt, Inc., Lockden had a long career in the petroleum industry. He had firsthand 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, Lockden spent a few years experimenting to create a spill- and leak-proof lock that is safe, efficient, and cost effective and fits commercial chemical containers.

In 2015, his efforts paid off when he was granted a patent for his invention, Duralock. Duralock 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, Lockden started his company, Lockitt, Inc. Ted Contador, a trusted former colleague, joined Lockden as his business partner. Being a former chemical industry insider, Lockden 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, Lockitt, Inc., was on a steady growth trajectory.

As do most small, privately held manufacturing companies, Lockitt, Inc., focused on its core operations and outsourced some of its other value-chain functions like human resources, IT, and sales and marketing. Lockden managed the manufacturing aspects of the business as well as the continuous improvement efforts. Contador 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, Contador had a medical emergency and has since been on long-term disability leave. He is expected to return to work in February 2020. Before Contador fell ill, he had prepared the sales forecast for the first six months of 2020. During the past summer, Contador attended an Excel for Business workshop.

Based on what he learned, Contador 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 Lockitt, Inc. since September, and you have done projects for both Lockden and Contador. Today, Lockden 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.

Lockden gave you the Excel template that Contador created and the following information:

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

December	January	February	March	April	May	June
15,200	15,800	16,500	16,600	17,800	17,700	17,800

The company expects to sell each Duralock for US$88.1

Duralock 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 Duralock.

At the end of each month, ending inventory of Duralock must be equal to 10% of the following months budgeted sales, or 1,600 (whichever is greater). Lockden is strict on this safety-stock policy; if any of his regular customers has an unexpected need for Duralock, Lockden wants to fulfill 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 liters. Lockitt, 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 liter, respectively.

The production process requires two types of direct laborLB075 and LH075. LB075 represents machinists who operate the molding machines. Molding is done in batches; each batch contains 50 Duralocks. One hour of LB075 labor is required to process one batch of Duralock. LH075 represents manual finishing labor. Each Duralock requires one-tenth of an hour of LH075 labor. 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 labor hour (based on total labor hours). Fixed factory overhead is $40,000 per month. Contador had been applying fixed factory overhead based on the budgeted production volume, and Lockden 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.

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

EXPECTATION

Lockden 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 Contador 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, Lockden 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, labor utilization, inventory policy, and supply chain. While he has many ideas, he needs assistance in evaluating their financial viability.

Lockden believes that a dynamic budget will be an effective tool to make 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, Lockden also wants to use it to carry out sensitivity analyses. Further, Lockden 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.

REQUIREMENTS:

1. Complete the Excel template: start by completing the Data tab, using information in the case. Next complete all other tabs, using Excel formulas - every cell within the standard cost tab, budget subcomponents, and Income Statement should be formula-based and properly cell-referenced. Do not type any numbers in the worksheets, EXCEPT for cells in the Data worksheet.

2. Create a copy of the Excel file you created in requirement 1. Make adjustments to data cells to answer the following question. Since its inception, Lockitt, Inc. has operated at a batch size of 50 units. The number was set at 50, based on production volume at that time. Founder Brett Lockden is interested in knowing the financial impact of increasing the batch size to 70, which is the maximum machine capacity at the current machine settings. What would be the direct labor cost and variable overhead cost if Lockitt, Inc. changes the batch size to 70 units? Given the management's desire to lower costs, would you recommend this change in batch size? Critically evaluate this decision considering both financial and nonfinancial factors. (Write your comments in a textbox in a new tab in the Excel worksheet)

3. Create a copy of the Excel file you created in requirement 1. Make adjustments to data cells to answer the following question. The government says that the viral pandemic is unlikely to impact the United States, but nongovernmental sources are predicting that the viral pandemic will impact the United States toward the end of the first quarter of 2020 and the economic impact will be visible in the second quarter of 2020. You realize that the sale projections that were given to you do not reflect the possible impact of the viral pandemic. After additional research, you assume that the monthly sales volume for quarter 2 will be 15% lower than the original projections if the virus spreads to the USA. Calculate the following, based on your modified expectations:

• Total cost of direct materials to be purchased for the first quarter
• Budgeted net income for the first quarter of 2020.
• Describe why the DM purchases and Income for Quarter 1 will be affected by a change in sales expectation for Quarter 2. (Write your comments in a textbox in a new tab in the Excel worksheet)

4. Create a copy of the Excel file you created in requirement 1. Make adjustments to data cells to answer the following question. As December approached, it became clear that the virus was affecting global Chem-glue suppliers. In response to this threat, Lockden wants to change the inventory policy relating to Chem-glue. For 2020, he wants to maintain the monthly ending inventory of Chem-glue equal to 10% of the following month's production needs.

• What would be the net income for the first quarter under this assumption?
• What would be the total cost of direct materials to be purchased for the first quarter under this assumption?
• Because of current global supply chain concerns, Lockden is reflecting on the sourcing decision he made several years ago. Due to a cheaper price for Chem-glue, Lockden opted for the global supplier. Now Lockden is considering changing to a local (domestic) supplier. The local supplier will allow Lockitt to maintain lower ending inventory of Chem-glue (to prevent obsolescence). Discuss the financial and nonfinancial implications of:
  • Global sourcing.
  • Holding inventory.
  • (Write your comments in a textbox in a new tab in the Excel worksheet)

\begin{tabular}{|c|c|c|c|} \hline \begin{tabular}{l} Lockitt Inc. 2020 Operating Budg \\ Data Sheet \end{tabular} & & & \\ \hline Revenue & Budgeted & Percentage Change & New \\ \hline Unit selling price & & & \\ \hline December sales volume & & & \\ \hline January sales volume & & & \\ \hline February sales volume & & & \\ \hline March sales volume & & & \\ \hline April Sales volume & & & \\ \hline May sales volume & & & \\ \hline June sales volume & & & \\ \hline Materials Quantity per Duralock & \begin{tabular}{l} Input quantity per \\ unit of output \end{tabular} & & \\ \hline LD-paste (Ibs.) & & & \\ \hline Chem-glue (ml) & & & \\ \hline Materials Unit Price & \begin{tabular}{l} Price per unit of \\ input \end{tabular} & & \\ \hline LD-paste: Price per pound & & & \\ \hline Chem-glue: Price per milliliter & & & \\ \hline Inventory Policy & requirement & Min & Max \\ \hline Finished goods (\%, units) & & & \\ \hline LD-paste (\%) & & & \\ \hline Chem-glue (%,ml) & & & \\ \hline Labor Rate & Hourly rate & & \\ \hline LB075 & & & \\ \hline LH075 & & & \\ \hline Labor Usage & & & \\ \hline LB075 (hours per batch) & & & \\ \hline LH075 (Hours per unit) & & & \\ \hline Batch size & & & \\ \hline Duralocks per batch & & & \\ \hline Variable OH & & & \\ \hline Per batch & & & \\ \hline Per direct labor hour & & & \\ \hline Fixed Overhead & & & \\ \hline Per month & & & \\ \hline Selling and Admin & & & \\ \hline Fixed, per month & & & \\ \hline Variable, percentage of revenue & & & \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|} \hline \multicolumn{4}{|c|}{ Standard Cost Sheet for the Product for Q1 } \\ \hline Direct materials & \begin{tabular}{l} Quantity of \\ Input per \\ Duralock \end{tabular} & \begin{tabular}{l} Price per Unit \\ of Input \end{tabular} & \begin{tabular}{l} Cost per \\ Unit of \\ Duralock \end{tabular} \\ \hline \multicolumn{4}{|l|}{ LD-paste } \\ \hline \multicolumn{4}{|l|}{ Chem-glue } \\ \hline Direct Labor & \begin{tabular}{l} HRS per Unit \\ of Duralock \end{tabular} & \begin{tabular}{l} Labor Rate per \\ Hour \end{tabular} & \\ \hline \multicolumn{4}{|l|}{ LB075 } \\ \hline \multicolumn{4}{|l|}{ LH075 } \\ \hline Variable overhead cost & \begin{tabular}{l} Cost per \\ Batch \end{tabular} & \begin{tabular}{l} Units per \\ Batch \end{tabular} & \\ \hline \multicolumn{4}{|l|}{ Batch-related } \\ \hline Variable overhead cost & Cost per Hour & Hours per Unit & \\ \hline \multicolumn{4}{|l|}{ Direct-labor related } \\ \hline Fixed overhead & \begin{tabular}{l} Cost per \\ Quarter \end{tabular} & \begin{tabular}{l} Budgeted \\ Production for \\ the Quarter \end{tabular} & \\ \hline \multicolumn{4}{|l|}{ Fixed overhead } \\ \hline Standard cost per unit & & & \\ \hline \end{tabular} Sales Budget \begin{tabular}{|l|l|l|l|l|l|} \hline \\ Sales (units) & January & February & March & \\ \hline Selling Price & & & & & \\ \hline Sales Revenue (\$) & & & & & \\ \hline & & & & & \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|} \hline \multicolumn{7}{|l|}{ Production Budget (all in Units) } \\ \hline & January & February & March & QTR & April & May \\ \hline \multicolumn{7}{|l|}{ Sales (Units) } \\ \hline \multicolumn{7}{|l|}{ Ending Inventory (with Constraint) } \\ \hline \multicolumn{7}{|l|}{ Beginning Inventory } \\ \hline \multicolumn{7}{|l|}{ Production } \\ \hline & & & \begin{tabular}{l} Cross- \\ footing \\ check \end{tabular} & & & \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|l|}{ Manufacturing Overhead Budget } \\ \hline & January & February & March & QTR & \\ \hline \multicolumn{6}{|l|}{ Number of batches } \\ \hline \multicolumn{6}{|l|}{ Total direct labor hours (LB075 and LH075) } \\ \hline \multicolumn{6}{|l|}{ Variable Overhead } \\ \hline Batch-related variable overhead cost & & & & & Cross-footing check \\ \hline Direct labor hour-related variable overhead cost & & & & & Cross-footing check \\ \hline \multicolumn{6}{|l|}{ Total variable OH cost } \\ \hline \multicolumn{6}{|l|}{ Fixed Overhead } \\ \hline \multicolumn{6}{|l|}{ Fixed overhead cost } \\ \hline \multicolumn{6}{|l|}{ Total MOH } \\ \hline & & & \begin{tabular}{l} Cross-f \\ check \end{tabular} & & \\ \hline \end{tabular} \begin{tabular}{l|l|l|l|l|} \hline \multicolumn{1}{l|}{ S\&A Budget } & \multicolumn{5}{l|}{} \\ \cline { 2 - 6 } Revenue & & & & \\ \hline & January & February & March & QTR \\ \hline Fixed S\&A expenses & & & & \\ & & & & \\ Variable S\&A expenses & & & & \\ \hline Total & & & & \\ \cline { 2 - 5 } & & & & \\ \hline \end{tabular} Crossfooting check \begin{tabular}{|c|c|} \hline \begin{tabular}{l} Lockitt Inc., \\ Budgeted Income Staten \end{tabular} & \\ \hline Quarter ended March & \\ \hline & % \\ \hline Revenue & \\ \hline Cost of Goods Sold & \\ \hline Gross Profit & \\ \hline Selling and Admin Expense & \\ \hline Income from operations before tax & \\ \hline \end{tabular}