EXCEL ASSIGNMENT #1
DUE DATE: Tuesday, 09/20/2016
CHECK FIGURES: MOH $9,400 overapplied
To receive credit for this assignment you must complete the following elements:
- DATA BLOCK PAGE included and the data cell referenced to your schedules and income statement.
- TWO Logic IF statements.
- Data Block Page Logic IF statement: Include a Logic IF statement on the data block page to determine under(over) applied overhead.
- Cost of Goods Sold Logic IF statement: Include a Logic IF statement in the Schedule of Cost of Goods Sold to determine whether to add or deduct overhead.
- See below for instructions on the Logic If statements.
- Prepare a Data Block page using the raw data in the problem posted to Bb.
- Prepare a Schedule of Cost of Goods Manufactured, Cost of Goods Sold Schedule and an Income Statement in Excel by cell referencing the data from the data block page.
- Schedules Format: Use the examples in Exhibits 2-11 and 2-12 on pages 87 – 89 for preparing the Schedules of Cost of Goods Manufactured, Cost of Goods Sold and the Income Statement.
- Graph the Selling and Administrative Expenses from the Income Statement using the original data first then later when you do the What If. The graph should automatically change for the new what if data.
- The graph should be a 3-D exploding pie chart with data labels (category names and dollar amounts should be included in your data labels).
- Set the decimal to zero.
- DO NOT USE A LEGEND for this assignment.
- The heading should include the company name, a title for the graph and the time period it covers. (See Graph Hints on the website.)
- Complete the “What If Analysis” described below. You will only be changing the data block page. The schedules and income statement will automatically recalculate once the new data is entered into the data block page.
REQUIRED ELEMENT: Data Block Page:
Set up a Data Block page. You will use the data block page as a “data entry” area and then “cell reference” this information to the Schedule of Cost of Goods Manufactured, Cost of Goods Sold Schedule and the Income Statement.
When you do the “What If” part of the assignment, you will need to save the original file under a new name and then only change the information listed in the Data Block (data entry area).
Below is a general format for the data block with the first two cost items in your problem filled in for you. However, you can construct the data block in any format you choose.
Split Items: For the items that are split between production (direct and indirect) and period, you will need to include the amounts for the breakdown between these classifications. For example, in this problem: Raw materials used is split between direct and indirect production according to the percentage related to each classification (illustrated above).
Inventory Ending Balances:
The beginning balances are given in the problem. Input these into your data block page.
You will need to calculate by formula and cell referencing the ending balances for RMI, WIP, and FG Inventories in your data block page. Don’t forget to include COGM and COGS somewhere in your data block page also. You will need these items to help you construct the WIP and FG formulas for the ending inventory balances.
For example the formula for ending RMI is: Beg. Bal. RMI + Purch. – Used. Use cell references from other parts of your data block page for the components of the formula so excel can calculate the End. Bal. RMI. That way when you change the data input amounts, your ending balances will automatically recalculate for the What If.
Manufacturing Overhead Calculations:
Since you have listed all of the indirect (MOH) costs in your data block column for Indirect costs, just sum up the total at the bottom of the table. This is your actual MOH costs incurred.
You will need to include in your data block page the estimated MOH and the estimated cost driver (allocation base) that are given in the problem so you can use this information to calculate the POHR. Next calculate the overhead applied (use cell references to construct a formula for this in your data block) and determine if overhead is under/over applied. Include in your data block page the formula for calculating under or overapplied overhead and your first Logic If statement. See the example below.
Total Actual MOH costs: $ ?*
Less Applied Overhead ___?*___
Use a Logic If Statement in this cell based on the cell to the right. $ ?*
*Use formulas in your data block to calculate these items.
+Instead of just typing in Over or Underapplied overhead, use a Logic IF statement here. See below for information on how to construct the Logic IF statement.
REQUIRED ELEMENT: Data Block Logic If Statement Instructions:
To properly label your under(over) applied overhead, use a “Logical IF statement.” Instead of typing in “Underapplied overhead” to the left of the calculated amount have excel determine the correct result (conclusion) by using a Logic IF statement instead.
An easy way to have Excel help you create this formula is to use the formula wizard. Click on the ‘fx’ button beside the editing toolbar. Use the arrow key to locate more formula options (sum should be the default). Choose IF. You will be presented with a box that asks for the necessary data. You can also modify the formula to include multiple results for multiple criteria (we may do one of these in a later excel assignment).
The general formula for a Logic IF statement is: = If (condition, true, false)
You must tell Excel what the condition (or test) is (Box 1 in the Wizard), what to do if that condition is true (Box 2 in the Wizard), and what to do if that condition is false (Box 3 in the Wizard). Here the condition (Box 1) is: if the result in the cell is = > than 0, then have excel type in “Underapplied overhead” (this is if the condition is true—Box 2), otherwise have excel type in “Overapplied overhead” (this is if the condition is false—Box 3).
Schedule of Cost of Goods Manufactured, Costs of Goods Sold & Income Statement:
Use Exhibits 2-11 and 2-12 for the general format of your Schedules of Cost of Goods Manufactured and Cost of Goods Sold and the Income Statement.
Use cell references from the data block to prepare your Schedules of Cost of Goods Manufactured and Cost of Goods Sold. Any formulas needed to complete the statement (for example: adding raw materials used plus direct labor plus total manufacturing overhead applied to arrive at total manufacturing costs) should be included in the Schedule of Cost of Goods Manufactured.
REQUIRED ELEMENT: Cost of Goods Sold Logic If Statement:
Your second Logic If statement will be in your Cost of Goods Sold schedule next to the under/(over) applied overhead cell amount. Let excel determine the result (conclusion).
Again, you will need to include the condition (or test) in box 1 of the wizard (For example: C25>=0); what excel should do if the test is true (box 2 of the wizard): “Add: Underapplied overhead”; and what excel should do if the test is false (box 3 of the wizard): “Deduct: Overapplied overhead”.
When preparing the Income Statement, use cell references from the Data Block page or the Schedule of Cost of Goods Manufactured. Again, any necessary formulas to complete this statement should be in the Income Statement.
Lastly, in the Income Statement, under “Selling and administrative expenses:” list out all of the selling and administrative expenses individually. You should have five items listed here. This will make it easier for you to graph these items, since you can just highlight them here when doing your graph and click F11. A graph will automatically be inserted into your workbook. If you are using this method, though, DON’T type across columns. Make each column wide enough to hold the data in it so information does not “bleed” across columns.
Add the heading below at the top of your page for the schedules:
Litewave Media, Inc.
For the Year Ended December 31, 2015
Don’t forget to include the subtitles for each schedule also. You can bold them and put them in italics as is shown in Exhibit 2-11.
Be sure your headings on the schedules and on the Income Statement do not extend beyond the last column of data so your report will be centered on the page in “print view”. Center the report horizontally on the page. See Excel Checklist on Bb for information on how to do this if you are not sure.
Income Statement Heading:
Add the heading below at the top of your income statement:
Litewave Media, Inc.
For the Year Ended December 31, 2015
Use the Exhibits on pg. 87-89 in the book for where and how to do your dollar signs, underlines, indentions, descriptive labels, column formatting, etc.
SAVING YOUR FILES:
Save the original file according to the following name format:
Original data file: (Your Last Name, First Name Initial) Excel#1.
For Example: SmithJExcel1.xls or SmithJExcel1.xlsx (depending on which version of Microsoft you are using).
Save the what if file according to the following name format:
Open the original file and save it under the new following name format:
What If data file: (Your last name, First name initial), Excel IF#1
For Example: SmithJExcelIF1.xls or SmithJExcelIF1.xlsx (depending on which version of Microsoft you are using).
WHAT IF ANALYSIS: Check Figure: Net Operating Income $108,100
The “What If” part of the assignment will help you determine whether you have correctly used cell referencing in your spreadsheets. (You are not using the “What If Analysis” in excel – you are using a duplicate original data file saved under a new file name.)
Demand has increased significantly since 10 Best Production released its list of the ten best video production firms in 2016. As a consequence the firm’s goal is to control the increase in costs in relationship to its revenues. Make the following changes to your data block page:
- Sales have increased to $1,000,000. The total cost to produce (manufacture) the videos according to their job costs sheets was $620,000.
- Raw material purchases for film, costumes, and similar items have increased to $225,000.
- Raw material used was $220,000 (85% of this material was considered direct to the videos in production, and the other 15% was considered indirect).
- Litewave Media, Inc. hired another part-time sales person to handle the increased demand and an additional “on-call” carpenter to help in building the sets. Direct labor has also increased due to an increase in the wage rate paid to the actors. Due to these changes, Direct labor increased to $90,000; Indirect labor increased to $135,000, and Administrative salaries increased to $105,000.
- Miscellaneous marketing costs increased to $8,800.
- The company signed a policy with a new insurance company. Prepaid insurance was reduced to $6,000 (85% was related to video production and the remainder was for the selling and administrative activities).
- Advertising has increased to $135,000.
- Total utility costs for video production has increased to $78,000.
- There was no change in depreciation from the previous year.
- Manufacturing overhead cost was estimated to be $315,000 for 2016 and total camera hours were estimated to be 7,500 for 2016.
- Actual camera hours were 7,450.
- Videos that cost $600,000 to produce according to their job cost sheets were transferred to the finished videos warehouse to await sale and shipment.
- You will need to change the beginning of year inventory balances. The amounts listed below are the carryover balances from 2015 (excel should automatically calculate the ending balances if you used formulas and cell references in the original data block page):
Raw materials $15,000 $ ?
Work in process 37,000 ?
Finished goods 31,000 ?
Your schedules and statements should automatically recalculate using the new data. You should not have to change any of the cell references or formulas for any of your schedules or statements. (If you do—then you have done the cell referencing incorrectly.) You are only changing the Data Block page.
EXTRA CREDIT (2.5 pts):
Job 307 was one of the many jobs started and completed during the year. The job required $20,000 in direct materials, $5,500 in direct labor cost, and used 160 camera hours. If the company billed the job at 115% above the cost, what price would have been charged to the customer? Show all calculations in your data block page.
Date for What If:
Change the date in the headings to be “For the Year Ending December 31, 2016”. (If you add the time period in your heading to your Data Block page and cell reference it to the statements, you will not have to change each statement heading. All you will need to do is change the time period in the Data Block page.) However, you may still need to change the date in the graph heading.)
SUBMISSION OF YOUR EXCEL ASSIGNMENT:
Put a footer on each page in the bottom right-hand corner which includes your name, section# and ZID#. Do not type the dots into your schedules or statements that are in the examples or homework solutions.
You will submit both files to Bb. The original file should contain the following items:
- Data Block page with the original problem data
- Schedules of Cost of Goods Manufactured, Cost of Goods Sold and Income Statement (using the original problem data).
- Graph (using the original problem data)
The what if file should contain the following items:
- Data block page with the “What If Analysis” data.
- Schedules of Cost of Goods Manufactured, Cost of Goods Sold and Income Statement (using “What If” data).
- Graph (using “What If” data)
Please be sure what you turn in is a unique product. You may work together, but you must each do your own spreadsheet. Do NOT turn in duplicate spreadsheets. We will assume you cheated and you both (or all) will get a zero for the assignment.
Save your work frequently! Do not be the next person telling horror stories about lost work! Back up your work on a disk! Do not fail to do this! There have been many students over the years that have had to redo the entire assignment because of a lack of a backup copy or failing to save their work frequently while they were working on it.