Please show me the formula or how you get the answers

Direct Material Variances Hand Ne Price Nie we At MAP 40 nevis ware Maand Malwe Math Actu AD Nu MORAP AKY Inc Mwen Variante Materiale Mware VW Mware at le Variante tard Y Mandarin C OOT NY and Pri Actual Quantity des Price AR. w AQUAP NG AGAP AGA Maria WWE Mean Guitare Mawlane Marianne Vine Ver Maarid Prie Vie Moduly Goose het budget Port Report Sun Bust Budget Input Best of Goods Sold DM Var Budgeted is De Accounting 365 Flex Budget Project Instructions BEFORE YOU BEGIN, READ CLOSELY Start with the template that can also be found in the Blackboard folder titled "Flex Budget Project". Seve this file with your None hit the title Example) Excel Project Joe Smith The first two tobs of the template Indicated with green Highlighting) should serve as your input tabs. These tobe will be filled by typing in the input data found in this direction 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 tobt of your spreadsheet, EVERY light grey cel should other reference another call from the file or should be calculated using a formula which also references the necessary cells. "NO numbers or text can be typed into calls with the exception of your input fubd Part 1 The Cream Shook is a local business that produces homemade ice cream that it sel to its customen ha 16 or 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 on effort to keep costs low. The owner 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, cookies and cream, and caramel rut that sell of a ratio of 40%, 35% and 25% of total soles, respectively. All three flavors require creom, uger, and toppings to produce the final product, but the ratio of how much material is needed differ scrow the flavor The table below summorts the necessary materials to create soch product Quantity (er) Strawberry Cheesecake Creom Toppings Sugar Cookies & Cream 10 5 1 Caramel Nur 11 4 1 6 2 Prices for these ingredients change throughout the course of the year in occordance to the market. For budget moking purposes ownership has decided to use on average of prior year costs. The following foble summarizes the cost for the different ingredients per ounce across the different quarters of the previous year Cost (5/2) Creom 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 5 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 fokes obout 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 is. The process also takes 3 minutes to complete The company also hos various overhead, selling, and administrative costs. To budget for these cort, the company estimates them using different cout drivers. The Cream Shock has determined that overhead should be applied based on 20% of direct labor coat. 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 Gream Shock la predicting unit oled to be 9,000 cartons for the upcoming year. The selling price per 16 oz carton for each of the different product lines in shown below Strawberry Cheesecake Cookies & Creme Coromal Nut Selling Price $ 12.00 $ 11.00 $ 10.50 Focus Tex Forward Backward Pane 1. Enter this information into the finto, Budget Inpur. Also, place your come the box provided in the othod ide of the tob. 2. Move to the Standard Codetab Develop the standard cost for the ice cream regarding direct materials, direct labor and overhead by using your budget Input For carton collations, you will need to vie the AVERAOE fundion. Remember there is a function () Follocated at the top of your Excel spreadsheet you need some guidance with thiAho, remember that you can drog formules to fill similar cells. This may require Macking certain cells references with the formule being wted. You can lock" calls in the formule by highlighting them ord prossing the way 3. Move to "budgeted Coit of Goods Sold" tob. Prepare a budgeted cont of goods told uummary for the projected ice cream solen 4. Move to the Budgeted IS" tab. Prepare a budgeted Income statement for the upcoming voor 5. Move to the Good Soek" tab. Owarihip n tooking at purchasing a new building to her on the produelon capabilita. Uut the pool seek tool to find the number of ke creom corton soles necessary to create operating Income of $10,000 Goal soek vies on erotive process to make one call be o portor volve by changing other cell's volue, and for ourposes of the projed we want operorng tecom to become 10,000 by changing the number of cofon solat. For this to work, your group's spreadsheet must be driven by fun tob and set up to flow correctly Using this fool will give you strong evidence that you are on the right track in the completion of you prodhout You can find goolveek on the "Dato me in Excel is under the 'Who Anelle fosture. After you have in gol, you need to copy the number of corion sales showing the cotton sales cell within the Budget puls" tab Wo the call on the cell within the coffee to buy the copying and pouring of typing the number. The son exception to the notyping rule You thanned to be sure to go back and change the carton las volia on the 'Budger input tobbock - 9,000, you don't do the the rest of your preacher will be wing off of the goalele rumber rather than the budgeted vol, and there of you the figures will not work I Part 2 Shortly after the end of the year, The Croom Shock hos compiled in actual tolerond expense data for the prior year and work to compare to the budget. The owner was very excited about an increase in s but could not understand whyprolin decreased during the year. He Woods to see who are expected and what orvos need improvement Adual dose from the year la presented below bushew did better than Income Statement Soles $104,050.00 Cost of Goods Sold 582.530.00 Oro Margin 321,320,00 Seling Experies $13,526,50 Administrative Expenses $6,243.00 Nel income 31750.50 Sales Unit Soler Dollar Sales Strawberry Cheesecake 4,000 $46,000.00 Cookies & Cream 3,000 $32,250,00 Carane Nur 2.400 $25,800.00 Material Quantity per 16 o Corton Cost per 16 Cart Cost of Goods Sold Total Materials Cortona Cout Strawberry Cheesecake Cream Toppings 7.00 5 6,505 1.82 2:47 Materials Quantity per 16 on Carton Cost of Goods Sold Cost per 1 or Total Materials Carton Carton Cout Strawberry Cheesecake Cream Toppings Sugar Carton 7.00 $ 6.50 $ 2.50 $ 1.00 $ 5 1.82 2.47 0.15 0.22 4.88 4,000 $ 18,640.00 Cookies & Cream Creem 11.00 $ 2.86 Toppings Sugar Corton 4.50 $ 0.50 $ 1.00 5 3 0.81 0.03 0.22 3.92 3,000 $ 11,760.00 Caramel Nut $ $ Cream Toppings Sugar Corton 10.50 3.50 2.00 1.00 $ 2.73 0.83 0.12 0.22 370 $ 13 2.400 B.880.00 39,280.00 Totol Materials Cost Labor Hours 2,000 Labor Coul $35,000.00 6. Enter this actual sales and cost information into the second tab, "Actual pur 7. Move to the "Flexible Budget Pref. Report" tab. Prepare a flexible budget performance report for the prior your to find octivity variances and revenue and spending variancet. Make sure to lobel your variances "Favorable "Unfavorable", or "No Vorlance? Remember that you can'type the directly in the call you will need to do this by the wie of a nested statement Simple stotements can be created using the function (fa) tool at the top of the page, but you will need to create your nested statement by hand by typing in your desired Niles at the top of the page in the formula bar roh next to the function (x) button. The following is an example of on "Yo stotement that aulom a category of year More than a year, or "Less than a yeo/' to the number of days contained in selected cele A365, 1 yearf (A1>365,"More than a year, less than a year Once you have the variances labeled or "Fovorable", "Unfavorable", or "No Variance, you will need to color code thone calls in order to make them more noticeable and coser to analyse. You can color code them by ving conditional formatting. You can find conditional formatting on the "Home" menu in Excel You will want to telect all of your cells containing the text "FavorableUnfavorable", or "No Varlonce your spreadsheet. Then you will want to go to the "Highlight Calle Rules drop down choose the option of Equal To.. You will then need to select to Noh your collequeling Favorable with "Green Fill with Dark Green Texe" and your celli equoling Unfavorable with light Red Fit with Dark Red Text 8. Move to the DMVariancas" tab. Compute the dive material variance for the prior year. The standard price of the materich will require you to use the PAVERAGE" function. Agory you will need to use your function () fool at the top of the page. You will need to use " Potener and Conditional Highlighting at you did in the previous tab for the Vorlonces. Follow the some rules for highlighting Remember that when you are calculating the standard quantities you are colculating the STANDARD quantities for ACTUAL production TOR Ddewdi Polie Once you have the variances labeled as "Favorable", "Unfavorable" or "No Vorlonce", 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 "Homo" 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 men 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 Variances" tob. Compute the direct materials verlences 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 of the top of the page. You will also need to use "P" 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 colculating the standard quanilles, you are calculating the STANDARD quarties for ACTUAL production Make sure you fill out all 12 boxen (materials for each flavor of ice cream) before moving on. 9. Move to the "DL Variancel" tob. Compute the direct labor vorlonces for the prior year. You will need to use the same tools and follow the same highlighting and Mandard quantity rules as mentioned for the previous tob. 10. Check your spreadsheet against the check figures thated below to ensure you have completed the spreadsheet correctly 11. Submit your enignment through Blackboard by onewering the questions in Boekhoord ond ontaching and submitting your completed template document via your onlignment link. Do not use email to submit your project. I 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 Sugar Cream Standard Quantity Actual Quantity Standard Price Actual Price Standard Quantity Actual Quantity Standard Price Actual Price AQ X SP SQ x SP AQ X AP AQX SP SQX SP AQX AP Amount Favorable/Unfavorable Amount Favorable/Unfavorable Material Price Variance Material Quantity Variance Material Price Variance Material Quantity Variance Cookies Cream Sugar Standard Quantity Actual Quantity Standard Price Actual Price Standard Quantity Actual Quantity Standard Price Actual Price AQx AP AQX SP SQX SP AQX AP AQ X SP SQ x SP Amount Favorable/Unfavorable Amount Favorable/Unfavorable Material Prke Variance Material Quantity Variance Material Price Variance Material Quantity Variance Caran Cream Sugar Standard Quantity Actual Quantity Standard Price Actual Price Standard Quantity Actual Quantity Standard Price Actual Price AQXAP AQ X SP SQ SP AQ X AP AQ X SP SQ X SP Amount Favorable/Unfavorable Amount Favorable/Unfavorable Material Price Variance Material Quantity Variance Material Price Variance Material Quantity Variance Budaat Innut M N Toppings Cartons Standard Quantity Actual Quantity Standard Price Actual Price Standard Quantity Actual Quantity Standard Price Actual Price AQXAP AQX SP SQXSP AQ X AP AQX SP SQ x SP orable Amount Favorable/Unfavorable Amount Favorabia/Unfavorable Material Price Variance Material Quantity Variance Material Price Variance Material Quantity Variance Cookies & Cream Toppings Cartons Standard Quantity Actual Quantity Standard Price Actual Price Standard Quantity Actual Quantity Standard Price Actual Price AQ X AP AQX SP SQX SP AQ X AP AQX SP SQ XSP rable Amount Favorable/Unfavorable Amount Favorable/Unfavorable Material Price Variance Material Quantity Variance Material Prke Variance Material Quantity Variance Caramel Nut Toppings Cartons Standard Quantity Actual Quantity Standard Price Actual Price Standard Quantity Actual Quantity Standard Price Actual Price AQX AP AQ X SP SQX SP AQXAP AQ, SP SQ SP orable Amount Favorable/Unfavorable Amount Favorable/Unfavorable Material Price Variance Material Quantity Variance Material Price Variance Material Quantity Variance 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 8. 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) Caramel Nut 11 Cream Toppings Sugar Strawberry Cheesecake 8 6 2 Cookies & Cream 10 5 1 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 nsists 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 proces also takes 3 minutes to complete Position Right: 0 After: O pt Wrap Text Bring Send Forward Backward Selection Pane Align Grol ir 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 is 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 & Crme $ 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 soles. 4. Move to the "Budgeted IS" tab. Prepare a budgeted income statement for the upcoming year. 5. Move to the "Goal Seek" tab. 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 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, and 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" mer in Excel. It is under the "What It Analysis feature. After you hove run gool seek, you need to copy the number of carton sales showing in the carton sales 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" tob back to 9,000. 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 Focus Shortly after the end of the year, The Cream 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 $104,050.00 Cost of Goods Sold $82,530.00 Gross Margin $21,520.00 Selling Expenses $13,526.50 Administrative Expenses $6,243.00 Net Income $1750.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,800.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 "F" 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 "IF" 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. LE(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 Varianco", 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 varlonces 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 varlances 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, 10 11 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 tobs. 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 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 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) Strawberry Cheesecake Cookies & Cream Caramel Nut Cream Toppings 6 5 Sugar 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) Quarter 1 Quarter 2 Quarter 4 Cream 0.22 0.23 Sugar 0.06 0.07 $ 0.08 0.07 Strawberry Cheesecake Topping 0.31 Cookies & Cream Topping $ 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 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 indicate which flavor it is. The process also takes 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 Shock 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. Quarter 3 $ 0.29 0.26 $ $ $ $ $ $ $ $ $ $ 0.30 0.37 0.18 0.34 0.19 $ $ $ For the purposes of the budget, The Cream Shack is 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 & Crme 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 Light 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 (fx) tool located at the top of your Excel spreadsheet if you need some guidance with thit. Also, remember that you can drag formulas to fili 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 "64" 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" tab. Prepare a budgeted income statement for the upcoming year. 5. Move to the "Goal Seek" tab. 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 seek uses an iterative process to make one cell be a particular value by changing another cell's value, and 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 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 soles showing in the corton sales cell within the "Budget Inputs" tab into the cell on the cell within the "Gool Seek" tab by either 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 1 Shortly after the end of the year, The Cream 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 $104,050.00 Cost of Goods Sold $82,530.00 Gross Margin $21,520.00 Selling Expenses $13,526.50 Administrative Expenses $6,243.00 Net Income $1.750.50 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 Corton Carton Total Materials Cost Cartons Strawberry Cheesecake Cream Toppings Sugar Corton 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 Nu Office Dictation Cream Toppings Sugar Carton 10.50 $ 3.50 $ 2.00 $ 1.00 $ $ VIVO 0.12 0.22 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 ceill You will need to do this by the use of a nested "F" 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 '1 year', 'More than a year', or less than a year' to the number of days contained in selected cells. =LE(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 onditio formatting on the "Home" menu 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 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 Direct Material Variances Hand Ne Price Nie we At MAP 40 nevis ware Maand Malwe Math Actu AD Nu MORAP AKY Inc Mwen Variante Materiale Mware VW Mware at le Variante tard Y Mandarin C OOT NY and Pri Actual Quantity des Price AR. w AQUAP NG AGAP AGA Maria WWE Mean Guitare Mawlane Marianne Vine Ver Maarid Prie Vie Moduly Goose het budget Port Report Sun Bust Budget Input Best of Goods Sold DM Var Budgeted is De Accounting 365 Flex Budget Project Instructions BEFORE YOU BEGIN, READ CLOSELY Start with the template that can also be found in the Blackboard folder titled "Flex Budget Project". Seve this file with your None hit the title Example) Excel Project Joe Smith The first two tobs of the template Indicated with green Highlighting) should serve as your input tabs. These tobe will be filled by typing in the input data found in this direction 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 tobt of your spreadsheet, EVERY light grey cel should other reference another call from the file or should be calculated using a formula which also references the necessary cells. "NO numbers or text can be typed into calls with the exception of your input fubd Part 1 The Cream Shook is a local business that produces homemade ice cream that it sel to its customen ha 16 or 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 on effort to keep costs low. The owner 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, cookies and cream, and caramel rut that sell of a ratio of 40%, 35% and 25% of total soles, respectively. All three flavors require creom, uger, and toppings to produce the final product, but the ratio of how much material is needed differ scrow the flavor The table below summorts the necessary materials to create soch product Quantity (er) Strawberry Cheesecake Creom Toppings Sugar Cookies & Cream 10 5 1 Caramel Nur 11 4 1 6 2 Prices for these ingredients change throughout the course of the year in occordance to the market. For budget moking purposes ownership has decided to use on average of prior year costs. The following foble summarizes the cost for the different ingredients per ounce across the different quarters of the previous year Cost (5/2) Creom 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 5 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 fokes obout 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 is. The process also takes 3 minutes to complete The company also hos various overhead, selling, and administrative costs. To budget for these cort, the company estimates them using different cout drivers. The Cream Shock has determined that overhead should be applied based on 20% of direct labor coat. 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 Gream Shock la predicting unit oled to be 9,000 cartons for the upcoming year. The selling price per 16 oz carton for each of the different product lines in shown below Strawberry Cheesecake Cookies & Creme Coromal Nut Selling Price $ 12.00 $ 11.00 $ 10.50 Focus Tex Forward Backward Pane 1. Enter this information into the finto, Budget Inpur. Also, place your come the box provided in the othod ide of the tob. 2. Move to the Standard Codetab Develop the standard cost for the ice cream regarding direct materials, direct labor and overhead by using your budget Input For carton collations, you will need to vie the AVERAOE fundion. Remember there is a function () Follocated at the top of your Excel spreadsheet you need some guidance with thiAho, remember that you can drog formules to fill similar cells. This may require Macking certain cells references with the formule being wted. You can lock" calls in the formule by highlighting them ord prossing the way 3. Move to "budgeted Coit of Goods Sold" tob. Prepare a budgeted cont of goods told uummary for the projected ice cream solen 4. Move to the Budgeted IS" tab. Prepare a budgeted Income statement for the upcoming voor 5. Move to the Good Soek" tab. Owarihip n tooking at purchasing a new building to her on the produelon capabilita. Uut the pool seek tool to find the number of ke creom corton soles necessary to create operating Income of $10,000 Goal soek vies on erotive process to make one call be o portor volve by changing other cell's volue, and for ourposes of the projed we want operorng tecom to become 10,000 by changing the number of cofon solat. For this to work, your group's spreadsheet must be driven by fun tob and set up to flow correctly Using this fool will give you strong evidence that you are on the right track in the completion of you prodhout You can find goolveek on the "Dato me in Excel is under the 'Who Anelle fosture. After you have in gol, you need to copy the number of corion sales showing the cotton sales cell within the Budget puls" tab Wo the call on the cell within the coffee to buy the copying and pouring of typing the number. The son exception to the notyping rule You thanned to be sure to go back and change the carton las volia on the 'Budger input tobbock - 9,000, you don't do the the rest of your preacher will be wing off of the goalele rumber rather than the budgeted vol, and there of you the figures will not work I Part 2 Shortly after the end of the year, The Croom Shock hos compiled in actual tolerond expense data for the prior year and work to compare to the budget. The owner was very excited about an increase in s but could not understand whyprolin decreased during the year. He Woods to see who are expected and what orvos need improvement Adual dose from the year la presented below bushew did better than Income Statement Soles $104,050.00 Cost of Goods Sold 582.530.00 Oro Margin 321,320,00 Seling Experies $13,526,50 Administrative Expenses $6,243.00 Nel income 31750.50 Sales Unit Soler Dollar Sales Strawberry Cheesecake 4,000 $46,000.00 Cookies & Cream 3,000 $32,250,00 Carane Nur 2.400 $25,800.00 Material Quantity per 16 o Corton Cost per 16 Cart Cost of Goods Sold Total Materials Cortona Cout Strawberry Cheesecake Cream Toppings 7.00 5 6,505 1.82 2:47 Materials Quantity per 16 on Carton Cost of Goods Sold Cost per 1 or Total Materials Carton Carton Cout Strawberry Cheesecake Cream Toppings Sugar Carton 7.00 $ 6.50 $ 2.50 $ 1.00 $ 5 1.82 2.47 0.15 0.22 4.88 4,000 $ 18,640.00 Cookies & Cream Creem 11.00 $ 2.86 Toppings Sugar Corton 4.50 $ 0.50 $ 1.00 5 3 0.81 0.03 0.22 3.92 3,000 $ 11,760.00 Caramel Nut $ $ Cream Toppings Sugar Corton 10.50 3.50 2.00 1.00 $ 2.73 0.83 0.12 0.22 370 $ 13 2.400 B.880.00 39,280.00 Totol Materials Cost Labor Hours 2,000 Labor Coul $35,000.00 6. Enter this actual sales and cost information into the second tab, "Actual pur 7. Move to the "Flexible Budget Pref. Report" tab. Prepare a flexible budget performance report for the prior your to find octivity variances and revenue and spending variancet. Make sure to lobel your variances "Favorable "Unfavorable", or "No Vorlance? Remember that you can'type the directly in the call you will need to do this by the wie of a nested statement Simple stotements can be created using the function (fa) tool at the top of the page, but you will need to create your nested statement by hand by typing in your desired Niles at the top of the page in the formula bar roh next to the function (x) button. The following is an example of on "Yo stotement that aulom a category of year More than a year, or "Less than a yeo/' to the number of days contained in selected cele A365, 1 yearf (A1>365,"More than a year, less than a year Once you have the variances labeled or "Fovorable", "Unfavorable", or "No Variance, you will need to color code thone calls in order to make them more noticeable and coser to analyse. You can color code them by ving conditional formatting. You can find conditional formatting on the "Home" menu in Excel You will want to telect all of your cells containing the text "FavorableUnfavorable", or "No Varlonce your spreadsheet. Then you will want to go to the "Highlight Calle Rules drop down choose the option of Equal To.. You will then need to select to Noh your collequeling Favorable with "Green Fill with Dark Green Texe" and your celli equoling Unfavorable with light Red Fit with Dark Red Text 8. Move to the DMVariancas" tab. Compute the dive material variance for the prior year. The standard price of the materich will require you to use the PAVERAGE" function. Agory you will need to use your function () fool at the top of the page. You will need to use " Potener and Conditional Highlighting at you did in the previous tab for the Vorlonces. Follow the some rules for highlighting Remember that when you are calculating the standard quantities you are colculating the STANDARD quantities for ACTUAL production TOR Ddewdi Polie Once you have the variances labeled as "Favorable", "Unfavorable" or "No Vorlonce", 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 "Homo" 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 men 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 Variances" tob. Compute the direct materials verlences 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 of the top of the page. You will also need to use "P" 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 colculating the standard quanilles, you are calculating the STANDARD quarties for ACTUAL production Make sure you fill out all 12 boxen (materials for each flavor of ice cream) before moving on. 9. Move to the "DL Variancel" tob. Compute the direct labor vorlonces for the prior year. You will need to use the same tools and follow the same highlighting and Mandard quantity rules as mentioned for the previous tob. 10. Check your spreadsheet against the check figures thated below to ensure you have completed the spreadsheet correctly 11. Submit your enignment through Blackboard by onewering the questions in Boekhoord ond ontaching and submitting your completed template document via your onlignment link. Do not use email to submit your project. I 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 Sugar Cream Standard Quantity Actual Quantity Standard Price Actual Price Standard Quantity Actual Quantity Standard Price Actual Price AQ X SP SQ x SP AQ X AP AQX SP SQX SP AQX AP Amount Favorable/Unfavorable Amount Favorable/Unfavorable Material Price Variance Material Quantity Variance Material Price Variance Material Quantity Variance Cookies Cream Sugar Standard Quantity Actual Quantity Standard Price Actual Price Standard Quantity Actual Quantity Standard Price Actual Price AQx AP AQX SP SQX SP AQX AP AQ X SP SQ x SP Amount Favorable/Unfavorable Amount Favorable/Unfavorable Material Prke Variance Material Quantity Variance Material Price Variance Material Quantity Variance Caran Cream Sugar Standard Quantity Actual Quantity Standard Price Actual Price Standard Quantity Actual Quantity Standard Price Actual Price AQXAP AQ X SP SQ SP AQ X AP AQ X SP SQ X SP Amount Favorable/Unfavorable Amount Favorable/Unfavorable Material Price Variance Material Quantity Variance Material Price Variance Material Quantity Variance Budaat Innut M N Toppings Cartons Standard Quantity Actual Quantity Standard Price Actual Price Standard Quantity Actual Quantity Standard Price Actual Price AQXAP AQX SP SQXSP AQ X AP AQX SP SQ x SP orable Amount Favorable/Unfavorable Amount Favorabia/Unfavorable Material Price Variance Material Quantity Variance Material Price Variance Material Quantity Variance Cookies & Cream Toppings Cartons Standard Quantity Actual Quantity Standard Price Actual Price Standard Quantity Actual Quantity Standard Price Actual Price AQ X AP AQX SP SQX SP AQ X AP AQX SP SQ XSP rable Amount Favorable/Unfavorable Amount Favorable/Unfavorable Material Price Variance Material Quantity Variance Material Prke Variance Material Quantity Variance Caramel Nut Toppings Cartons Standard Quantity Actual Quantity Standard Price Actual Price Standard Quantity Actual Quantity Standard Price Actual Price AQX AP AQ X SP SQX SP AQXAP AQ, SP SQ SP orable Amount Favorable/Unfavorable Amount Favorable/Unfavorable Material Price Variance Material Quantity Variance Material Price Variance Material Quantity Variance 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 8. 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) Caramel Nut 11 Cream Toppings Sugar Strawberry Cheesecake 8 6 2 Cookies & Cream 10 5 1 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 nsists 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 proces also takes 3 minutes to complete Position Right: 0 After: O pt Wrap Text Bring Send Forward Backward Selection Pane Align Grol ir 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 is 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 & Crme $ 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 soles. 4. Move to the "Budgeted IS" tab. Prepare a budgeted income statement for the upcoming year. 5. Move to the "Goal Seek" tab. 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 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, and 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" mer in Excel. It is under the "What It Analysis feature. After you hove run gool seek, you need to copy the number of carton sales showing in the carton sales 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" tob back to 9,000. 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 Focus Shortly after the end of the year, The Cream 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 $104,050.00 Cost of Goods Sold $82,530.00 Gross Margin $21,520.00 Selling Expenses $13,526.50 Administrative Expenses $6,243.00 Net Income $1750.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,800.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 "F" 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 "IF" 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. LE(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 Varianco", 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 varlonces 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 varlances 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, 10 11 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 tobs. 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 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 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 neede