Accounting Question

there are 3 requirements including writing and excel sheet dot not use AI like chatgpt if you use i will request a refund immediately

Cost Accounting
Budgeting1
Objectives
•
•
•
Develop a dynamic operating budget for business planning and decision making.
Develop unstructured problem-solving skills.
Improve technical accounting and business knowledge.
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 2019, 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 2024
Today is November 1, 2023. A month ago, soon after he started working on the 2024 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 2024. Before Contador fell ill, he had prepared the
sales forecast for the first six months of 2024. 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
1
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
2024.
Lockden gave you the Excel template that Contador created and the following information:
• Expected sales in units for December 2023 and the first six months of 2024 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 Duralock for US$88.
Duralock requires two types of direct materials—LD-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 month’s 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 labor—LB075 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 2024 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 company’s 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 company’s sales volume. There are also reports of regional
2
conflicts in other parts of the world. Some experts say it might impact the economy in this
country soon.
Given these uncertainties, Lockden is interested in actively pursuing cost-reduction strategies to
protect the company’s 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 2024. 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
Requirement 1
Complete all worksheets in the Excel template.
a. Complete data worksheet using the information given in the info file.
b. Complete the remaining worksheets using your technical knowledge on operating budgets
and your Excel skills. All cells within the remaining worksheets should be formula-based and
properly cell-referenced. Do not enter any numbers in the remaining worksheets. Use MIN,
MAX, and ROUNDUP functions as needed when creating the direct materials and direct labor
budgets.
Requirement 2
Since its inception, Lockitt, Inc. has operated at a batch size of 50 units. The number was set at
50, based on the 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.
a. What would be the direct labor cost and variable overhead cost if Lockitt, Inc. changes the
batch size to 70 units?
b. 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.
3
Requirement 3
After you prepared the operating budget for the first quarter, Lockden learned that one of the
two molding machines used by the company needs a major overhaul soon. Since the company
is considering cost reduction strategies in response to the economic uncertainty, this news was
unfavorable. But Lockden realized that the overhaul is important and has decided to do the
overhaul during the first quarter of 2024. The overhaul will cost US$100,000, and the machine
will not be operational during the overhaul (i.e., the entire duration of the first quarter). The
company is exploring the viability of the following options to handle this operational challenge:
Option 1: Lockitt, Inc. will run the functional molding machine two shifts per day, instead of one.
Since LB075 labor is paid hourly, this proposal will not change the labor hours required. But the
company must pay a higher wage rate to the employees in the second shift, causing the average
LB075 rate to increase to $45 per hour. Changing to two shifts will increase the monthly fixed
manufacturing overhead costs by $15,000. Assume that this change has no impact on LH075
labor.
Option 2: An engineer has suggested that the company should increase the batch size of the
functional machine to 100 units during the overhaul of the other machine. To accommodate the
large batch size, settings of the functional molding machine need to be changed to reduce the
number of functions it performs. As a result, additional work will have to be completed using
manual finishing labor. Under this scenario, LH075 time required per unit will change to 15
minutes.
a. Which of these two options should the company adopt if the decision is strictly based on
financial factors (i.e., minimizing the cost)? Support your answer with calculations.
b. Given Lockden’s desire to pursue cost reduction strategies, he argues that the company
should choose the option where costs are minimized. But you realize that other factors
besides cost minimization should be considered wh
4
Lockitt Inc. 2024Operating Budget
Data Sheet
Revenue
Unit selling price
December sales volume
January sales volume
February sales volume
March sales volume
April Sales volume
May sales volume
June sales volume
Materials Quantity per Duralock
LD-paste (lbs.)
Chem-glue (ml)
Materials Unit Price
LD-paste: Price per pound
Chem-glue: Price per milliliter
Inventory Policy
Finished goods (%, units)
LD-paste (%)
Chem-glue (%, ml)
Labor Rate
LB075
LH075
Labor Usage
LB075 (hours per batch)
LH075 (Hours per unit)
Batch size
Duralocks per batch
Variable OH
Per batch
Per direct labor hour
Fixed Overhead
Per month
Budgeted
Input quantity per unit
of output
Price per unit of input
% of next period’s
requirement
Hourly rate
Min
Selling and Admin
Fixed, per month
Variable, percentage of revenue
Max
Standard Cost Sheet for the Product for Q1
Direct materials
LD-paste
Chem-glue
Direct Labor
LB075
LH075
Variable overhead cost
Batch-related
Variable overhead cost
Direct-labor related
Fixed overhead
Fixed overhead
Standard cost per unit
Quantity of
Input per
Duralock
Price per Unit
of Input
HRS per Unit
of Duralock
Labor Rate per
Hour
Cost per
Batch
Units per Batch
Cost per Hour Hours per Unit
Cost per
Quarter
Budgeted
Production for
the Quarter
Cost per
Unit of
Duralock
Sales Budget
January
Sales (units)
Selling Price
Sales Revenue ($)
February
March
QTR
April
May
Production Budget (all in Units)
January
Sales (Units)
Ending Inventory (with Constraint)
Beginning Inventory
Production
February March
QTR
April
May
Direct Materials Purchase Budget
January
Direct Materials Budget (LD-paste)
Quantity required for production (lbs.)
Ending inventory (lbs.)
Beginning inventory (lbs.)
Purchases (lbs.)
Cost ($)
Direct Materials Budget (Chem-glue)
Quantity required for production (ml)
Ending inventory (with constraint) (ml)
Beginning inventory (ml)
Purchases (ml)
Cost ($)
Total Direct Materials Purchase Cost
February
March
QTR
April
May
Direct Labor Budget
January
Direct Labor LB075
Batches of production (original)
Batches of production (whole number)
LB075 labor hours needed
Direct labor cost-LB075
Direct Labor LH075
Direct Labor hours needed
Direct labor cost LH075
Total labor cost
February
March
QTR
Manufacturing Overhead Budget
January
Number of batches
Total direct labor hours (LB075 and LH075)
Variable Overhead
Batch-related variable overhead cost
Direct labor hour-related variable overhead cost
Total variable OH cost
Fixed Overhead
Fixed overhead cost
Total MOH
February
March
QTR
S&A Budget
Revenue
January
Fixed S&A expenses
Variable S&A expenses
Total
February
March
QTR
Lockitt Inc.,
Budgeted Income Statement
Quarter ended March 31
%
Revenue
Cost of Goods Sold
Gross Profit
Selling and Admin Expense
Income from operations before tax