Answered step by step
Verified Expert Solution
Question
1 Approved Answer
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
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 all the Excel slides here and 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.
1, START
A B C D E F G H Manufacturing Overhead Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2023 Test My Budget MyArmor, Inc. Quarter in the 2023 Planning Year Full Year 1st 2nd 3rd 4th Total Total Direct Labor Hours (from Direct Labor Budget) times Variable Overhead Cost Rate per Direct Labor Hour (DLH) equals Total Variable Manufacturing Overhead Cost NOGREGGAGNE SOONQUAN plus Total Fixed Manufacturing Overhead Cost Total Manufacturing Overhead Cost Check Amount + $61,760 Check Amount + $268,880 Calculation of Cash Payments for Manufacturing Overhead - for the Cash Budget Full Year 1st Qtr. 2nd Qtr. 3rd Qtr. 4th Qtr. Total Total Manufacturing Overhead Cost (per above) less Depreciation Included in Manufacturing Overhead ( ) Total Cash Payments for Manufacturing Overhead Check Amounts + $54,760 $62,200 $65,080 $58.840 Fix the Format of All White Cells Calculation of the Predetermined Overhead Rate - for the Finished Goods Ending Inventory Budget and Restore the Formula in Cell D28 Full Year Total Manufacturing Overhead Cost divided by Full Year Total Direct Labor Hours equals Predetermined Overhead Rate per Direct Labor Hour (DLH) | $0 per DLH less Units Available from Beginning Inventory ( ) Finished Units to Produce Check Amounts 9,800 units 16,000 units 18,400 units 13,200 units Fix the Format of All White Cells 13 14 15 16 Multiple Choice Questions 18 1. Using the data in the simple example on Pg. 1 of the Production Budget.pdf document, Answer: 19 what would be Finished Units to Produce if Units in Beginning Inventory were 5 rather than 20? 20 A. 110 21 B. 100 22 C. 90 23 D. 80 25 2. Which one of the following statements is correct? Answer: 26 A. Finished Units to Produce on the Production Budget will be an input to the Direct Labor Budget. 27 B. Total Dollar Sales on the Sales Budget is an input to the Production Budget. 28 C. Finished Units to Produce on the Production Budget will be an input to the Cash Budget. 29 D. None of the above statements are correct. 30B C D E F G H N Selling, General, and Administrative (SG&A) Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2023 MyArmor, Inc. Test My Budget Quarter in the 2023 Planning Year Full Year 1st 2nd 3rd 4th Total SG&A Variable Cost Sales in Units (from Sales Budget) times Variable SG&A Cost per Unit Sold Total SG&A Variable Cost SG&A Fixed Costs Advertising Cost Salary Cost Mall Kiosk Rental Cost Utilities Cost Property Taxes Depreciation Total SG&A Fixed Cost Total SG&A Cost Check Amounts + $100,000 $166,000 Fix the Format of All White Cells Calculation of Cash Payments - for the Cash Budget Full Year 1st Qtr. 2nd Qtr. 3rd Qtr. 4th Qtr. Total Total SG&A Cost (per above) less Depreciation Included in Total SG&A Cost () Total Cash Payments for SG&A 30 Check Amounts + $116,000 $127,000 31 32 Multiple Choice Questions 35 1. Using the data in the simple example on Pg. 2 of the SG&A Budget.pdf document, Answer: 36 what would be Total SG&A Cost if Variable SG&A Cost per Unit Sold was $2 rather than $3? 37 A. $150 38 B. $250 39 C. $350 40 D. $450 42 2. Which one of the following statements is correct? Answer: 43 A. Total SG&A Fixed Cost changes based on the level of activity. 44 B. Total SG&A Variable Cost changes based on the level of activity. 45 C. Total SG&A Cost is an input to the Cash Budget. 46 D. None of the above statements are correct.B C E F G H Inputs (Assumptions) for the Profit Planning Year Jan. 1 to Dec. 31, 2023 MyArmor, Inc. Set Input Values Back to Original Amounts As o Applies to Fix the Format of All Yellow Cells Dec. 31 All Qtrs. Quarter in the 2023 Planning Year 1st Quarter 2022 in 2023 1st 2nd ard of 2024 Sales Budget Sales in Units 10,000 units 14,000 units 16,000 units 18,000 units Average Selling Price per Unit 530 per unit $30 per unit $32 per unit $32 per unit Cash Collection Pattern for Sales in the: 1st Quarter 60% 109 Remainder 2nd Quarter 60% Remainder 3rd Quarter 80% Remainder 4th Quarter 70% Remainder 15 Production Budget Finished Units in Inventory - as of 12/31/2022 (the prior year) 3,000 units Forecasted Sales in Units - 1st Quarter of 2024 (next year) 12,000 units % of the Next Qtr.'s Sales in Units to Have in This Qtr.'s Ending Inventory 20% 30% 40% 20% Raw Materials Budget 22 Ounces of PlasticSteel Powder (PsP) Required per Finished Unit 10 oz % of Next Qtr.'s "Ounces of PSP Required" to Have in This Qtr's Ending Inventory 20% 24 Ounces of PSP in Raw Materials Inventory - as of 12/31/2022 (the prior year) 15,000 oz Forecasted "Ounces of PSP Required" in the 1st Qtr of 2024 (next year) 130,000 oz 26 Cost of PSP per Ounce $1.20 per oz $1.10 per oz $1.10 per oz $1.20 per oz Cash Payment Pattern for PSP Purchased in the: 1st Quarte 40%% 30% Remainder 28 2nd Quarter 60% 20% Remainder 3rd Quarter 70% Remainder 30 4th Quarter 80% Remainder Direct Labor Budget Direct Labor Hours (hrs) per Finished Unit 0.20 hrs 34 Average Cost of a Direct Labor Hour $18 per hour $20 per hour $20 per hour $18 per hour 35 36 Manufacturing Overhead Budget 37 Variable Overhead Cost Rate per Direct Labor Hour (DLH) $6 per DLH 38 Total Fixed Manufacturing Overhead Cost per Quarter $50,000 39 Depreciation per Quarter Included in Fixed Manufacturing Overhead $7,000 40 Finished Goods Inventory Budget 42 No Required Input Data 43 44 Cost of Goods Sold Budget 45 Only Required Input Data is on the Prior Year Balance Sheet belowB C D E F G H Raw Materials Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2023 MyArmor, Inc. Test My Budget SOO YOU A WN Quarter in the 2023 Planning Year Full Year 1st 2nd 3rd 4th Total Finished Units to Produce (from Production Budget) times Ounces of PlasticSteel Powder (PSP) Required per Finished Unit Ounces of PSP Required to Produce the Finished Units Ounces of PSP Required to Produce the Finished Units plus Ounces of PSP Needed in Ending Raw Materials Inventory less Ounces of PSP Available from Beginning Raw Materials Inventory () Total Ounces of PSP to Purchase Total Ounces of PSP to Purchase times Cost of PSP per Ounce A Total Cost of PsP Purchases 20 Check Amount $138,000 Check Amount $668, 160 Fix the Format of All White Cells 23 Calculation of Cash Payments for Raw Materials - for the Cash Budget 24 1st Qtr. 2nd Qtr. 3rd Qtr. 4th Qtr. Payment of December 31, 2022 Accounts Payable Payment of 1st Qtr PSP Purchases Payment of 2nd Qtr PSP Purchases Payment of 3rd Qtr PSP Purchases Partial Payment of 4th Qtr PSP Purchases Total Cash Payments for PsP Check Amounts - $83,200 $150, 168 $211,328 $219,880 Calculation of December 31, 2023 Accounts Payable - for the Balance Sheet 4th Quarter Total Cost of PsP Purchases less Partial Payment of 4th Qtr PSP Purchases () Accounts Payable as of December 31, 2023 Check Amount + $31,584 Calculation of Dec. 31, 2023 Raw Materials Inventory (FIFO) for the Cost of Goods Sold Budget and the Balance Sheet 4th Quarter Ounces of PSP in Ending Raw Materials Inventory times 4th Quarter Cost of PsP per Ounce Raw Materials Inventory as of December 31, 2023 Check Amount + $31,200 Multiple Choice Questions . Using the data in the simple example on Pg. 1 of the Raw Materials Budget.pdf document, what would Answer: be Total Cost of Raw Material Purchases if Pounds of Raw Material Needed in Ending Raw Materials Inventory were 30 rather than 25? A. $635 B. $625 C. $645 D. $615 2. Which one of the following statements is correct? Answer: A. Total Cost of PsP Purchases on the Raw Materials Budget will be an input to the Forecasted Income Statement. 57 B. Total Cost of PsP Purchases on the Raw Materials Budget will be an input to the Forecasted Balance Sheet. 58 C. Total Cost of PSP Purchases on the Raw Materials Budget will be an input to the Cost of Goods Sold Budget. 59 D. None of the above statements are correct.A B C D E F Forecasted Balance Sheet for the Profit Planning Year Ending December 31, 2023 Test My Budget MyArmor, Inc. IMPORTANT Before "Wiring-Up" the Balance Sheet, complete the PP&E, Accumulated Depreciation, and Retained Earnings schedules DO YOU A W N located below the Balance Sheet as of December 31, 2023 Assets Dollars Current Assets Cash (from Cash Budget) 0.00% Accounts Receivable (from Sales Budget) 0.00% Raw Materials Inventory (FIFO) (from Raw Materials Budget) 0.00% Finished Goods Inventory (FIFO) (from Finished Goods Budget) 0.00% Total Current Assets 0.00% Property, Plant, and Equipment, gross (from schedule below) 0.00% less Accumulated Depreciation () (from schedule below) 0.00% Property, Plant, and Equipment, net 0.00% 18 Total Assets 0.00% 19 Check Amount + $686,273 20 Liabilities and Stockholders' Equity Current Liabilities Accounts Payable (from Raw Materials Budget) 0.00% Bank Loan (from Cash Budget) 0.00% Total Current Liabilities 0.00% Stockholders' Equity 26 Common Stock 0.00% IN Retained Earnings (from schedule below) 0.00% 28 Total Stockholders' Equity 0.00% 29 Total Liabilities and Stockholders' Equity 0.00% 30 Check Amount + $686,273 31 Fix the Format of All White Cells 32 Amounts (not calculated on other Budgets) Needed for the Balance Sheet 34 35 Property, Plant, and Equipment, gross 36 Property, Plant, and Equipment, gross as of Dec. 31, 2022 37 plus Equipment Purchases - Full Year Total (from Cash Budget) 38 Property, Plant, and Equipment, gross as of Dec. 31, 2023 39 Accumulated Depreciation Accumulated Depreciation as of Dec. 31, 2022 ( ) add Depreciation - Full Year Total (from Manufacturing Overhead Budget) ( ) add Depreciation - Full Year Total (from SG&A Budget) ( ) Accumulated Depreciation as of Dec. 31, 2023 ( ) Retained Earnings Retained Earnings as of Dec. 31, 2022 48 plus Net Income (Net Loss) (from Income Statement) 49 less Dividends - Full Year Total (from Cash Budget) ( ) 50 Retained Earnings as of Dec. 31, 2023 51A B C D E F Forecasted Income Statement for the Profit Planning Year Ending December 31, 2023 Test My Budget MyArmor, Inc. 2023 Dollars % V A 8 Sales (from Sales Budget) 0.00% 9 less Cost of Goods Sold (from Cost of Goods Sold Budget) ( ) 0.00% 10 Gross Profit 0.00% 11 less SG&A Cost (from SG&A Budget) ( ) 0.00% 12 Operating Income 0.00% 13 less Interest Expense (from Cash Budget) ( ) 0.00% 14 Net Income (Net Loss) 0.00% 15 Check Amount + $114.430 16 Fix the Format of All White Cells 17A B C D E F G H Direct Labor Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2023 Test My Budget MyArmor, Inc. Quarter in the 2023 Planning Year Full Year 1st end 3rd 4th Total Finished Units to Produce (from Production Budget) GUAWNSOOOYOUAWN times Direct Labor Hours per Finished Unit equals Total Direct Labor Hours Total Direct Labor Hours times Average Cost of a Direct Labor Hour Total Direct Labor $'s Check Amount + $35,280 Check Amount + $220,400 Fix the Format of All White Cells 17 18 Cash Payments for Direct Labor - for the Cash Budget 19 1st Qtr. 2nd Qtr. 3rd Qtr. 4th Qtr. 21 Total Cash Payments for Direct Labor * 22 * MyArmor pays all direct labor cost in the period incurred; thus Total Cash Payments = Total Direct Labor $'s 25 26 Multiple Choice Questions 29 1. Using the data in the simple example on Pg. 1 of the Direct Labor Budget.pdf document, Answer: 30 what would be Total Direct Labor $'s if Finished Units to Produce were 80 rather than 100? 31 A. $400 32 B. $500 33 C. $600 34 D. $700 36 2. Which one of the following statements is correct? Answer: 37 A. The Direct Labor Budget is more difficult than the Raw Materials Budget. 38 B. Total Direct Labor Hours is an input to the Cost of Goods Sold Budget. 39 C. Total Direct Labor $'s is an input to the Manufacturing Overhead Budget. 40 D. None of the above statements are correctStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started