EXCEL ASSIGNMENT
REQUIRED ELEMENTS:
To receive credit for this assignment you must complete the following elements:
• DATA BLOCK PAGEincluded 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.
REQUIREMENTS:
• 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 Excelby cell referencing the data fromthe data block page.
o Schedules Format: Use the examplesin Exhibits 2-11 and 2-12on pages87 - 89 for preparing the Schedules of Cost of Goods Manufactured, Cost of Goods Sold and the Income Statement.
• Graph the Selling and AdministrativeExpenses from theIncome Statement using theoriginal data first then later when you do the What If. The graph should automatically change for the new what if data.
o 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).
o Set the decimal to zero.
o DO NOT USE A LEGEND for this assignment.
o 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.
Litewave Media Problem and Data
Litewave Media, located in the Tampa/St. Petersburg Florida area was named the number one video production firm by 10 Best Production in July 2016. It is known throughout the industry as a premier full service video production company with over 10 years of experience in serving the film and video community. Litewave Media has a proven track record in production and marketing, from reality television shows to feature films to music videos. Though Litwave Media is based in Florida, the company has experience in film production throughout the world.
Because the videos differ in length and in complexity of production, Litewave Media uses a job order costing system to determine the cost of each video produced. Studio (manufacturing) overhead is charged to videos on the basis of camera-hours of activity. The company's predetermined overhead rate for the year is based on a cost formula that estimated $280,000 in manufacturing overhead for an estimated allocation base of 7,000 camera-hours. At the beginning of the year, the inventory balances were as follows:
Raw materials inventory (films, costumes) $30,000
Work in process inventory (videos in process) 45,000
Finished goods inventory (finished videos awaiting sale) 81,000
The following transactions were recorded for the year (2015):
a. Film, costumes, and similar raw materials purchased on account, $185,000.
b. Film, costumes, and other raw materials issued to production, $200,000 (85% of this material was considered direct to the videos in production, and the other 15% was considered indirect).
c. Costs for salaries and wages were incurred as follows:
a. Direct labor (actors and directors) $ 82,000
b. Indirect labor (carpenters to build sets,
costume designers, etc.) 110,000
c. Administrative salaries 95,000
d. Utility costs incurred in the production studio, $72,000
e. Depreciation recorded on the studio, cameras, and other equipment, $84,000. Three-fourths of this depreciation related to actual production of the videos, and the remainder related to equipment used in marketing and administration.
f. Advertising expense, $130,000.
g. Prepaid insurance expired during the year, $7,000 (80% related to production of videos, and 20% related to marketing and administrative activities).
h. Miscellaneous marketing and administrative expenses incurred, $8,600
i. Studio (manufacturing) overhead was applied to videos in production. The company recorded 7,250 camera-hours of activity during the year.
j. Videos that cost $550,000 to produce according to their job cost sheets were transferred to the finished videos warehouse to await sale and shipment.
k. Sales for the year totaled $925,000 and were all on account. The total cost to produce these videos according to their job cost sheets was $600,000.
l. Collections from customers during the year totaled $850,000.
m. Payments to suppliers on account during the year, $500,000; payments to employees for salaries and wages, $285,000.
REQUIRED:
1. Prepare a schedule of cost of goods manufactured.
2. Prepare a schedule of cost of goods sold.
3. Prepare an income statement for the year.
Video Job 207 was one of the many jobs started and completed during the year. The job required $15,000 in direct materials, $6,000 in direct labor cost and used 145 camera hours. (Hint: You will need to calculate the MOH applied to the job.) If the company billed the job at 115% above the cost, what price would have been charged to the customer? Show all computations in your data block page.
The What If part of the assignment will also have an extra credit part worth 2 pts. See your Excel Instructions under What If.
Attachment:- Attachments.598