Individual Case Study 2 Budgeting After two years study at UCW, you finally graduate and start a job as Junior accountant at Godox Inc. Your manager is responsible for the nationwide distribution of...

1 answer below »
These questions need to be answered in excel formatRequired: Prepare a master quarterly budget for 2021. Include the following budget schedules and financial statements: 1) Master Budget 2) Cash Budget. Show the cash budget by quarter. 3) Budgeted Income statement by quarter and in total


Individual Case Study 2 Budgeting After two years study at UCW, you finally graduate and start a job as Junior accountant at Godox Inc. Your manager is responsible for the nationwide distribution of creator design sets. Because of the new social media influence among current generation, the company has grown rapidly, and the prompt growth forces the management team to improve their efficiency and manage their production effectively. You have just been given responsibility for all planning and budgeting of the entire lighting set division. Today is your first day, you have just given an assignment to prepare master budget for the manager, who needs to present the budget and discuss the financial objectives with the shareholders tomorrow. During your job interview, you clearly stated that you gained managerial accounting knowledge and hand on experience during your MBA study. Your first assignment is to prepare a master budget for the next fiscal year, starting January 1, 2021. Your co-work has left a pile of files on your desk including the past sale records, product information, manufacture schedule and supplier pricing list. Now, you realized that you should have pay more attention during the lecture. Now, you don’t know where to start. Fortunately, you remember that you still kept a copy spreadsheet of the master budget template in your laptop from the accounting course. Note: The company desires a minimum ending cash balance each quarter on $2,000,000. The Godox products are sold to retailers for $125 each and the sales have been in demand due to the Covid-19. However, the marketing department has been conservative toward the end of the year due market saturation. The marketing department has just sent you their forecasted quarter sales and marketing budget. Quarter 2021 Q1 2021 Q2 2021 Q3 2021 Q4 2022 Q1 2022 Q2 Sales in Unit 30,000 35,000 40,000 55000 50000 55,000 Marketing Expenses $35,000 $20,000 $20,000 $35,000 $35,000 $25,000 Ending finished goods inventories are supposed to be equal to 20% of the next quarter’s sales in units. Godox currently does its own assembly production in house. Each unit consists of 2 LED panels and the cost of each is $15. Each unit needs 0.50 labour hour from assemble to finish package. The hourly pay rate to the assembling workers is $15 per hour. The production manager also required desired direct material ending inventory to 30% of the next quarter production. Purchases are paid for in the following manner: 50% in the quarter of the purchase and the remaining 50% paid in the quarter following the purchase. All sales to the distributors are made on credit terms with no discount (for now), and payable within 45 days. The Godox has determined that only 50% of sales are collected by the end of the quarter in which the sale occurred. An additional 50% is collected in the quarter following the sale. Bad debts have been negligible, supporting the credit terms as favorable. Below is a display of the Godox division quarterly manufacture overhead and selling and administrative expenses: Manufacture Overhead Indirect Labour $ 2.50 per labour hour Indirect Materials $ 0.75 per labour hour Wages and Salaries $ 6,000.00 Utilities $ 6,000.00 Insurance $ 5,000.00 Depreciation $ 5,000.00 Selling and Administrative Variable: Sales Commissions $ 1.00 Freight $ 0.50 Fixed: Wages and Salaries $62,000 Utilities $18,000 Insurance $3,200 Depreciation $5,500 Miscellaneous $8,000 Labour, Manufacture Overhead, and Selling and Administrative expenses are all paid during the quarter, in cash, with the exception of depreciation (of course). Godox will make a purchase of a parcel of land during the second quarter of 2021 for $3,500,000 cash. Godox’s balance sheet at the end of the fourth quarter last year is shown below: Assets Cash $35,000 Uncollected Accounts receivable in Q4 this year: $250,000 Liabilities Accounts payable $85,000 An agreement with Bank of the West allows Godox to borrow up to a total loan amount of $3,500,000. The interest rate on these loans is 12% annually (pretty high considering market rates) but the interest is not compounded, meaning this is simple interest only. Required: Prepare a master quarterly budget for 2021. Include the following budget schedules and financial statements: 1) Master Budget 2) Cash Budget. Show the cash budget by quarter. 3) Budgeted Income statement by quarter and in total Activity/Competencies Demonstrated % of Final Grade Budgetary Planning (100%) a. Sales Budget 5 b. Production Budget 10 c. Direct Materials Budget 10 d. Direct Labour 5 e. MOH 10 f. S&GA 10 g. Cost of Goods Sold 5 h. Budgeted Income Statement 10 i. Schedule of Collection 10 l. Schedule of Purchase 10 m. Cash Budget 15 Total 100
Answered Same DayMar 30, 2022

Answer To: Individual Case Study 2 Budgeting After two years study at UCW, you finally graduate and start a job...

Sandeep answered on Mar 30 2022
100 Votes
Sheet1
    Quarter Wise Sales Budget
            Quarter    2021 Q1    2021 Q2    2021 Q3    2021 Q4
        Description
        Unit Sales        30000    35000    40000    55000
        Unit Price ($)        $125    $125    $125    $125
        Total Sales         $3,750,000    $4,375,000    $
5,000,000    $6,875,000
    Quarter Wise Production Budget
            Quarter    2021 Q1    2021 Q2    2021 Q3    2021 Q4    2022 Q1
        Description
        Sales in units         30000    35000    40000    55000    50000
    Add    Desired Closing Inventory
        (20% of next Qtr sales)        7000    8000    11000    10000
        Total Finished Goods Required         37000    43000    51000    65000
    Less     Opening Finished Goods Inventory         6000    7000    8000    11000
        Required Production budget         31000    36000    43000    54000
    Quarter Wise Raw Material Budget
            Quarter    2021 Q1    2021 Q2    2021 Q3    2021 Q4    2022 Q1
        Description
        Required Production budget         31000    36000    43000    54000    50000
    Add    Desired Direct material ending inventory        10800    12900    16200    15000
        Total Raw material         41800    48900    59200    69000
    Less     Raw Material opening Inventory        0    10800    12900    16200
        Require Raw Materila to be pruchased         41800    38100    46300    52800
        Raw Material needed per unit        2    2    2    2
        Budgeted Raw Material to procure        83600    76200    92600    105600
    Quarter wise Raw Material Purchase cost Budget
            Quarter    2021 Q1    2021 Q2    2021 Q3    2021 Q4    2022 Q1
        Description
        Required Raw Material to purchase         83600    76200    92600    105600
        Cost per unit         $15    $15    $15    $15
        Direct Material Cost ($)        $1,254,000    $1,143,000    $1,389,000    $1,584,000
        Direct Labour Hour Required (No. of Labour Hrs)        15500    18000    21500    27000
        Direct Labour Cost per unit         $15    $15    $15    $15
        Total Direct Labour Cost         $232,500    $270,000    $322,500    $405,000
        Total Raw Material Purchase cost         $1,486,500    $1,413,000    $1,711,500    $1,989,000
    Quarter Wise Manufacturing cost Budget
            Quarter    2021 Q1    2021 Q2    2021 Q3    2021 Q4    2022 Q1
        Description
        Required Production(Units)        31000    36000    43000    54000
        Indirect Labour Hour @ 2.5 per labour Hr        $77,500    $90,000    $107,500    $135,000
        Indirect Material @ .75 per Labour Hr        $23,250    $27,000    $32,250    $40,500
                $100,750    $117,000    $139,750    $175,500
        Total Fixed Manufacturing overhead
        Wages and...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here