Here is the spreadsheet i did

answer these questions

1. How much did the materials cost per unit change between June and July?

2. How much did the conversion cost per unit change between June and July?

3. Which is the optimal choice of material and factory overhead costs in August? Why?

4. On a FIFO basis, what are the percent changes in the cost per completed unit from one group to the next over the three-month period? What may have caused these differences?

INTRODUCTION William Black is a creative, entrepreneurial individual who is constantly working on unique and innovative solutions to everyday problems and annoyances. He has developed several interesting products and even garnered a limited number of online sales. But none of his inventions has taken off-until now. William recently developed an electronic device, BallBoy, which locates golf balls above ground, in ground, and under water. The built-in technology is powerful enough to search for and detect balls within a radius of the length of a soccer field. BallBoy is a holster that holds smart phones of various sizes and models. It is activated once the short cable on the device is plugged into the phone. BallBoy gives walking directions to golf balls visually on the phone screen as well as via audio instructions. BallBoy is inexpensive to produce but, due to its unique design, is difficult to replicate. William is particularly pleased with BallBoy, realizing it is his best idea yet. He has a patent pending to protect the functionality and use of his invention. William and his parents made and packaged 1,500 units of BallBoy in their garage and offered the product for sale online at US$99 per unit. William posted photos, videos, and information about BallBoy on his ecommerce website; the response was overwhelming. Within a week, orders exceeded 3,600 units. He shipped the available units and advised the remaining customers of the backorder. Customers posted rave reviews for quality, value, and functionality. William began hiring employees and expanded into his basement and backyard to scale the size of his operations. Orders continued to roll in at a pace he never imagined, and the team was barely able to keep up with the growing demand. William had neither the capacity nor the capital to further expand his production. He was denied loans at several banks due to his inexperience and unproven financial track record, and his family and friends did not have money to spare. The good news is he was contacted to appear on Spark Bank, a television show where entrepreneurs present new business ideas and products to five self-made billionaires who invest in small businesses they find appealing. On the show, the Spark Bank investors/judges were impressed with BallBoy and its potential to generate significant profit. Mike Peruvian and Mr. Fantastic" showed genuine interest in William's business concept and offered a 10% equity stake in the business in exchange for funding to finance inventory and establish a production facility. Their one contingency was that after one year of operations, William must license the BallBoy technology to a major manufacturer, which they believe could use it for a different application. William was hesitant about sharing his trade secret, yet he agreed to the deal since he needed to money to proceed. Mike Peruvian wanted to be sure that William was well-versed in process costing as a cost allocation technique for identical items that are manufactured in a continuous sequence. He saw this as a test of William's ability to be a good product manager. He asked William to allocate anticipated production costs for both fully and partially completed units average 60%, the targeted percentage. The expected number of units and conversion rates are: 5,600 units 30% of the conversion completed June July August 7,100 units 8,900 units 90% of the conversion completed 60% of the conversion completed William also estimates the following expenditures for June: direct materials, US$716,555, direct labor costs, US$276,612; and factory overhead, US$149,520. In July, William projects factory overhead cost per equivalent unit will change, since some initial overhead costs in June pertained to the first month only. He also believes that the materials cost per unit will change due to political decisions regarding the North American Free Trade Agreement (NAFTA). In August, direct labor will likely increase per equivalent unit based on the contractual rate increase after the 60-day probationary period. William chooses the better of the two options regarding materials and overhead in August: for three consecutive months in the assembly department using the first in, first out (FIFO) method of process costing. Mike expected William to be able to determine the cost for each unit in each of three months, explain how one period's production data feeds into the next in a continuous flow, and analyze the progress of the business over time. Mr. Fantastic's perspective was "Show me the money!" He wanted William to investigate ways to reduce production costs over time to increase profitability. The first month's production costs would be a baseline, and William should locate less expensive suppliers, labor, and/or overhead items to reduce unit costs in the second and third months. This would involve decision points for William, since a change in one cost might impact another cost. William begins by thinking about the production process. He determines that it will be most efficient if he moves materials from the stockroom as infrequently as possible, particularly since there is sufficient space in the production area to stage all materials needed to fully assemble items as they enter the assembly department. He estimates that units will spend no more than three weeks in the assembly department and recognizes that conversion costs will be incurred throughout this period. The first month of operations in the new facility will be June, and William analyzes costs for June, July, and August in his pro-forma analyses. Based on sales projections in good-weather conditions, William estimates he will need to begin production on 41,300 new units in June, 54,200 units in July, and 71,100 units in August. He is aware that the National Weather Service has predicted an extremely active hurricane season, which may adversely impact August and September sales, perhaps indicating a decline in production in August up to 20%. But he decides to proceed with his original August estimate. William realizes that not all of the new units started in a month will be completed the same month. He approximates that 13% of the units started each month will remain in work in process at the end of that month. His target is for 60% of the conversion process to be completed on the unfinished units at the end of each month. He anticipates, however, that in June the learning curve and start-up glitches may make that goal unattainable. Therefore, he conservatively estimates only a 30% completion rate instead. To make up for June's slower-than-average start and bring production back on track in July, he plans to offer employees compensation time incentives to work additional hours to achieve a 90% conversion rate. Over the course of the first two months, the conversion completed will ultimately Direct Materials Direct Labor Factory Overhead a. $1,239,984 $518,700 $266,175 b. $1,235,007 $518,700 $273,000 Note: All monetary values are represented as US dollars. William develops a spreadsheet to analyze cost data for the three months. For a sample, see Figure 1. ASSIGNMENT 1. a. How much did the materials cost per unit change between June and July? b. How much did the conversion cost per unit change between June and July? 2. Which is the optimal choice of material and factory overhead costs in August? Why? 3. On a FIFO basis, what are the percent changes in the cost per completed unit from one group to the next over the three-month period? What may have caused these differences? 4. How should William respond to Mr. Fantastic's call to "Show me the money!" in terms of options to reduce production costs over time to increase profitability? 5. If the estimate for projected production in August is adjusted down by 20% due to inclement weather, yet total labor and factory overhead costs remain at the original amounts, what is the impact on cost per equivalent unit? Process Costing - Assembly Department - JUNE Information regarding inventory production for the Assembly Department for June is as follows: Direct Materials Costs in June for 41,300 Units $ 716,555 Direct Labor Costs incurred in June 276,612 Factory Overhead costs incurred in June 149,520 Work completed in June: 35,700 units Ending Work in Progress, June 30:5,600 units, 30% completed Equivalent Units of Production for Materials Equivalent Units of Production for Conversion Costs Category Whole Units Started in May: Completed in June Started in June: Completed in June 35,700 35,700 35,700 Started in June: Completed in July 5,600 5,600 1,680 TOTAL 41,300 41,300 37,380 a.) Materials cost per equivalent unit $ 17.35 b.) Conversion cost per equivalent unit $ 11.40 c.) Cost per unit of finished goods started in May: completed in June d.) Total cost of all finished goods started in May: completed in June e.) Cost per unit of finished goods started and completed in June f.) Total cost of all finished goods started and completed in June g.) Cost per unit of the work in progress inventory on June 30 $ 28.75 $ 1,026,375.00 $ 20.77 h.) Total cost of the work in process inventory on June 30 $ 116,312.00 Process Costing - Assembly Department - JULY Information regarding inventory production for the Assembly Department for July is as follows: $ 944,164 424,834 Direct Materials Costs in July for 54,200 Units Direct Labor Costs incurred in July Factory Overhead costs incurred in July Work completed in July: 52,700 units 223,899 Ending Work in Progress, July 31:7,100 units, 90% completed Equivalent Units of Production for Materials Equivalent Units of Production for Conversion Costs Category Whole Units Started in June: Completed in July 5,600 3,920 Started in July: Completed in July 47,100 47,100 47,100 Started in July: Completed in August 7,100 7,100 6,390 TOTAL 59,800 54,200 57,410 a.) Materials cost per equivalent unit $ 17.42 b.) Conversion cost per equivalent unit $ 11.30 $ 28.72 $ 160,608.00 c.) Cost per unit of finished goods started in June: completed in July d.) Total cost of all finished goods started in June: completed in July e.) Cost per unit of finished goods started and completed in July f.) Total cost of all finished goods started and completed in July g.) Cost per unit of the work in progress inventory on July 31 $ 28.72 $ 1,352,712.00 $ 28.72 h.) Total cost of the work in process inventory on July 31 $ 195,889.00 Process Costing - Assembly Department - AUGUST Information regarding inventory production for the Assembly Department for Aug. is as follows: Direct Materials Costs in August for 71,100 Units $ 1,239,984.00 Direct Labor Costs incurred in August 518,700 266,175 Factory Overhead costs incurred in August Work completed in August: 69,300 units Ending Work in Progress, August 31:8,900 units, 60% completed Equivalent Equivalent Units Units of of Production for Production for Materials Conversion Category Whole Units Started in July: Completed in August 7,100 710 Started in August: Completed in August 62,200 62,200 62,200 Started in August: Completed in September 8,900 8,900 5,340 TOTAL 78,200 71,100 68,250 a.) Materials cost per equivalent unit $ 17.44 b.) Conversion cost per equivalent unit $ 11.50 $ 28.94 $ 204,054.00 c.) Cost per unit of finished goods started in July: completed in August d.) Total cost of all finished goods started in July: completed in August e.) Cost per unit of finished goods started and completed in August f.) Total cost of all finished goods started and completed August g.) Cost per unit of the work in progress inventory on August 31 $ 28.94 $ 1,800,068.00 $ 28.94 h.) Total cost of the work in process inventory on August 31 $ 216,626.00