please show me the formula or how you get the answers

please go down and take it look...i have taken new photo that is clear

Accounting 365 Flex Budget Project Instruction BEFORE YOU NON HEAD CLOSELY Asert with the con le fond dood old medale Perife with your Women Empel be The first of the dedykoteThese robe beled who had found a reminded WWW NY FORMULA DRIVER Therefore of your prendre, IVERY war france were www.fr "WO pediate with the creation of your input Pan1 The Cross the who 1 cm. Alte som en reply peep, heber behalf on Dee Chron Stockwere boererar Currently. The Shower and en de 2014.39% 23% of , el All rente, per, and define prode, but are owned normal an. Stay Cheese Cookies Com C Ce! Quarter 1 Quartz Querer 0:29 5 036 1 0.22 $ 033 Sego 1 0.06 1 0.00 1 DOM $ 0.00 Sebanyo Taping 5 31 034 1 0.31 1 0.30 Cole & Co $ 0.18 1 0.19 . 0.21 1 0.22 0.12 1 0.15 0.12 1 0.14 They are wided by the hipew. The production proche de reprehendine how copco oferecidos products The other website www ste The closed to budget to the coming de The Creath has two bedre web page has the shore and reply for the budget, the there was to be 9.000 The coming The repor 18 to the red ye Cake Car 5 12.00 11160 10.50 2. Med derepending odberund rated by a rewig te vreed to use AVERAGE a tulee ping her 2. Herred Cost of Good report the cor prethod many due the procedemel More sted sabe Freure bude to the vine had to resurrectly in the fool will your widence that you are employees You can find hold on to Denme hat under the West Aly" Bere. After you have gre, you need yteroster or sale height yerindoping the typing den 001. you do of procurer on the budgeted wole, and there of your crack figures will not work Senty Sher the end of the year. The Cran Shack has compiled maculo and done for your and were comportere the world that were in a blandt andre to where to bushen edhe red and where ed imprenditran the years Income Sule 3164,000.00 Com of God 113,530.00 Grom Mergh 121,320.00 Seling $13,525.50 Awit pe 16.260.00 11750.50 Sales Doloros Sebeny Och 4,000 $4,000.00 Cases Cream 1.000 532,310.00 Core 2.400 525 100.00 Cates DI Q 16 or Carter Calper 16 Se Torel Me Car Carte Seberry Desich Topp Super 7.00 1 6501 2.50 2.0 0.15 Casa Malle Qey SC Car Cart To Male Cour troom Topping Super 7.00 1 2.30 1.005 3 18 3. 18 0.3) 4000 $ 13.00 GO Spor Carton 11.00 401 0.01 1.00 5 285 031 003 022 30 3,000 11.700.00 CH 0.01 2.01 2001 0 1 23 go 012 03 370 Cart 1,600 Tulle M. 0 0 Labas Heute her Com . 1. Mare Baterpetua wywod how do Morselyne view Yowed to deve implembe rewolwerp te bere you and whether anarhul are opere for yo'o de derde etapew", " low One piele, Valio, you with and we Yound by fortel Yel your caleconeixe'n yw it. The worden. Yndyrare Durk Green Darlar Meteo Cathedraler for the perpet. The rendre el resto AVIAO A you were he was Cong or you did pe cartoon Polows for high Tenester hat es geweest ACTUA 10 watt En 39200.00 Lab Men 37000 Lab Cew 351.000.00 1. The de Trattupurab tree of legere the periore o Tady vendengar Maker who were el directly in the call toward by the name timplemented the page you will need to note your mind wat by order typing in your desired to the page the relation the plus remony More than your thorpeate the day or need Owe yoted World, Variana, wieder des cliente en andeny.cc before you to mwing en toe in Pasal You will all of your comment below, Verion you reaches they will want to tell and the Tweede Werte higher leverer i Durre lorem feverede i Dantelor * MOM V Comercivarse por the moon par viegue y AVERAGE And you tendon se did has tobom Pulire the one whestur 10. Oddych you howed here are areAnser co hours Check your Tw 0400 twted ved Material tante para com og varme for Cust I Metal yarance for Dream OM 310000 1.3.160 inwe AutoSave ABES 3.Flex Budget Project - TEMPLATE Home Insert Draw Page Layout Formulas Data Review View Tell me Calibr (Body) 11 AA 29 Wrap Text Gener Paste BTU A Merge & Center C51 Vfx Budget Input Information 200 Canon Segre Norton ON Wh CA 10 Cent 11.00 11.00 10.50 Dista 11 Quantity carton beny Cheese Cook Coruncit to 1 C Town 18 Cestised Gusta Quarter Dara 1 C 2 2 Sty Chepong 26 Cookies Camping trutto 74 Cestito 1 Guntar Gun 11 Dr 12 11 her mus 1. Mary Solo 41 Period Cou 11 Sep 14 A alat me No Budget Input Actual input Standard Costa Budgeted Cost of Goods Sold Budgeted is Ready D Actual Input Information Carton Sales Dollar Sales 3 Sales 4 5 Strawberry Cheesecake 6 Cookies & Cream 7 Caramel Nut 3 9 Materials Quantity per 16 Cost per 16 o Carton Carton Total Materials Cost Cartons 10 11 Strawberry Cheesecake 12 Cream Toppings Sugar Carton 13 14 15 26 7 Cookies & Cream 18 19 20 1 -2 3. Caramel Nut 4 5 6 7 Cream Toppings Sugar Carton Cream Toppings Sugar Carton 9 Total Materials Cost 3 Labor 2 3 Hours Labor Cost Income Statement Sales COGS Gross Margin Selling Expenses Administrative Expenses Net Income Budget Input Actual Input Standard Costs Budgeted Cost of Goods Solc Read A B D 1 Standard Costs 3 Direct Materials Strawberry Cheesecake 4 Cookies & Cream Caramel Nut Cost per Cost per Carton Cost per Carton 5 Cream 6 Sugar 7 Strawberry Cheesecake Topping 16oz 8 Cookies & Cream Topping Carton 9 Caramel Nut Topping 10 Carton 11 Total Direct Material Cost per Carton 12 13 14 Direct Labor 15 16 Mixing the ingredients Labor 17 Activities Filling the Cartons 18 Labeling the Cartons 19 Total Direct Labor Cost per Carton 20 21 22 Manufacturing Overhead 23 24 Direct Labor Cost 25 Overhead Application Rate 26 Total Overhead Cost per Carton 27 28 29 30 31 32 33 34 35 36 37 38 39 10 11 42 Budget Input Actual Input Standard Costs Budgeted Cost of Goods Sold 1 Budgeted Cost of Goods Sold Statement Cost per Carton Number of Cartons Total Cost 3 4 Direct Materials 5 Strawberry Cheesecake 6 Cookies & Cream 7 Caramel Nut 8 Total Direct Materials 9 Direct Labor 10 Manufacturing Overhead 11 Cost of Goods Sold 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 Budget Input Actual Input Standard Costs Budgeted Cost of Goods Sold Bud Ready Accounting 365 Flex Budget Project Instructions BEFORE YOU BEGIN, READ CLOSELY.... A Start with the template that can also be found in the Blackboard folder titled "Flex Budget Project". Sove this file with your Nome in the title Example) Excel Project Joe Smith B. The first two tabs of the template (indicated with green highlighting) should serve as your input tabs. These tabs will be filled by typing in the input data found in this directions document. Each cell shaded with light grey needs to be filled by you. Other than the first two fobs, the rest of your spreadsheet should be ENTIRELY FORMULA DRIVEN. This means that on the following seven tabs of your spreadsheet, EVERY light grey cell should either reference another cell from the file or should be calculated using a formula which also references the necessary cells. *NO numbers or text can be typed into cells with the exception of your input tebal Part 1 The Cream Shack is a local business that produces homemade ice cream that it sells to its customers in a 16 oz carton. Although the business has been growing rapidly in the past few years, much of the production process is done in the basement of the owner's house. This helps the company avoid paying expensive rent for a building in an effort to keep costs low. The owners of The Cream Shack are attempting to create a budget for the upcoming year to ensure profitability. Currently, The Cream Shock produces three different flavors of ice cream strawberry cheesecake, cookles and cream, and caramel nut that sell at a ratio of 40%, 35%, and 25% of total sales, respectively. All three flavors require cream, sugar, and toppings to produce the final product, but she ratios of how much material is needed differ across the flavors. The table below summarizes the necessary materials to create each product Quantity (oz) Strawberry Cheesecake Cookies & Cream 10 5 Caramel Nut 11 Cream Toppings Sugar 6 2 Prices for these ingredients change throughout the course of the year in accordance to the market. For budget moking purposes ownership has decided to use an average of prior year costs. The following table summarizes the costs for the different Ingredients per ounce across the different quarters of the previous year Cost ($/03) Quarter 1 Quarter 2 Quarter 3 Quarter 4 Cream 5 0.29 $ 0.26 $ 0.22 $ 0.23 Sugar $ 0.06 $ 0.07 $ 0.08 $ 0.07 Strawberry Cheesecake Topping $ 0.37 $ 0.34 $ 0.31 $ 0.30 Cookies & Cream Topping $ 0.18 $ 0.19 $ 0.21 $ 0.22 Caramel Nut Topping $ 0.12 $ 0.15 $ 0.19 $ 0.14 The only labor necessary for production is provided by the owner who makes $18 per hour. The production process involves three steps that must be done manually. The first step is to mix all of the ingredients together using a machine which typically takes 6 minutes to complete. The second step consults of removing the mixed product from the machine and emptying it into a plastic carton. This process tokes about 3 minutes and each carton costs 25 cents. The final step of the labor process involves applying a label to the carton to indicate which flavor it. The process also tokes 3 minutes to complete. The company also has various overhead, selling, and administrative costs. To budget for these costs, the company estimates them using different cost drivers. The Cream Shack hos determined that overhead should be applied based on 20% of direct labor cost. Selling and administrative costs are best estimated using a percentage of total revenue. Historical analysis has shown that these expenses run about 12% and 8% of total revenue, respectively, For the purposes of the budget, The Cream Shack is predicting unit soles to be 9,000 cartons for the upcoming year. The selling price per 16 oz carton for each of the different product lines is shown below: Strawberry Cheesecake Cookies & Creme Caramel Nut Selling Price $ 12.00 $ 11.00 $ 10.50 1. Enter this information into the first tab, "Budget Input". Also, place your name in the box provided in the doht. band side of the tab. 2. Move to the Standard Cosh" tab. Develop the standard costs for the ice cream regarding direct materials, direct labor and overhead by using your budget input. For certain calculations, you will need to use the AVERAGE function. Remember there is a function (fx) tool located at the top of your Excel spreadsheet If you need some guidance with this. Also, remember that you can drag formulas to fill similar cells. This may require Mocking certain cells references within the formula being used. You can lock cells in the formulo by highlighting them and pressing the "44"key. 3. Move to the Budgeted Coat Goods Sold" tab. Prepare a budgeted cost of goods sold summary for the projected ice cream sales 4. Move to the "Budgeted IS" tab. Prepare a budgeted income statement for the upcoming year. 5. Move to the "Gool Seek" tob. Ownership is looking at purchasing a new building to Increase its production capabilities. Use the goal seek tool to find the number of ice cream carton sales necessary to create operating Income of $10,000. Goal seakuses on iterative process to make one cell be a particular value by changing another call's value, and for purposes of this project we want operating income to become 10,000 by changing the number of carton saleFor this to work, your group's spreadsheet must be driven by the Inputs tab and set up to flow correctly. Using this tool will give you strong evidence that you are on the right trock in the completion of your spreadsheet. You can find goal seek on the "Data" menu In Excel. It is under the "What-If Analysis" feature. After you have run goal seek, you need to copy the number of carton sales showing in the carton soles cell within the "Budget Inputs" tob Into the call on the cell within the "Goal Seek" tab by elther copying and pasting or just typing the number. This is an exception to the no typing rule. You then need to be sure to go back and change the carton sales value on the "Budget Inputs" tab back to 9,000. If you don't do this the rest of your spreadsheet will be running off of the goal seek number rather than the budgeted value, and the rest of your check figures will not work. Part 2 Shortly after the end of the year, The Cream Shock has compiled its actual sales and expense data for the prior year and wants to compare it to the budget. The owner was very excited about an increase in sales but could not understand why profin decreased during the year. He wants to see what areas of his business did better than expected and what areos need improvement. Actual data from the year la presented belowi Income Statement Soles Cost of Goods Sold Grou Margin Selling Expenses Administrative Expenses Net Income $104,050.00 $82,530.00 $21,520.00 $13,526.50 $6,243.00 $1.750.50 Sales Strawberry Cheesecake Cookies & Cream Caramel Nyt Unit Sales 4,000 3,000 2.400 Dollar Sales $46,000.00 $32,250.00 $25,800.00 Materials Cast of Goods Sold Quantity per Cost per 16 oz 16 or Carton Carton Total Materials Cost Cartons Strawberry Cheesecake $ Cream Toppings Sugar Carton 7.00 6.50 2.50 1.00 $ $ $ 1.82 2.47 0.15 0.22 $ 4.66 4,000 $ 18,640.00 Cookdes & Cream $ $ Cream Toppings Sugar Carton 11.00 4.50 0.50 1.00 S $ $ 2.86 0.81 0.03 0.22 3.92 3,000 $ 11,760.00 Caramel Nut Cream Toppings Sugor Carton 10.50 3.50 2.00 1.00 $ $ $ $ $ 2.73 0.63 0.12 0.22 3.70 2,400 Total Materials Cout $ $ 8,880.00 39,280.00 Labor Hours 2,000 Labor Cost $35,000.00 6. Enter this actual sales and cost Information into the second tob, "Actual Input" 7. Move to the "Flexible Budget Pref. Report" tab. Prepare a flexible budget performance report for the prior year to find activity variances and revenue and spending variances. Make wre to label your variances "Favorable". "Unfavorable", or "No Variance". Remember that you can't type this directly in the celll You will need to do this by the use of a nested IF statement. Simple "F" statements can be created using the function (x) tool at the top of the page, but you will need to create your nested fatement by hand by typing in your desired rules at the top of the page in the formula bar right next to the function (fx) button. The following is an example of on statement that ouigni a category of year', 'More than a year, or less than a year to the number of days contained in selected cells A1365, 1 year IAL>365,"More than a year. "Less than a year")) Once you have the varlonces labeled os "Favorable", "Unfavorable", or "No Varlonces you will need to color code those cells in order to make them more noticeable and easier to analyze. You can color code them by using conditional formatting. You can find conditional formatting on the "Home" menu in Excel. You will want to select all of your calle containing the text "Favorable". "Unfavorable", or "No Variance" in your preadtheat. Then you will want to go to the Highlight Cell Rules" dro down MAL choose the option of "Equal To..." You will then need to select to highlight your cell equaling Favorable with Green Fill with Dark Green Text" ond your cell equaling Unfavorable with light Red Pill with Dark Red Text". 8. Move to the "DM Variances" tab. Compute the direct materials variances for the prior year. The standard price of the materials will require you to use the "AVERAGE" function. Again, you will need to use your function (fx) tool at the top of the page. You will also need to use "F" statements and Conditional Highlighting as you did in the previous tab for the variances. Follow the same rules for highlighting. Remember that when you are calculating the standard quantities, you are calculating the STANDARD quantities for ACTUAL production, Make sure you fill out all 12 boxes (4 materials for each flavor of ice cream) before moving on. 9. Move to the "DL Variances" tab. Compute the direct labor variances for the prior year. You will need to use the same tools and follow the same highlighting and standard quantity rules as mentioned for the previous tab. 10. Check your spreadsheet against the check figures listed below to ensure you have completed the spreadsheet correctly, 11. Submit your assignment through Blackboard by answering the questions in Blackboard and attaching and submitting your completed template document via your assignment link. Do not use email to submit your project. SPREADSHEET CHECK FIGURES: Tab Cell Check Figure Budgeted Cost of Goods Sold Flexible Budget Performance Report $36,022.50 $6,604.00 Flexible Budget Performance Report Total Budgeted Direct Materials Cost Flexible Budget Net Income Revenue & Spending Variance for Cost of Goods Sold Materials Quantity Variance for Cream for the Strawberry Cheesecake flavor Labor Efficiency Variance $4,174 Unfavorable DM Variances DL Variances $1,000.00 Favorable $2,160 Unfavorable Accounting 365 Flex Budget Project Instructions BEFORE YOU BEGIN, READ CLOSELY... A. Start with the template that can also be found in the Blackboard folder titled "Flex Budget Project". Save this file with your Name in the title. Example) Excel Project Joe Smith B. The first two tabs of the template (indicated with green highlighting) should serve as your input tabs. These tabs will be filled by typing in the input data found in this directions document. Each cell shaded with light grey needs to be filled by you. Other than the first two tabs, the rest of your spreadsheet should be ENTIRELY FORMULA DRIVEN. This means that on the following seven tabs of your spreadsheet, EVERY light grey cell should either reference another cell from the file or should be calculated using a formula which also references the necessary cells. *NO numbers or text can be typed into cells with the exception of your input tabs! Part 1 The Cream Shack is a local business that produces homemade ice cream that it sells to its customers in a 16 oz carton. Although the business has been growing rapidly in the past few years, much of the production process is done in the basement of the owner's house. This helps the company avold paying expensive rent for a building in an effort to keep costs low. The owners of The Cream Shack are attempting to create a budget for the upcoming year to ensure profitability. Currently, The Cream Shack produces three different flavors of ice cream strawberry cheesecake, cookies and cream, and caramel nut that sell at a ratio of 40%, 35%, and 25% of total sales, respectively. All three flavors require cream, sugar, and toppings to produce the final product, but the ratios of how much material is needed differ across the flavors. The table below summarizes the necessary materials to create each product: Quantity (oz) Cream Toppings Sugar Strawberry Cheesecake 8 6 2 Cookies & Cream 10 5 Caramel Nut 11 4 Prices for these ingredients change throughout the course of the year in accordance to the market. For budget making purposes ownership has decided to use an average of prior year costs. The following table summarizes the costs for the different ingredients per ounce across the different quarters of the previous year Cost ($/oz) Cream Sugar Strawberry Cheesecake Topping Cookies & Cream Topping Caramel Nut Topping Quarter 1 0.29 $ 0.06 $ 0.37 $ 0.18 $ 0.12 Quarter 2 $ 0.26 $ 0.07 $ 0.34 $ 0.19 $ 0.15 Quarter 3 $ 0.22 $ 0.08 $ 0.31 $ 0.21 $ 0.19 Quarter 4 $ 0.23 $ 0.07 $ 0.30 $ 0.22 $ 0.14 The only labor necessary for production is provided by the owner who makes $18 per hour. The production process involves three steps that must be done manually. The first step is to mix all of the ingredients together using a machine which typically takes 6 minutes to complete. The second step consists of removing the mixed product from the machine and emptying it into a plastic carton. This process takes about 3 minutes and each carton costs 25 cents. The final step of the labor process involves applying a label to the carton to indicato which flavor it is. The process also takes 3 minutes to complete. Position Right: 0 inli After: O pt Wrap Text Bring Send Forward Backward Selection Allgn Pane Gros The company also has various overhead, selling, and administrative costs. To budget for these costs, the company estimates them using different cost drivers. The Cream Shack has determined that overhead should be applied based on 20% of direct labor costs. Selling and administrative costs are best estimated using a percentage of total revenue. Historical analysis has shown that these expenses run about 12% and 8% of total revenue, respectively, $ For the purposes of the budget, The Cream Shack la predicting unit sales to be 9,000 cartons for the upcoming year. The selling price per 16 oz carton for each of the different product lines is shown below Selling Price Strawberry Cheesecake $ 12.00 Cookies & Creme $ 11.00 Caramel Nut 10.50 1. Enter this information into the first tab, "Budget Input". Also, place your name in the box provided in the disht hand side of the tab. 2. Move to the "Standard Costs" tab. Develop the standard costs for the ice cream regarding direct materials, direct labor and overhead by using your budget input. For certain calculations, you will need to use the AVERAGE function. Remember there is a function(x) tool located at the top of your Excel spreadsheet if you need some guidance with this. Also, remember that you can drag formulas to fill similar cells. This may require "locking" certain cells references within the formula being used. You can lock cells in the formula by highlighting them and pressing the "44"key. 3. Move to the "Budgeted Cost of Goods Sold" tab. Prepare a budgeted cost of goods sold summary for the projected ice cream sales. 4. Move to the "Budgeted 15" tob. Prepare a budgeted Income statement for the upcoming year. 5. Move to the "Gool Seek" tab. Ownership is looking of purchasing a new building to Increase its production capabilities. Use the goal seek tool to find the number of ice cream carton soles necessary to create operating Income of $10,000. Goal seek uses an iterative process to make one cell be a particular value by changing another cel's value, ond for purposes of this project we want operating Income to become 10,000 by changing the number of carton sales. For this to work, your group's spreadsheet must be driven by the inputs tob and set up to flow correctly. Using this tool will give you strong evidence that you are on the right trock in the completion of your spreadsheet. You can find goal seek on the "Data" menu in Excel. It is under the "What Analysis feature. After you hove run goal seek, you need to copy the number of carton sales showing in the carton sale cell within the "Budget Inputs" tab into the cell on the cell within the "Gool Seek" tab by other copying and posting or just typing the number. This is an exception to the no typing rule. You the need to be sure to go back and change the carton sales value on the Budget Input" tab back to 9,000. If you don't do this the rest of your spreadsheet will be running off of the goal seek number rather than the budgeted volue, and the rout of your check figures will not work. Part 2 Focus Shortly after the end of the year, The Croam Shack has compiled Its actual sales and expense data for the prior year and wants to compare it to the budget. The owner was very excited about an increase in sales but could not understand why profits decreased during the year. He wants to see what areas of his business did better than expected and what areas need improvement. Actual data from the year is presented below Income Statement Sales Cost of Goods Sold Gross Margin Selling Expenses Administrative Expenses Net Income $104,050.00 $82,530.00 $21,520.00 $13,526.50 $6,243.00 $1,750.50 Sales Strawberry Cheesecake Cookies & Cream Caramel Nut Unit Sales 4,000 3,000 2,400 Dollar Sales $46,000.00 $32,250.00 $25,800.00 Materials Cost of Goods Sold Quantity per Cost per 16 oz 16 oz Carton Carton Total Materials Cost Cartons Strawberry Cheesecake Cream Toppings Sugar Carton 7.00 6.50 2.50 1.00 $ $ $ $ $ 1.82 2.47 0.15 0.22 4.66 4,000 $ 18,640.00 Cookies & Cream Cream Toppings Sugar Carton 11.00 4.50 0.50 1.00 $ $ $ $ $ 2.86 0.81 0.03 0.22 3.92 3,000 $ 11,760.00 Caramel Nut Cream Toppings Sugar Carton 10.50 3.50 2.00 1.00 $ $ $ $ 2.73 0.63 0.12 0.22 3.70 $ 2,400 $ $ 8,880.00 39,280.00 Total Materials Cost 3.70 2,400 Total Materials Cost 8,880.00 39,280.00 $ Labor Hours 2,000 Labor Cost $35,000.00 6. Enter this actual sales and cost Information into the second tab, "Actual Input 7. Move to the "Flexible Budget Pref. Report" tab. Prepare a flexible budget performance report for the prior year to find activity variances and revenue and spending variances. Make sure to label your variances "Favorable", "Unfavorable", or "No Variance". Remember that you can't type this directly in the cell You will need to do this by the use of a nested "IP" statement Simple "F" statements can be created using the function (fx) tool at the top of the page, but you will need to create your nested "F" statement by hand by typing in your desired rules at the top of the page in the formula bar right next to the function (fx) button. The following is an example of an "F" statement that assigns a category of 'l year', 'More than a year', or "Less than a year' to the number of days contained in selected cells. *E(A1-365, "1 year" if (A1>365,"More than a year", "Less than a year")). Once you have the variances labeled as "Favorable", "Unfavorable", or "No Variance", you will need to color code those cells in order to make them more noticeable and easier to analyze. You can color code them by using conditional formatting. You can find conditional formatting on the "Home" menu in Excel. You will want to select all of your cells containing the text "Favorable", "Unfavorable", or "No Variance" in your spreadsheet. Then you will want to go to the "Highlight Cells Rules" drop down menu, and choose the option of "Equal To...". You will then need to select to highlight your cells equaling Favorable with "Green Fill with Dark Green Text" and your cells equaling Unfavorable with light Red Fill with Dark Red Text". 8. Move to the "DM Varlances" tab. Compute the direct materials variances for the prior year. The standard price of the materials will require you to use the "AVERAGE" function. Again, you will need to use your function (fx) tool at the top of the page. You will also need to use "F" statements and Conditional Highlighting as you did in the previous tab for the variances. Follow the same rules for highlighting. Remember that when you are calculating the standard quantities, you are calculating the STANDARD quantities for ACTUAL production. Make sure you fill out all 12 boxes (4 materials for each flavor of ice cream) before moving on. 9. Move to the "DL Variances" tab. Compute the direct labor variances for the prior year. You will need to use the same tools and follow the same highlighting and standard quantity rules as mentioned for the previous tab. 10. Check your spreadsheet against the check figures listed below to ensure you have completed the spreadsheet correctly. 11. Submit your assignment through Blackboard by answering the questions in Blackboard and attaching and submitting your completed template document via your assignment link. Do not use email to submit your project SPREADSHEET CHECK FIGURES: Tab Cell Check Figure Budgeted Cost of Goods Sold Flexible Budget Performance Report $36,022.50 $6,604.00 Total Budgeted Direct Materials Cost Flexible Budget Net Income Revenue & Spending Variance for Cost of Goods Sold Materials Quantity Variance for Cream for the Strawberry Cheesecake flavor Labor Efficiency Variance Flexible Budget Performance Report $4,174 Unfavorable DM Variances DL Variances $1,000.00 Favorable $2,160 Unfavorable Accounting 365 Flex Budget Project Instruction BEFORE YOU NON HEAD CLOSELY Asert with the con le fond dood old medale Perife with your Women Empel be The first of the dedykoteThese robe beled who had found a reminded WWW NY FORMULA DRIVER Therefore of your prendre, IVERY war france were www.fr "WO pediate with the creation of your input Pan1 The Cross the who 1 cm. Alte som en reply peep, heber behalf on Dee Chron Stockwere boererar Currently. The Shower and en de 2014.39% 23% of , el All rente, per, and define prode, but are owned normal an. Stay Cheese Cookies Com C Ce! Quarter 1 Quartz Querer 0:29 5 036 1 0.22 $ 033 Sego 1 0.06 1 0.00 1 DOM $ 0.00 Sebanyo Taping 5 31 034 1 0.31 1 0.30 Cole & Co $ 0.18 1 0.19 . 0.21 1 0.22 0.12 1 0.15 0.12 1 0.14 They are wided by the hipew. The production proche de reprehendine how copco oferecidos products The other website www ste The closed to budget to the coming de The Creath has two bedre web page has the shore and reply for the budget, the there was to be 9.000 The coming The repor 18 to the red ye Cake Car 5 12.00 11160 10.50 2. Med derepending odberund rated by a rewig te vreed to use AVERAGE a tulee ping her 2. Herred Cost of Good report the cor prethod many due the procedemel More sted sabe Freure bude to the vine had to resurrectly in the fool will your widence that you are employees You can find hold on to Denme hat under the West Aly" Bere. After you have gre, you need yteroster or sale height yerindoping the typing den 001. you do of procurer on the budgeted wole, and there of your crack figures will not work Senty Sher the end of the year. The Cran Shack has compiled maculo and done for your and were comportere the world that were in a blandt andre to where to bushen edhe red and where ed imprenditran the years Income Sule 3164,000.00 Com of God 113,530.00 Grom Mergh 121,320.00 Seling $13,525.50 Awit pe 16.260.00 11750.50 Sales Doloros Sebeny Och 4,000 $4,000.00 Cases Cream 1.000 532,310.00 Core 2.400 525 100.00 Cates DI Q 16 or Carter Calper 16 Se Torel Me Car Carte Seberry Desich Topp Super 7.00 1 6501 2.50 2.0 0.15 Casa Malle Qey SC Car Cart To Male Cour troom Topping Super 7.00 1 2.30 1.005 3 18 3. 18 0.3) 4000 $ 13.00 GO Spor Carton 11.00 401 0.01 1.00 5 285 031 003 022 30 3,000 11.700.00 CH 0.01 2.01 2001 0 1 23 go 012 03 370 Cart 1,600 Tulle M. 0 0 Labas Heute her Com . 1. Mare Baterpetua wywod how do Morselyne view Yowed to deve implembe rewolwerp te bere you and whether anarhul are opere for yo'o de derde etapew", " low One piele, Valio, you with and we Yound by fortel Yel your caleconeixe'n yw it. The worden. Yndyrare Durk Green Darlar Meteo Cathedraler for the perpet. The rendre el resto AVIAO A you were he was Cong or you did pe cartoon Polows for high Tenester hat es geweest ACTUA 10 watt En 39200.00 Lab Men 37000 Lab Cew 351.000.00 1. The de Trattupurab tree of legere the periore o Tady vendengar Maker who were el directly in the call toward by the name timplemented the page you will need to note your mind wat by order typing in your desired to the page the relation the plus remony More than your thorpeate the day or need Owe yoted World, Variana, wieder des cliente en andeny.cc before you to mwing en toe in Pasal You will all of your comment below, Verion you reaches they will want to tell and the Tweede Werte higher leverer i Durre lorem feverede i Dantelor * MOM V Comercivarse por the moon par viegue y AVERAGE And you tendon se did has tobom Pulire the one whestur 10. Oddych you howed here are areAnser co hours Check your Tw 0400 twted ved Material tante para com og varme for Cust I Metal yarance for Dream OM 310000 1.3.160 inwe AutoSave ABES 3.Flex Budget Project - TEMPLATE Home Insert Draw Page Layout Formulas Data Review View Tell me Calibr (Body) 11 AA 29 Wrap Text Gener Paste BTU A Merge & Center C51 Vfx Budget Input Information 200 Canon Segre Norton ON Wh CA 10 Cent 11.00 11.00 10.50 Dista 11 Quantity carton beny Cheese Cook Coruncit to 1 C Town 18 Cestised Gusta Quarter Dara 1 C 2 2 Sty Chepong 26 Cookies Camping trutto 74 Cestito 1 Guntar Gun 11 Dr 12 11 her mus 1. Mary Solo 41 Period Cou 11 Sep 14 A alat me No Budget Input Actual input Standard Costa Budgeted Cost of Goods Sold Budgeted is Ready D Actual Input Information Carton Sales Dollar Sales 3 Sales 4 5 Strawberry Cheesecake 6 Cookies & Cream 7 Caramel Nut 3 9 Materials Quantity per 16 Cost per 16 o Carton Carton Total Materials Cost Cartons 10 11 Strawberry Cheesecake 12 Cream Toppings Sugar Carton 13 14 15 26 7 Cookies & Cream 18 19 20 1 -2 3. Caramel Nut 4 5 6 7 Cream Toppings Sugar Carton Cream Toppings Sugar Carton 9 Total Materials Cost 3 Labor 2 3 Hours Labor Cost Income Statement Sales COGS Gross Margin Selling Expenses Administrative Expenses Net Income Budget Input Actual Input Standard Costs Budgeted Cost of Goods Solc Read A B D 1 Standard Costs 3 Direct Materials Strawberry Cheesecake 4 Cookies & Cream Caramel Nut Cost per Cost per Carton Cost per Carton 5 Cream 6 Sugar 7 Strawberry Cheesecake Topping 16oz 8 Cookies & Cream Topping Carton 9 Caramel Nut Topping 10 Carton 11 Total Direct Material Cost per Carton 12 13 14 Direct Labor 15 16 Mixing the ingredients Labor 17 Activities Filling the Cartons 18 Labeling the Cartons 19 Total Direct Labor Cost per Carton 20 21 22 Manufacturing Overhead 23 24 Direct Labor Cost 25 Overhead Application Rate 26 Total Overhead Cost per Carton 27 28 29 30 31 32 33 34 35 36 37 38 39 10 11 42 Budget Input Actual Input Standard Costs Budgeted Cost of Goods Sold 1 Budgeted Cost of Goods Sold Statement Cost per Carton Number of Cartons Total Cost 3 4 Direct Materials 5 Strawberry Cheesecake 6 Cookies & Cream 7 Caramel Nut 8 Total Direct Materials 9 Direct Labor 10 Manufacturing Overhead 11 Cost of Goods Sold 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 Budget Input Actual Input Standard Costs Budgeted Cost of Goods Sold Bud Ready Accounting 365 Flex Budget Project Instructions BEFORE YOU BEGIN, READ CLOSELY.... A Start with the template that can also be found in the Blackboard folder titled "Flex Budget Project". Sove this file with your Nome in the title Example) Excel Project Joe Smith B. The first two tabs of the template (indicated with green highlighting) should serve as your input tabs. These tabs will be filled by typing in the input data found in this directions document. Each cell shaded with light grey needs to be filled by you. Other than the first two fobs, the rest of your spreadsheet should be ENTIRELY FORMULA DRIVEN. This means that on the following seven tabs of your spreadsheet, EVERY light grey cell should either reference another cell from the file or should be calculated using a formula which also references the necessary cells. *NO numbers or text can be typed into cells with the exception of your input tebal Part 1 The Cream Shack is a local business that produces homemade ice cream that it sells to its customers in a 16 oz carton. Although the business has been growing rapidly in the past few years, much of the production process is done in the basement of the owner's house. This helps the company avoid paying expensive rent for a building in an effort to keep costs low. The owners of The Cream Shack are attempting to create a budget for the upcoming year to ensure profitability. Currently, The Cream Shock produces three different flavors of ice cream strawberry cheesecake, cookles and cream, and caramel nut that sell at a ratio of 40%, 35%, and 25% of total sales, respectively. All three flavors require cream, sugar, and toppings to produce the final product, but she ratios of how much material is needed differ across the flavors. The table below summarizes the necessary materials to create each product Quantity (oz) Strawberry Cheesecake Cookies & Cream 10 5 Caramel Nut 11 Cream Toppings Sugar 6 2 Prices for these ingredients change throughout the course of the year in accordance to the market. For budget moking purposes ownership has decided to use an average of prior year costs. The following table summarizes the costs for the different Ingredients per ounce across the different quarters of the previous year Cost ($/03) Quarter 1 Quarter 2 Quarter 3 Quarter 4 Cream 5 0.29 $ 0.26 $ 0.22 $ 0.23 Sugar $ 0.06 $ 0.07 $ 0.08 $ 0.07 Strawberry Cheesecake Topping $ 0.37 $ 0.34 $ 0.31 $ 0.30 Cookies & Cream Topping $ 0.18 $ 0.19 $ 0.21 $ 0.22 Caramel Nut Topping $ 0.12 $ 0.15 $ 0.19 $ 0.14 The only labor necessary for production is provided by the owner who makes $18 per hour. The production process involves three steps that must be done manually. The first step is to mix all of the ingredients together using a machine which typically takes 6 minutes to complete. The second step consults of removing the mixed product from the machine and emptying it into a plastic carton. This process tokes about 3 minutes and each carton costs 25 cents. The final step of the labor process involves applying a label to the carton to indicate which flavor it. The process also tokes 3 minutes to complete. The company also has various overhead, selling, and administrative costs. To budget for these costs, the company estimates them using different cost drivers. The Cream Shack hos determined that overhead should be applied based on 20% of direct labor cost. Selling and administrative costs are best estimated using a percentage of total revenue. Historical analysis has shown that these expenses run about 12% and 8% of total revenue, respectively, For the purposes of the budget, The Cream Shack is predicting unit soles to be 9,000 cartons for the upcoming year. The selling price per 16 oz carton for each of the different product lines is shown below: Strawberry Cheesecake Cookies & Creme Caramel Nut Selling Price $ 12.00 $ 11.00 $ 10.50 1. Enter this information into the first tab, "Budget Input". Also, place your name in the box provided in the doht. band side of the tab. 2. Move to the Standard Cosh" tab. Develop the standard costs for the ice cream regarding direct materials, direct labor and overhead by using your budget input. For certain calculations, you will need to use the AVERAGE function. Remember there is a function (fx) tool located at the top of your Excel spreadsheet If you need some guidance with this. Also, remember that you can drag formulas to fill similar cells. This may require Mocking certain cells references within the formula being used. You can lock cells in the formulo by highlighting them and pressing the "44"key. 3. Move to the Budgeted Coat Goods Sold" tab. Prepare a budgeted cost of goods sold summary for the projected ice cream sales 4. Move to the "Budgeted IS" tab. Prepare a budgeted income statement for the upcoming year. 5. Move to the "Gool Seek" tob. Ownership is looking at purchasing a new building to Increase its production capabilities. Use the goal seek tool to find the number of ice cream carton sales necessary to create operating Income of $10,000. Goal seakuses on iterative process to make one cell be a particular value by changing another call's value, and for purposes of this project we want operating income to become 10,000 by changing the number of carton saleFor this to work, your group's spreadsheet must be driven by the Inputs tab and set up to flow correctly. Using this tool will give you strong evidence that you are on the right trock in the completion of your spreadsheet. You can find goal seek on the "Data" menu In Excel. It is under the "What-If Analysis" feature. After you have run goal seek, you need to copy the number of carton sales showing in the carton soles cell within the "Budget Inputs" tob Into the call on the cell within the "Goal Seek" tab by elther copying and pasting or just typing the number. This is an exception to the no typing rule. You then need to be sure to go back and change the carton sales value on the "Budget Inputs" tab back to 9,000. If you don't do this the rest of your spreadsheet will be running off of the goal seek number rather than the budgeted value, and the rest of your check figures will not work. Part 2 Shortly after the end of the year, The Cream Shock has compiled its actual sales and expense data for the prior year and wants to compare it to the budget. The owner was very excited about an increase in sales but could not understand why profin decreased during the year. He wants to see what areas of his business did better than expected and what areos need improvement. Actual data from the year la presented belowi Income Statement Soles Cost of Goods Sold Grou Margin Selling Expenses Administrative Expenses Net Income $104,050.00 $82,530.00 $21,520.00 $13,526.50 $6,243.00 $1.750.50 Sales Strawberry Cheesecake Cookies & Cream Caramel Nyt Unit Sales 4,000 3,000 2.400 Dollar Sales $46,000.00 $32,250.00 $25,800.00 Materials Cast of Goods Sold Quantity per Cost per 16 oz 16 or Carton Carton Total Materials Cost Cartons Strawberry Cheesecake $ Cream Toppings Sugar Carton 7.00 6.50 2.50 1.00 $ $ $ 1.82 2.47 0.15 0.22 $ 4.66 4,000 $ 18,640.00 Cookdes & Cream $ $ Cream Toppings Sugar Carton 11.00 4.50 0.50 1.00 S $ $ 2.86 0.81 0.03 0.22 3.92 3,000 $ 11,760.00 Caramel Nut Cream Toppings Sugor Carton 10.50 3.50 2.00 1.00 $ $ $ $ $ 2.73 0.63 0.12 0.22 3.70 2,400 Total Materials Cout $ $ 8,880.00 39,280.00 Labor Hours 2,000 Labor Cost $35,000.00 6. Enter this actual sales and cost Information into the second tob, "Actual Input" 7. Move to the "Flexible Budget Pref. Report" tab. Prepare a flexible budget performance report for the prior year to find activity variances and revenue and spending variances. Make wre to label your variances "Favorable". "Unfavorable", or "No Variance". Remember that you can't type this directly in the celll You will need to do this by the use of a nested IF statement. Simple "F" statements can be created using the function (x) tool at the top of the page, but you will need to create your nested fatement by hand by typing in your desired rules at the top of the page in the formula bar right next to the function (fx) button. The following is an example of on statement that ouigni a category of year', 'More than a year, or less than a year to the number of days contained in selected cells A1365, 1 year IAL>365,"More than a year. "Less than a year")) Once you have the varlonces labeled os "Favorable", "Unfavorable", or "No Varlonces you will need to color code those cells in order to make them more noticeable and easier to analyze. You can color code them by using conditional formatting. You can find conditional formatting on the "Home" menu in Excel. You will want to select all of your calle containing the text "Favorable". "Unfavorable", or "No Variance" in your preadtheat. Then you will want to go to the Highlight Cell Rules" dro down MAL choose the option of "Equal To..." You will then need to select to highlight your cell equaling Favorable with Green Fill with Dark Green Text" ond your cell equaling Unfavorable with light Red Pill with Dark Red Text". 8. Move to the "DM Variances" tab. Compute the direct materials variances for the prior year. The standard price of the materials will require you to use the "AVERAGE" function. Again, you will need to use your function (fx) tool at the top of the page. You will also need to use "F" statements and Conditional Highlighting as you did in the previous tab for the variances. Follow the same rules for highlighting. Remember that when you are calculating the standard quantities, you are calculating the STANDARD quantities for ACTUAL production, Make sure you fill out all 12 boxes (4 materials for each flavor of ice cream) before moving on. 9. Move to the "DL Variances" tab. Compute the direct labor variances for the prior year. You will need to use the same tools and follow the same highlighting and standard quantity rules as mentioned for the previous tab. 10. Check your spreadsheet against the check figures listed below to ensure you have completed the spreadsheet correctly, 11. Submit your assignment through Blackboard by answering the questions in Blackboard and attaching and submitting your completed template document via your assignment link. Do not use email to submit your project. SPREADSHEET CHECK FIGURES: Tab Cell Check Figure Budgeted Cost of Goods Sold Flexible Budget Performance Report $36,022.50 $6,604.00 Flexible Budget Performance Report Total Budgeted Direct Materials Cost Flexible Budget Net Income Revenue & Spending Variance for Cost of Goods Sold Materials Quantity Variance for Cream for the Strawberry Cheesecake flavor Labor Efficiency Variance $4,174 Unfavorable DM Variances DL Variances $1,000.00 Favorable $2,160 Unfavorable Accounting 365 Flex Budget Project Instructions BEFORE YOU BEGIN, READ CLOSELY... A. Start with the template that can also be found in the Blackboard folder titled "Flex Budget Project". Save this file with your Name in the title. Example) Excel Project Joe Smith B. The first two tabs of the template (indicated with green highlighting) should serve as your input tabs. These tabs will be filled by typing in the input data found in this directions document. Each cell shaded with light grey needs to be filled by you. Other than the first two tabs, the rest of your spreadsheet should be ENTIRELY FORMULA DRIVEN. This means that on the following seven tabs of your spreadsheet, EVERY light grey cell should either reference another cell from the file or should be calculated using a formula which also references the necessary cells. *NO numbers or text can be typed into cells with the exception of your input tabs! Part 1 The Cream Shack is a local business that produces homemade ice cream that it sells to its customers in a 16 oz carton. Although the business has been growing rapidly in the past few years, much of the production process is done in the basement of the owner's house. This helps the company avold paying expensive rent for a building in an effort to keep costs low. The owners of The Cream Shack are attempting to create a budget for the upcoming year to ensure profitability. Currently, The Cream Shack produces three different flavors of ice cream strawberry cheesecake, cookies and cream, and caramel nut that sell at a ratio of 40%, 35%, and 25% of total sales, respectively. All three flavors require cream, sugar, and toppings to produce the final product, but the ratios of how much material is needed differ across the flavors. The table below summarizes the necessary materials to create each product: Quantity (oz) Cream Toppings Sugar Strawberry Cheesecake 8 6 2 Cookies & Cream 10 5 Caramel Nut 11 4 Prices for these ingredients change throughout the course of the year in accordance to the market. For budget making purposes ownership has decided to use an average of prior year costs. The following table summarizes the costs for the different ingredients per ounce across the different quarters of the previous year Cost ($/oz) Cream Sugar Strawberry Cheesecake Topping Cookies & Cream Topping Caramel Nut Topping Quarter 1 0.29 $ 0.06 $ 0.37 $ 0.18 $ 0.12 Quarter 2 $ 0.26 $ 0.07 $ 0.34 $ 0.19 $ 0.15 Quarter 3 $ 0.22 $ 0.08 $ 0.31 $ 0.21 $ 0.19 Quarter 4 $ 0.23 $ 0.07 $ 0.30 $ 0.22 $ 0.14 The only labor necessary for production is provided by the owner who makes $18 per hour. The production process involves three steps that must be done manually. The first step is to mix all of the ingredients together using a machine which typically takes 6 minutes to complete. The second step consists of removing the mixed product from the machine and emptying it into a plastic carton. This process takes about 3 minutes and each carton costs 25 cents. The final step of the labor process involves applying a label to the carton to indicato which flavor it is. The process also takes 3 minutes to complete. Position Right: 0 inli After: O pt Wrap Text Bring Send Forward Backward Selection Allgn Pane Gros The company also has various overhead, selling, and administrative costs. To budget for these costs, the company estimates them using different cost drivers. The Cream Shack has determined that overhead should be applied based on 20% of direct labor costs. Selling and administrative costs are best estimated using a percentage of total revenue. Historical analysis has shown that these expenses run about 12% and 8% of total revenue, respectively, $ For the purposes of the budget, The Cream Shack la predicting unit sales to be 9,000 cartons for the upcoming year. The selling price per 16 oz carton for each of the different product lines is shown below Selling Price Strawberry Cheesecake $ 12.00 Cookies & Creme $ 11.00 Caramel Nut 10.50 1. Enter this information into the first tab, "Budget Input". Also, place your name in the box provided in the disht hand side of the tab. 2. Move to the "Standard Costs" tab. Develop the standard costs for the ice cream regarding direct materials, direct labor and overhead by using your budget input. For certain calculations, you will need to use the AVERAGE function. Remember there is a function(x) tool located at the top of your Excel spreadsheet if you need some guidance with this. Also, remember that you can drag formulas to fill similar cells. This may require "locking" certain cells references within the formula being used. You can lock cells in the formula by highlighting them and pressing the "44"key. 3. Move to the "Budgeted Cost of Goods Sold" tab. Prepare a budgeted cost of goods sold summary for the projected ice cream sales. 4. Move to the "Budgeted 15" tob. Prepare a budgeted Income statement for the upcoming year. 5. Move to the "Gool Seek" tab. Ownership is looking of purchasing a new building to Increase its production capabilities. Use the goal seek tool to find the number of ice cream carton soles necessary to create operating Income of $10,000. Goal seek uses an iterative process to make one cell be a particular value by changing another cel's value, ond for purposes of this project we want operating Income to become 10,000 by changing the number of carton sales. For this to work, your group's spreadsheet must be driven by the inputs tob and set up to flow correctly. Using this tool will give you strong evidence that you are on the right trock in the completion of your spreadsheet. You can find goal seek on the "Data" menu in Excel. It is under the "What Analysis feature. After you hove run goal seek, you need to copy the number of carton sales showing in the carton sale cell within the "Budget Inputs" tab into the cell on the cell within the "Gool Seek" tab by other copying and posting or just typing the number. This is an exception to the no typing rule. You the need to be sure to go back and change the carton sales value on the Budget Input" tab back to 9,000. If you don't do this the rest of your spreadsheet will be running off of the goal seek number rather than the budgeted volue, and the rout of your check figures will not work. Part 2 Focus Shortly after the end of the year, The Croam Shack has compiled Its actual sales and expense data for the prior year and wants to compare it to the budget. The owner was very excited about an increase in sales but could not understand why profits decreased during the year. He wants to see what areas of his business did better than expected and what areas need improvement. Actual data from the year is presented below Income Statement Sales Cost of Goods Sold Gross Margin Selling Expenses Administrative Expenses Net Income $104,050.00 $82,530.00 $21,520.00 $13,526.50 $6,243.00 $1,750.50 Sales Strawberry Cheesecake Cookies & Cream Caramel Nut Unit Sales 4,000 3,000 2,400 Dollar Sales $46,000.00 $32,250.00 $25,800.00 Materials Cost of Goods Sold Quantity per Cost per 16 oz 16 oz Carton Carton Total Materials Cost Cartons Strawberry Cheesecake Cream Toppings Sugar Carton 7.00 6.50 2.50 1.00 $ $ $ $ $ 1.82 2.47 0.15 0.22 4.66 4,000 $ 18,640.00 Cookies & Cream Cream Toppings Sugar Carton 11.00 4.50 0.50 1.00 $ $ $ $ $ 2.86 0.81 0.03 0.22 3.92 3,000 $ 11,760.00 Caramel Nut Cream Toppings Sugar Carton 10.50 3.50 2.00 1.00 $ $ $ $ 2.73 0.63 0.12 0.22 3.70 $ 2,400 $ $ 8,880.00 39,280.00 Total Materials Cost 3.70 2,400 Total Materials Cost 8,880.00 39,280.00 $ Labor Hours 2,000 Labor Cost $35,000.00 6. Enter this actual sales and cost Information into the second tab, "Actual Input 7. Move to the "Flexible Budget Pref. Report" tab. Prepare a flexible budget performance report for the prior year to find activity variances and revenue and spending variances. Make sure to label your variances "Favorable", "Unfavorable", or "No Variance". Remember that you can't type this directly in the cell You will need to do this by the use of a nested "IP" statement Simple "F" statements can be created using the function (fx) tool at the top of the page, but you will need to create your nested "F" statement by hand by typing in your desired rules at the top of the page in the formula bar right next to the function (fx) button. The following is an example of an "F" statement that assigns a category of 'l year', 'More than a year', or "Less than a year' to the number of days contained in selected cells. *E(A1-365, "1 year" if (A1>365,"More than a year", "Less than a year")). Once you have the variances labeled as "Favorable", "Unfavorable", or "No Variance", you will need to color code those cells in order to make them more noticeable and easier to analyze. You can color code them by using conditional formatting. You can find conditional formatting on the "Home" menu in Excel. You will want to select all of your cells containing the text "Favorable", "Unfavorable", or "No Variance" in your spreadsheet. Then you will want to go to the "Highlight Cells Rules" drop down menu, and choose the option of "Equal To...". You will then need to select to highlight your cells equaling Favorable with "Green Fill with Dark Green Text" and your cells equaling Unfavorable with light Red Fill with Dark Red Text". 8. Move to the "DM Varlances" tab. Compute the direct materials variances for the prior year. The standard price of the materials will require you to use the "AVERAGE" function. Again, you will need to use your function (fx) tool at the top of the page. You will also need to use "F" statements and Conditional Highlighting as you did in the previous tab for the variances. Follow the same rules for highlighting. Remember that when you are calculating the standard quantities, you are calculating the STANDARD quantities for ACTUAL production. Make sure you fill out all 12 boxes (4 materials for each flavor of ice cream) before moving on. 9. Move to the "DL Variances" tab. Compute the direct labor variances for the prior year. You will need to use the same tools and follow the same highlighting and standard quantity rules as mentioned for the previous tab. 10. Check your spreadsheet against the check figures listed below to ensure you have completed the spreadsheet correctly. 11. Submit your assignment through Blackboard by answering the questions in Blackboard and attaching and submitting your completed template document via your assignment link. Do not use email to submit your project SPREADSHEET CHECK FIGURES: Tab Cell Check Figure Budgeted Cost of Goods Sold Flexible Budget Performance Report $36,022.50 $6,604.00 Total Budgeted Direct Materials Cost Flexible Budget Net Income Revenue & Spending Variance for Cost of Goods Sold Materials Quantity Variance for Cream for the Strawberry Cheesecake flavor Labor Efficiency Variance Flexible Budget Performance Report $4,174 Unfavorable DM Variances DL Variances $1,000.00 Favorable $2,160 Unfavorable