Looking at the attached template, note there is a tab entitled "Instructions."

Budget Project 1609AC202_D2 Variables Sales Production Template.xlsx

The information necessary for the Variables tab and the Sales-Production Budget tab is on the "Instructions" tab.

The yellow highlighted areas are to be filled in.

On the Sales-Production budget tab, the Sales Budget should be fairly easy to complete.In cell D9, for example, to determine the total sales number, insert the formula =D7*D8.This will perform the calculation for you.

To calculate the production budget numbers. understand the ending inventory for January must be 50% of the sales volume for February.If February sales volume was going to be 20,000 units, the January ending balance would need to be 10,000 units.Notice I say Units, and not dollars.

To determine how much to produce, one must calculate the numbers.Looking at January, how many units must be produced so that 5,000 can be sold, have an ending balance of 1/2 the next month's sales.This has to take into account the beginning balance in inventory.In this case, we see that 4,500 units must be produced to accomplish this.

This project is due when stated, and no late assignments will be accepted or graded.

Fifteen (15) minutes after the due date, the next template will be provided which will contain the answers for the first part.This will allow you to move forward with correct information, while not having to wait for your corrected homework.For this reason, once again, no late assignments will be accepted or graded.

Student: Master Instructions: Cells1.with yellow shading must be completed 2. All portions must be delivered through a file upload on Canvas. No exceptions will be allowed or graded. 3. Deliverables are due when listed in Canvas. No late submissions will be accepted, regardless of reason. 4. Solutions will be available after the due date, and no late submissions will be accepted 5. is to be done individually. Any cheating discovered will result in an immediate zero (0) All work for the entire project Project Instructions are as follows 1. in units, sales price and pay rates by department and individual are listed below Sales, Month Sales Sales Price (Units) per Unit January February March April May June July August September October November December 5,000 5,000 5,000 7,000 9,000 9,000 9,000 9,000 9,000 8,000 7,000 6,000 $ $ $ $ $ $ $ $ $ $ $ $ 2,000.00 2,100.00 2,200.00 2,300.00 2,500.00 3,000.00 3,000.00 3,000.00 2,900.00 2,500.00 2,200.00 2,100.00 2. Production Budget, Ending Inventory must be equal to 50% of the next month's sales volume. The sales manager believes sales will remain the same one year to the next. On the The 3. following employees work for the organization and make the amount per hour listed. Tim Chance Maciel Jim Donna Kally Bob Thomas Joshua Sam Maletha Bill Shareka Alhagie William 9.85 11.18 11.62 12.50 12.63 14.55 15.00 16.00 16.50 17.20 17.48 18.00 18.00 18.24 22.55 The 4. advertising rate is 15% of Sales The 5. Sales Commission rate is $15,000 plus $350 for each unit sold The 6. travel and entertainment rate is 5% of Sales The 7. Income Tax rate is 40% 8. Admistrative Expenses are as follows Monthly Officers Salaries Office Support Salaries Rent Expense Office Supplies Utilities $ $ $ $ $ 100,000.00 60,000.00 16,000.00 8,000.00 13,500.00 55%9.of sales are collected in the current month, 40% are collected in the month after the sale, and 5% are uncollectible 10, Disbursements are made as follows: 11 Disbursement Current Next Direct Materials Direct Labor FOH SGA 40% 95% 80% 55% 60% 5% 20% 45% The beginning cash balance is $15,000 0 Sales (Units) Month Sales Price January February March April May June July August September October November December Employee Shareka Joshua Kally Alhagie Chance Bob Bill Jim Sam Tim Thomas William Maciel Maletha Donna Rate Sales B 14 January Units Price per unit Total Sales $ $ February 5,000 2,000 10,000,000 March April 5,000 Production Budget (Ending Inventory mu January February Beginning Balance Sold Ending Inventory 3,000 5,000 2,500 Produced 4,500 March April Sales Budget May June July August September October September October duction Budget (Ending Inventory must equal 50% of the next month's sales) May June July August November December November December Engine Components Steel Aluminum rubber wire spark plugs Direct Materials (Engine) Per Unit Price 25 Total 2.00 1.25 7.00 5 30 2 Components Steel 18.75 Aluminum rubber 5.70 wire Fiberglass Paint 4.00 Total Bob Bill Jim Sam Tim Hours per unit Cost 1.00 1.10 0.80 1.25 2.00 Total Employee Shareka Joshua Kally Alhagie Chance FOH per Unit 14 155 36 0.5 Direct Materials (Assembly) Per Unit Price Trailer 7.50 Safety Vest Decals 29.45 Wax 108.00 1 1 15 0.25 362.98 21.56 1.00 18.00 ### 21.56 4.50 Total Hours per unit Cost 1.60 1.80 2.30 3.40 2.80 Direct Labor (Assembly) Total Employee Hours per unit Thomas William Maciel Maletha Donna Cost Total 0.50 0.40 0.75 1.60 0.25 3.50 Factory Overhead (FOH) (Body) Factory Overhead (FOH) (Assembly) 2,650,000.00 Prior Year Dollars 3,015,500.00 Prior Year Dollars 137,500.00 Prior Year Units 137,500.00 Prior Year Units FOH per Unit Total 27.00 11.90 Factory Overhead (FOH) (Engine) Prior Year Units 2.00 1.25 7.00 Components Direct Labor (Body) 6.15 Prior Year Dollars 3 Total Total Direct Labor (Engine) Employee Direct Materials (Body) Per Unit Price FOH per Unit 2,984,500.00 137,500.00 Direct Materials January February March April May June July August September October November Production Level Direct Materials Direct Labor Factory Overhead (FOH) Totals Total Cost Per Unit December FOH Budget January February 882.13 Sales (Units) Sales (Dollars) Selling Expenses (Variable) Advertising Expense Sales Salaries Expense Travel and Entertainment Total Selling Expenses Administrative Expenses (Fixed) Officers Salaries Office Support Salaries Rent Expense Office Supplies Utilities Total Administrative Expenses Total SGA - March ### April 882.13 May 882.13 June ### July 882.13 August 882.13 882.13 September October November December 882.13 882.13 882.13 882.13 Budgeted Income Statement January February 882.13 Sales Cost of Goods Sold Gross Profit Selling and Administrative Expenses Income From Operations Income Tax (40%) Net Income IS Sales 14.00 March ### April 882.13 May 882.13 June 882.13 July 882.13 August 882.13 882.13 September October November December 882.13 882.13 882.13 882.13 Cash Budget January February Beginning Cash Balance Add: Receipts Collections from Customers Interest Income Total Receipts Total Available Cash Less: Disbursements Direct Materials Direct Labor Manufacturing Overhead SGA Expenses Income Taxes Total Disbursements Excess (Dificiency) of Cash Minimum Required (Ending Balance) Amount to be (borrowed) saved Financing (Savings) Savings Beginning Balance Add: Interest Savings Including Interest Plus: Excess of Cash Savings Ending Balance Financing (Borrowings) Borrowings Beginning Balance Add: Borrowings Add: Interest Borrowings Including Interest Less: Repayments including Interest Total Borrowings Ending Cash Balance - March April May June July August September October November December