cluld you include the formulas in the excel please.
The Coce Jamely Company (CuC) owns the global rights to sell a very fragile ghass ornament called "lack Wilshere's Tears": The cost of goods sold for the ornaments are shown on the secend page. CiC sets its selling prices so that each unit sold returns a sixty percent gross margie. Data regarding prodect costs, and administration and selling expenses for the past twehe months have been accumulated in order develop a budget estimate for an upcoming month. Coco lamela's Marketing Department has reviewed this data and could not reach a conclusion as to what is the most appropriate independent variable for predicting variable and mixed costs. These costs may be dependent on labor hours, unit sales, or distance shipped - Marketing gust doent know since they never had to take this accounting course. it is up to you to figure this out. Mequted: Note- Please have the data from the second page of this assignment and your work for Acquiremenb A-C an a single worksiert. A. Get the information from the second page into your Excel workbeok. Analyze the data to determine the behavior of each cost. The variable and mixed costs may be dependent on lahor hours, unit sales, or distance shipped. Use Excel's isQ finction to determine what the cost driver is for variable component of these costs that are variable or mixed. So, you will have to run the RSQ function three times for each of the costs. B. After the cost driver is determined for each of the mixed or variable costs you will need to determine the cost elements using the Excel SL.OPE and INTEHCEFT functions using that cost driver. c. The Coco lampla Company wants to estimate profitability for the apcoming month. The follosing estimates are to be used: unit sales: 73,500; total labor hours worked: 93,000 hours; distance shipped:322,000 miles. Firnt, prepare a Contribution Format income Statement for the upcoming month. Next construct a Traditional Income Statement for the upcoming month. Make certain to present cach cost/expense item separately on its owa line - do not aggregate the caste. Aegarding the selling price; this should first be calculated on a per unit basis, then use the ROUND function to limit this amount to a whole dellar amount. D. For this portion of the assignment you must first duplicate the worksheet for Requiremest C. Name this new tab "Requirement D: Your goal in this part of the assignment is to determine the Break-Even. Point for the company and then prove it with a contribution format income statement. Delete the Traditional Income Statement from this tab's worksheet - you will only need the Contribution Format version for Requirement D. Next, for those mined or variable costs that did not have Units Sold as their cost driver recalculate as if they did use Units as the independent Variable (cost driver) and determine the fixed and variable cost components. The next step is to determine the CM and then calculate the Break Even Point in units. Maloe sure that this cell is labeled and obvious. Finally, update the Contribution Format Income Statement in this worksheet ising yoar BEP units as the variable cost driver If done correctly the Net Operating Income should be zero. The Coco lamela Concpany (cuC) owns the dobal rights to sell a very fragile glass omament alled "lack Wibhere's Tears" The cost of goods sold for the ornaments ane showa on the monthi tame been actumulated in onder derelop a budget esuimate lor an upcoming mopth. Cece landis Marketing Department kas reviewed this dasa ind could cot reach a concluation. as to what is the mest appoppriate isdependent variable for predictine variable and mited cents. These costs moqy be depensent oe labor beurs, enit sales, ar diatance shipped - Marketing ket dobst know eince they brever had to take this accoanting course it is up to yoa to figure thit out. Rendinis: A. Cet the infarmacion frea die second page into your ficel workbok. Asalye the data to determine the behambo of nath cout. The variable and mixed costs moy te drpendent an abbor hears, unit sales, or dietance thipped. Use Lace's RSQ fanction to determise what the cot driver is for variable compoeent of these costs that are variable of mixed. So, you will have in rus the k keg functios three times far each of the cats. osst dricer. c. The Cokolantwif Comany wants to eitiemate profitubility for the vpooming moe th. The following eitimater are to be usndt unit sales 73.500, total labor hoars worked: 93,000 hours; distunce shipped: 322,000 miles. Fint. piepare a Contritration format income Statement for the vpcoming month. Kiest, eonatrut a Traditional Iscome Stateinent for the upcoming month. Make certain to greseot eacti cost/expense item separately an its ewn line - do not apgrerate the coste. Roparding the seling price; thie ahould firat be calculated eo a per unit basis, then uie the ROUNO function to limit thit amount to a whole dollar amoent. D. For this portion of the asigigment yoa must firat duplicate the workaheet for Reguirement C. Name this new tab " hequirement o' four goal in this part of the assignment is te determibe the Break. Even-Poiet for the company and then prove it wich a contribution format income statement. Delete the Traditional locome Statement from this tab's worksheet - you will oely need the Contribution Format version for Requirement D. Next, for thote mixad or variatle costa that eid act have Units Sold as their cont driver recalculate as if they did use Units as the lndependent Variable (cost driver) and determine the fixed and variable eost componentk. Ihe neit step is to determine the CM and then calculate the Break Even Point in units. Make sure that this cell is labeled and obvious. Pinally update the Contribution Format lncome Statement in this wofksheet uaing your bey units as the variable cost drwec if done correctly the Net Operating income should be zero
