70.2K

Verified Solution

Link Copied!

Complete the Data worksheet in the Excel template using the information given in the case.

2. Complete the standard cost worksheet in excell and all subcomponents of the operating budget (subsequent worksheets) With Formula plssss

3. In preparing the income statement, assume that the standard cost per unit for 2019 is the same as that calculated for the first quarter of 2020. Every cell within the standard cost worksheet and budget subcomponents should be formula-based and properly cell-referenced. Do not enter any numbers in the worksheets, except for the Data worksheet. Use MIN, MAX, and ROUNDUP functions as needed when creating the direct materials and direct labor budgets.

4. 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 2020. 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:

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: - The company expects to sell each Duralock for US $88.1 - Duralock requires two types of direct materials-LD-paste and Chem-glue. Two pounds of LDpaste and 350ml 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 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: - The company expects to sell each Duralock for US $88.1 - Duralock requires two types of direct materials-LD-paste and Chem-glue. Two pounds of LDpaste and 350ml 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

Answer & Explanation Solved by verified expert

See Answer