Explore BrainMass

Explore BrainMass

    Excel Problem dealing with management accounting

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    Problem 1 Send your completed problem via the dropbox in the student tools area.
    Due June 24. You will be graded on the accuracy of your answer and the usage of excel.
    Parts 2,3, 4, & 5 must use excel cell referencing and the chart wizard.
    The problem solution is worth a total of 10 pts. I will take off 5 pts. if you do not use the excel application
    appropriately. You need to use only cell references on the 2 statements you are developing.
    I have a demo exercise that demonstrates the development of the statements, which you can access by going
    to the course documents area Chapter 2. The requirements and problem
    below are a little bit different than my demo problem, but there are a lot of similarities. I have set up
    the data field for you, so just use this sheet as your reference. Also, I have already started the 2
    statements on separate sheets for you. Make sure you look at the comments I inserted.
    Toy Company produces dolls. For the year ended December 31, 2002, the company reported the following information.

    DATA FIELD:
    Direct materials inventory, January 1 $15,000
    Direct materials inventory, December 31 25,000
    Direct materials purchased 80,000
    Salary of the production supervisor 38,000
    Direct labor used in the factory 80,000
    Rent on the office building 24,000
    Property tax on the factory 10,000
    Maintenance on factory equipment 20,000
    Office supplies used by sales personnel 2,000
    Sales commissions paid 15,000
    Administrative wages 24,000
    Depreciation, factory equipment 4,000
    Utility cost, factory 7,000
    Work in process Inventory, January 1 8,000
    Work in process Inventory, December 31 12,000
    Finished Goods inventory, January 1 20,000 Units 110,000
    Finished Goods inventory, December 31 ???? Units ????

    Number of units produced 45,000 Units
    Number of units sold 58,000 Units
    Selling price per unit $8 per unit

    Required:

    Part 1: Using the information above fill in the chart below with the product and period costs being incurred in 2002. A cost
    item could affect multiple areas.
    Total each of the columns after you have assigned all the costs to the applicable column(s)
    I have entered the direct materials used to get you started.
    Product costs
    Direct Manufacturing Period Prime Conversion
    Cost item Direct Materials used Labor overhead Costs costs Costs
    Material used 70,000 70,000

    Totals

    Part 2: Use the data field I have set up above starting in cell A13 to produce the following statements. Do not type in numbers into your statements.
    a. Prepare a Statement of Cost of Goods Manufactured for the year ended 2002 like the one on page 45.
    b. Compute the unit product (manufacturing) cost to produce one doll.
    Remember to use the data field on this sheet to reference the information in preparing your Statement
    of Cost of Goods Manufactured and Income Statement. Use a separate sheet for each statement.
    I have started each of the statements for you. Look at the sheet tab COGM and IS.

    Part 3: Prepare an income statement like page 44, assuming the company sold 58,000 dolls for $8 each.
    Assume that the company uses a FIFO inventory flow assumption.
    The numbers on the statements should only appear because of cell references. If I change a number
    in the data field it should automatically change on the statements.
    Please note that you should not be typing in any numbers into the statements but using only cell
    referencing.

    Part 4: Prepare 2 pie charts.
    1. One chart should compare the total product costs to the total period costs.
    2. Another pie chart should compare three types product costs.
    Insert these charts on separate sheets and comment on your chart results.

    Part 5: The three situations below are independent of each other, so make sure you change your figures back to answer
    the questions.
    a. What if you feel you can increase the price of the doll to $9 without impacting the sales
    volume, which statement or statements will be affected and what will be the new net income?
    If you have set up your data field correctly and only cell referenced in the statements you will only need to change one number and
    the applicable statement(s) will change automatically.
    b. What if the temperatures drop to unreal lows, which increases
    utility costs in the factory to $9,000, which statement or statements will be affected and what will be the new net income?
    If you have set up your data field correctly you will only need to change one number and
    the applicable statement(s) will change automatically.
    c. What if the rent on the office building goes up to $28,000. Which statements will change?
    What will be the new net income?
    If you have set up your data field correctly you will only need to change one number and
    the applicable statement(s) will change automatically.

    © BrainMass Inc. brainmass.com June 3, 2020, 4:49 pm ad1c9bdddf
    https://brainmass.com/business/accounting/excel-problem-dealing-management-accounting-5155

    Attachments

    Solution Summary

    The expert examines an Excel problem for dealing with management accounting.

    $2.19

    ADVERTISEMENT