I need help with this Budget Modeling Assignment.

I need this done exactly as it asks and with every white cell containing the appropriate Excel Formula.

I will post screenshots of all the Excel slides here, as well as the excel slides copied and pasted underneath to make the process faster. I will also included the reference pdf for the multiple questions. Everything I share should be all that's needed to complete this assignment. I will also identify every Excel tab/page in capital letters to help keep things organized. Please make sure all formulas are shown clearly so I can input them on my end.

1, START

Pg. 1 of 4 Direct Labor Budget To begin working on the Direct Labor Budget: D Open your activated project workbook, and enable macros. D Click the Inputs tab D Click the Set Input Values Back to Original Amounts | button D Click the DirLab tab Overview of the Direct Labor Budget The Direct Labor Budget is the fourth budget in MyArmor's Master Budget. The input that drives this budget is Finished Units to Produce, which comes from the Production Budget. This is the same input that drove the Raw Materials Budget that you just finished. The fact that Finished Units to Produce is the main driver of both the Raw Materials Budget and the Direct Labor Budget makes sense-the Raw Materials Budget is determining how much PsP (raw material) MyArmor needs to purchase to produce the Finished Units, and the Direct Labor Budget is determining how much Direct Labor MyArmor needs to purchase to produce the Finished Units. When you "Wire-Up" the Direct Labor Budget you will observe that it is much simpler than the Raw Materials Budget. The Direct Labor Budget is much simpler, because there is no Beginning Inventory of Direct Labor Hours, and there is no Ending Inventory of Direct Labor Hours. The Direct Labor Budget has two main purposes: D To calculate, the required Total Direct Labor Hours each quarter and for the full year. D To calculate, Total Direct Labor $'s each quarter and for the full year. Observe on the screen how the Direct Labor Budget is broken into two calculation blocks: 1st The calculation of: Total Direct Labor Hours This is the first 3 lines of the Direct Labor Budget. 2nd The calculation of: Total Direct Labor $'s This is the last 3 lines of the Direct Labor Budget. Let's utilize a simple example to understand the above two calculation blocks; and thus understand the Direct Labor Budget. This example is for one quarter. This data is not for MyArmor-this data is for Simple Company. (the amounts are small to make the math easy to see) D Finished Units to Produce during the quarter ............. 100 units Direct Labor Hours Required per Finished Unit.... .50 hrs per unit Average Cost of a Direct Labor Hour . wwwww....: $10 per hr 1" Calculation Block - Total Direct Labor Hours The first block calculates the Total Direct Labor Hours required to produce the Finished Units. The information above shows that there will be 100 Finished Units produced during the quarter, and that it takes .50 hours of Direct Labor to produce each Finished Unit. Thus there will be 50 hours of Direct Labor needed to produce the 100 Finished Units. 100 Finished Units x .50 hours of Direct Labor per Finished Unit = 50 hours of Direct LaborPg. 1 of 5 Production Budget To begin working on the Production Budget: D Open your activated project workbook, and enable macros. Click the Inputs tab D Click the Set Input Values Back to Original Amounts button Click the Prod tab Overview of the Production Budget The Production Budget is the second budget in MyArmor's Master Budget. All unit amounts shown on this budget are Finished Cell Phone Covers. The main purpose of the Production Budget is to calculate how many cell phone covers (Finished Units) MyArmor will need to produce each quarter. The starting point for the Production Budget is Sales in Units, which is an input from the Sales Budget. If MyArmor started each quarter with no Finished Units in inventory (no beginning inventory) and did not need to have any Finished Units in inventory at the end of each quarter (no ending inventory) - then the Finished Units to Produce would simply be equal to Sales in Units. But MyArmor does need to have Ending Inventory of Finished Units, and does have Beginning Inventory of Finished Units. Thus the Production Budget: Starts with units that will be sold during the qtr. - Sales in Units DAdds units needed in inventory at the end of the qtr. - Units Needed for Ending Inventory D Subtracts units in inventory at the start of the qir. - Units Available from Beginning Inventory D To arrive at units to produce during the qtr. - Finished Units to Produce Let's look at a simple example to demonstrate the logic used in the Production Budget. This data is not for MyArmor-this data is for Simple Company. (the amounts are small to make the math easy to see) Units sold during the quarter . . 100 > Units Needed in Ending Inventory . ...... 15 Units in Beginning Inventory . 20 The company will sell 100 units during the quarter and needs to have 15 units in Ending Inventory. If the company did not have any inventory at the beginning of the quarter, the Finished Units to Produce for the quarter would simply be 115 units (100 units to sell + 15 units needed in ending inventory). But the company does have 20 units in inventory at the beginning of the quarter; thus they would need to produce only 95 units (115 units - 20 units available from beginning inventory). The above logic is used in the Production Budget-that you will now "Wire-Up".Manufacturing Overhead Budget - Pg. 3 of 7 To calculate the, Predetermined Overhead Rate per Direct Labor Hour for the full year. Let's utilize a simple example to understand the Manufacturing Overhead Budget. This example is for one quarter. This data is not for MyArmor-this data is for Simple Company. (the amounts are small to make the math easy to see) Total Direct Labor Hours for the quarter.. ...... 100 direct labor hours D Variable Overhead Cost Rate per Direct Labor Hour ..... $2 per direct labor hour D Total Fixed Manufacturing Overhead Cost per quarter..... $50 Calculation of Total Variable Manufacturing Overhead Cost for the Quarter The company uses Direct Labor Hours as the "cost driver" to estimate Total Variable Manufacturing Overhead Cost. Based on the above data, the estimated Total Variable Manufacturing Overhead cost for the quarter would be $200. 100 direct labor hours x $2 variable overhead cost per direct labor hour = $200 total variable manufacturing overhead cost for the quarter Calculation of Total Manufacturing Overhead Cost for the Quarter The calculation of Total Manufacturing Overhead Cost for the quarter is simply Total Variable Manufacturing Overhead Cost plus Total Fixed Manufacturing Overhead Cost. $200 total variable manufacturing overhead cost + $50 total fixed manufacturing overhead cost = $250 total manufacturing overhead cost for the quarter You will now "Wire-Up" the Manufacturing Overhead Budget and the two related schedules located below the budget. "Wiring" the Manufacturing Overhead Budget Utilize the following: D Your understanding based on the simple example above. D The section Details for Where A Line Item Comes From or How A Line Item is Calculated starting on Pg. 6. D Tip box ACash Budget - Pg. 2 of 8 D Calculation of Ending Cash Balance (interest is calculated in this section) . This Section starts with Cash Balance before Paying Interest. . Calculates if any interest is due to the bank on the bank loan. If interest is due, the amount of Interest (to be) Paid on the Bank Loan at the End of the Quarter is calculated and subtracted from Cash Balance before Paying Interest. This calculation logic will be discussed in Tip box c] and in the example starting on this page. . Ends with Ending Cash Balance-the amount of Cash the company will have at the end of the quarter after making the Cash interest payment to the bank. D Loan Schedule Calculation of "Ending Loan Balance After Additional Borrowings and Repayments" . This Section starts with Beginning Loan Balance Before Additional Borrowings and Repayments. This is the amount the company owes to the bank at the beginning of the quarter. Note that the amount owed to the bank at the beginning of the quarter is the same amount that the company owed to the bank at the end of the previous quarter. . Adds Additional Borrowings from the bank during the quarter. This amount was calculated in the Cash Budget above. . Subtracts Repayments made to the bank during the quarter. This amount was calculated in the Cash Budget above . Ends with Ending Loan Balance After Additional Borrowings and Repayments. This is the amount the company owes to the bank at the end of the quarter. Let's utilize a simple example to understand these four calculation sections; and thus understand the Cash Budget and the Loan Schedule. This example will be for two quarters. In the first quarter, Simple Company will need to borrow Cash from the bank. In the second quarter, Simple Company will be able to make a repayment to the bank. This data is not for MyArmor-this data is for Simple Company. The following data is for the 1" quarter of the example: (the amounts below are small to make the math easy to see) D Beginning Cash Balance as of January 1, 2023 ............. $200 beginning of the 1* quarter Cash Collections during 1st qtr. ....... $500 during the 1st qtr. D Payments during 1st qtr. (this is the total of all Payments).... $600 this is the total of payments for Raw Materials, Direct Labor, Mig. Overhead, etc. D End-of-quarter minimum required Cash Balance (before paying interest) ...$300 at the end of each quarter D Annual Interest charged by the bank on the loan .... 8% annual rate for 2023 D Beginning Loan Balance as of January 1, 2023 ..":.... $400 beginning of the 1st quarter Utilizing the above data, the four calculation sections for the 1" quarter are shown on the next page. Cash Budget - Pg. 3 of 8 1" Qtr. Calculation of Excess Cash or (Deficiency of Cash) before Borrowings, Repayments, Interest Based on the data from Pg. 2: The company started the quarter with a Cash balance of $200 During the quarter the company collected $500 of Cash from customers, and paid-out $600 of Cash for Raw Materials, Direct Labor, Mfg. Overhead, etc. The Excess Cash or (Deficiency of Cash) before Borrowings, Repayments, Interest is calculated on the Cash Budget as follows: Beginning Cash Balance .... $200 Cash Collections.. ...... 500 Payments for Raw Materials, Direct Labor, Mig. Overhead, etc ..... (600) = Excess Cash or (Deficiency of Cash) before Borrowings, Repayments, Interest . $100SG&A Budget - Pg. 2 of 4 The President's salary is normally determined for the planning year (the year covered by the Master Budget) by a compensation committee. If Sales in Units increase or decrease during the planning year, her salary normally will not be changed. Thus the cost of the President's salary is an example of an SG&A Fixed Cost. Let's utilize a simple example to understand the SG&A Budget. This example is for one quarter. This data is not for MyArmor-this data is for Simple Company. (the amounts are small to make the math easy to see) Total Sales in Units for the quarter .... 100 units sold D Variable SG&A Cost per Unit Sold. $3 per unit sold Total SG&A Fixed Cost per quarter... $50 Calculation of Total SG&A Variable Cost for the Quarter The company uses Sales in Units as the indicator of "level of activity" (the "cost driver") to estimate Total SG&A Variable Cost. The Variable SG&A Cost rate of $3 per unit sold covers all of Simple Company's Variable SG&A costs. The estimated Total SG&A Variable cost for the quarter would be $300. 100 units sold x $3 variable SG&A cost per unit sold = $300 total SG&A variable cost for the quarter Calculation of Total SG&A Cost for the Quarter The calculation of Total SG&A Cost for the quarter is Total SG&A Variable Cost plus Total SG&A Fixed Cost. $300 total variable SG&A cost + $50 total fixed SG&A cost = $350 total SG&A cost for the quarter You will now "Wire-Up" the SG&A Budget and the schedule located below the budget.Pg. 1 of 5 Finished Goods Inventory Budget To begin working on the Finished Goods Inventory Budget: D Open your activated project workbook, and enable macros. Click the Inputs tab Click the Set Input Values Back to Original Amounts | button Click the FinGoods tab Overview of the Finished Goods Inventory Budget The Finished Goods Inventory Budget is the sixth budget in MyArmor's Master Budget. The purpose of this budget is to calculate the Production Cost of a finished unit (a cell phone cover), and then use that per unit cost to value the finished units that are in inventory as of December 31, 2023-the end of MyArmor's profit planning year. As previously discussed in the Manufacturing Overhead Budget document, there are three categories of manufacturing costs: Raw Material Direct Labor DManufacturing Overhead As you will see in the simple example below, and when you prepare the Finished Goods Inventory Budget, an amount for each of these three manufacturing costs will be assigned to a unit of Finished Goods-in the case of MyArmor, to a finished cell phone cover. Let's utilize a simple example to understand the Finished Goods Inventory Budget. This data is not for MyArmor-this data is for Simple Company. Simple Company: Assigns Manufacturing Overhead costs to Finished Units based on a Predetermined Overhead Rate per Direct Labor Hour. The calculation of this rate uses full year Total Manufacturing Overhead Cost and full year Total Direct Labor Hours; thus the calculated rate is an estimate for all of 2023. The calculated rate is given to you below. D Uses the First-In, First-Out (FIFO) cost flow assumption to determine the value of Finished Goods Inventory. As a result of using the FIFO cost flow assumption, Finished Goods Inventory is valued using the most recent costs, which are the: . 4" quarter cost per pound for Raw Material . 4"h quarter cost per hour for Direct Labor . Predetermined Overhead Rate per Direct Labor Hour for Manufacturing Overhead (the amounts below are small and round to make the math easy to see) D Pounds of Raw Material required for a finished unit.......... 4 pounds per finished unit D 4th qtr. cost of a pound of Raw Material... $3 per pound of Raw Material D Hours of Direct Labor required for a finished unit...... 2 hours per finished unit D 4th qtr. cost of an hour of Direct Labor...... $10 per hour of Direct Labor Predetermined Overhead Rate per Direct Labor Hour ..... $20 per Direct Labor Hour D Finished Units in inventory as of Dec. 31, 2023 ....... 100 Finished Units Finished Goods Inventory Budget - Pg. 2 of 5 Using the data on Pg. 1 we can calculate the 4" quarter cost of producing one Finished Unit: 4 pounds of raw material x $3 per pound = $12 of Raw Material 2 hours of direct labor x $10 per hour = $20 of Direct Labor 2 direct labor hours x $20 of overhead per direct labor hour = $40 of Manufacturing Overhead $72 Total Cost of a Finished Unit If the total 4" quarter cost of producing 1 Finished Unit is $72, then the value of the 100 Finished Units in inventory as of December 31, 2023 is: 100 Finished Units x $72 per Finished Unit = $7,200Pg. 1 of 5 Cost of Goods Sold Budget To begin working on the Cost of Goods Sold Budget: D Open your activated project workbook, and enable macros. D Click the Inputs tab Click the Set Input Values Back to Original Amounts| button D Click the CofGS tab Overview of the Cost of Goods Sold Budget The Cost of Goods Sold Budget is the seventh budget in MyArmor's Master Budget. All inputs for this budget come from other budgets-or from the Prior Year Balance Sheet at the bottom of the Inputs tab. This budget calculates Cost of Goods Sold for the Forecasted Income Statement. There are three calculation blocks in this budget. Each calculation block relates to one of the three types of inventory. The Raw Materials calculation block passes an amount to Work-in-Process The Work-in-Process calculation block passes an amount to Finished Goods. The Finished Goods calculation block completes the Cost of Goods Sold calculation. Let's utilize a simple example to understand the three calculation blocks; and thus understand the Cost of Goods Sold Budget. This data is not for MyArmor-this data is for Simple Company. (the amounts below are small to make the math easy to see) D Raw Materials Inventory as of January 1, 2023 .. $10 at the beginning of the year D Total Cost of Raw Materials Purchases for 2023 ........... $100 for all of 2023 >Raw Materials Inventory as of December 31, 2023........ $20 at the end of the year Work-in-Process Inventory as of January 1, 2023 ..... $0 at the beginning of the year D Total Direct Labor $'s for 2023 $300 for all of 2023 D Total Manufacturing Overhead Cost for 2023 .... $600 for all of 2023 D Work-in-Process Inventory as of December 31, 2023 ..... $0 at the end of the year Finished Goods Inventory as of January 1, 2023 ..... $50 at the beginning of the year Finished Goods Inventory as of December 31, 2023 ..... $40 at the end of the year Let's utilize the above data to calculate the three sections of the Cost of Goods Sold Budget, and arrive at Simple Company's Cost of Goods Sold for 2023. The three calculation blocks are shown on the next page. Cost of Goods Sold Budget - Pg. 2 of 5 Raw Materials Inventory calculation block The company started the year with $10 in Raw Materials Inventory. During all of 2023 the company purchased $100 of additional Raw Materials. Thus the company had a total of $110 of Raw Materials that could have been put into the production process. At the end of 2023 the company had $20 of Raw Materials remaining in inventory. Thus the company did put $90 into the production process. The following is how this $90 is calculated on the Cost of Goods Sold Budget: Raw Materials Inventory as of January 1, 2023 ..... .. $ 10 + Total Cost of Raw Materials Purchased for 2023.. ...... 100 - Raw Materials Inventory as of December 31, 2023 = Cost of Raw Materials Transferred to Work-in-Process Inventory ... $ 90Pg. 1 of 7 Raw Materials Budget To begin working on the Raw Materials Budget: D Open your activated project workbook, and enable macros. D Click the Inputs tab DClick the Set Input Values Back to Original Amounts | button Click the RawMat tab Overview of the Raw Materials Budget The Raw Materials Budget is the third budget in MyArmor's Master Budget. The primary input that drives this budget is Finished Units to Produce, which comes from the Production Budget. The Raw Materials Budget has two main purposes: D To calculate, Total Ounces of PsP to Purchase each quarter and for the full year. To calculate, Total Cost of PsP Purchases each quarter and for the full year. Observe on the screen how the Raw Materials Budget is broken into three calculation blocks: 1st The calculation of: Ounces of PsP Required to Produce the Finished Units This is the top 3 lines of the Raw Materials Budget. 2nd The calculation of: Total Ounces of PsP to Purchase This is the middle 4 lines of the Raw Materials Budget. The logic in these 4 lines is the same logic that is used in the Production Budget. 3rd The calculation of: Total Cost of PsP Purchases This is the last 3 lines of the Raw Materials Budget. Let's utilize a simple example to understand the above three calculation blocks; and thus understand the Raw Materials Budget. This example is for one quarter. There is just one Raw Material item used in the production of the Finished Units, and that Raw Material item's unit-of- measure is pounds. This data is not for MyArmor-this data is for Simple Company. (the amounts are small to make the math easy to see) D Finished Units to Produce during the quarter ...... ........:: 100 units Pounds of Raw Material Required per Finished Unit..... ... 2 pounds per unit Pounds of Raw Material Needed in Ending Raw Materials Inventory ....... 25 pounds Pounds of Raw Material in Beginning Raw Materials Inventory.... ....... 15 pounds Cost of Raw Material per Pound . .... $3 per pound 1" Calculation Block - Raw Material Required to Produce the Finished Units The first block calculates the pounds of Raw Material required to produce the Finished Units. The information above shows that there will be 100 Finished Units produced during the quarter, and that it takes 2 pounds of Raw Material to produce each Finished Unit. Thus there will be 200 pounds of Raw Material used to produce the 100 Finished Units. 100 Finished Units x 2 pounds of Raw Material per Finished Unit = 200 pounds of Raw Material