1 A c E G H 2 Land O'Lakes Cash CHECK TORALS Ending Cash Balance Ending Cash Balance in Ouarter 4 Working down Total column Difference Provided Check Figure (Mhould be $0) (check for match) begin tabular l 31 32 33 34 hline end tabular 135 486 960 (check for match) Inputs Beg Balance Sheet Salos Colloctions Production Production Cost S8A Cash Income Statement Land O'Lakes The company uses variable costing The company shows the following beginning balance sheet as of December 31, 2023t Case Wo will be focusing on the master budget for Land O'Lakes butter divistion The information below pertains to Land O'Lakes budgeting process for the upcoming year, 2024 Budgeting Info All of this information is already placed into the INPUTS and Beginning Balance Sheet tobs from which you should work Land O'Lakes sells butter in one pound packages which include 4 sticks of butter Each pound of butter is reforred to as a finished good unit Each package of butter sells for $6 10 Budgeted sales in units are as followss Quarter 12024 75,000,000 pounds of butter Quarter 22024 62,000,000 pounds of butter Quarter 32024 50,000,000 pounds of butter Quarter 42024 106,000,000 pounds of butter Quarter 12025 72,000,0000 pounds of butter Quarter 22025 60,000,000 pounds of butter 'Land O'Lakes sells primarily to grocery stores Data shows the company's collection pattern is 85 of sales are collected in the quarter of sale 15 of sales are collected in the quarter following the sale Land O'Lakes strives to have ending Inventory of 4 of the next quarter's butter sales on hand at the end of each quarter On December 31, 2023, the company has 3,000,000 pounds (packages) of butter on hand One quarter of cream is required to produce one pound of butter Land O'Lakes desires to have raw materials on hand at the end of each quarter equal to 3 of the following quarter's production needs On December 31, 2023, the company has 2,234,400 quarts of cream on hand Land O'Lakes contracts with suppliers to purchase the cream used in butter production for $2 75 per quart The company's payment pattern for cream tis 90 of the quarter's purchases are paid for in the quarter of purchase 10 of the quarter's purchases are paid for in the quarter following the purchase Each package of butter requires 0 05 hours ( 3 minutes) of labor time to make and the hourly employees are pald $18 00 per hour Wages are paid in the quarter incurred Variable manufacturing overhead is $0 35 per pound of butter Fixed manufacturing overhead is $20,000,000 per quarter including $14,000,000 in depreciation that is not a current cash outflow All cash disbursements for manufacturing overhead are paid in the quarter incurred Variable selling and administrative expenses are $0 25 per pound of butter Fixed selling and administrative expense is $68,000,000 per quarter including $19,000,000 in depreciation that is not a current cash outflow All cash disbursements for selling and administrative costs are paid in the quarter incurred Land O'Lakes likes to keep a minimum cash balance on hand of $100,000,000 Land O'Lakes had $120,000,000 borrowed as of December 31, 2023 The business repays the full $120,000,000 note plus $4,800,000 of Interest on January 1, 2024 Land O'Lakes is planning for a factory equipment purchase in the fint quarter of 2024 which totals $80,000,000 The company is also planning for a corporate headquarters renovation throughout the third and fourth quarters of 2024 totalling $140,000,000 each quarter Depreclation related to this capital spending is already bullt into the non cash expenses provided The company uses varlable costing Naluation Rubric that will bo used to assoss Excel case 3 t begin tabular c c hline begin tabular l Have correct answors thet properly caleulate match the following check figures end tabular Possible hline Cash collections for 2024 $1,769,310,000 1 hline Raw material purchases for 2024 $805,175,800 1 hline S A cash disbursements for 2024 $269,250,000 1 hline Ending cash balance for 2024 $135,486,960 1 hline Budgeted net income for 2024 $190,050,000 1 hline Total assets (and liabilities equities) at the end 2024 $1,777,897,360 1 hline Goal seok selling price per unit is 57 05 4 hline begin tabular l All colls aro formula driven (you lose 2 points each time there is a number entered in the worksheets) end tabular 10 hline hline begin tabular l Change the row moterial (croam) cest per quart to $2 50 and the budgeted net lincoms for 2024 is $263,300,000 end tabular 5 hline end tabular 2 Complete the Goal Seek tab Land O'Lakes would like to bulld a new production facility in 2025 which would require $300,000,000 of capital However, interest rates are currently high, and they are looking to pay for this projoct without having to borrow the capltal necessary You have been asked to run an analysis that shows how much Land O'Lakes would need to increase their sales price for each package of butter to occommodate the cash necessary for this prolect Rememben Land O'Lakes must maintain a minimum cash balance of $100,000,000 Use Goal Seek to find what the selling price of each package of butter would need to be to end 2024 with an ending cash balance of $400,000,000 ( $300,000,000 that will be used for the new production facility the $100,000,000 minimum ending cash balance) HINT To find goal seek USING MS OFFICE click on the Data tab, and within the Forecast section click on What If and find Goal Seek When you type your info into the template that pops up, you want to do the following Set celli click on the ending cash balance cell for 2024 in the cash budget To value $400,000,000 By changing celli click on the $6 10 sales price cell in the inputs tab Click 'OK' and this function will work through the math within your file to figure out what the sales price would need to be to reach this goal If you have formula drlven everything correctly, you should be ablo to cllick back to the inputs tab and see the new sales price listed as $7 05 Manually change the butter sales price back to $6 10 on the inputs tab and then elick over to the Goal Seek tab and manually enter your answer in the cell provided Ihils the only cell in the prolest in which it is allowed that you menually type a number inte the sells Requirements 1 Prepare a master budget for the year ended December 31,2024 that includes the following Projected Salos Budget, Expected Cash Collections Budget, Production Budget, Variable Product Cost per Unit Calculation, Raw Materials Budget, Direct Laber Budget, Manufacturing Overhead Budget, Salling 2 Administretive Budget, Cash Budget, Budgeted Income Statement, 2 Budgeted Balance Sheet Each budget should be completed as it appears in your template There is a templote provided to you in the the yellow shaded areas of the template only All of your sprect theets must be formula drivenl This means that EVERY cell should contain elther a value referenced from the input worksheet or a formula manually entered into the cell using the referenced volues or the input values You sheuld MOT tyme number the entire time you are yorking on this proinet Any typed numbers will cost you 2 points per instance There is a purpose to this requirement that we hepe you understand The beauty (usefulness) of linking your spreadsheets to an inputs page is when something changes, such as the cost per unit of raw materials, you don't have to go through the entire budget to fix every cell that used that value Instead, when you change it on the inputs tab, It will automatically change every cell which used that value within your entire budget This becomes even more useful as your spreadsheets become more complex Land O'Lakes Goal Seek Gonl seek Selling Price Per Pacage of tuter (will need to type this value in aher running goal seek 8 then replace selling price on inpuss tab back to original value) CHECK TOTALS Selling Price per Package of Butter Provided Check Figure 57 05 (check for makch) y y y y y y y y y y y y y y y y y y y y y y y begin tabular c c hline INPUTS hline hline Sales hline Pounds of Butter 75,000,00062,000,00050,000,000106,000,000 72,000,00060,000,000 hline Soling Price por Pound (4 sticks) 3 hline Collections hline Quarter of Sale 85 hline Duater after Salo hline Anished Goods I inventary hline Ending Fo imemory Feguirement 4 of next quarters sales hline Ending FG inventory Uhis (Decenber 31,2023 ) 3,000,000 Units (1 pound pkg w 4 sticks) hline Raw Materials invertory hline Raw Moterials (oroam) Required per Pound of Butter 1 quart hline Raw Mrerias cost per ouat 2 75 per quart hline Ending PMM Invertay Poguirement 35 of next quarters production needs hline Ending Fim Iirromtory Unts (Docember 31, 2023) begin tabular c c 2,234,400 quants end tabular hline Pad in Ouarter of Purchase hline Pad in followng ovarter 10 1 hline Direct Labor hline hline Lasor cost per hour hline Mandactiring Overiead hline Varabite 0 35 per pound af butter hline Fined $20,000,000 per quarter hline Wonkash Fred (nclubed h above) is 14,000,000 per quarder hline Selling s deminatiative Experises hline 0 25 per pound of butier hline Fand 568,000,000 per quarter hline Wonctash Fixed (neluded in above) is 19,000,000 per quarter hline hline Wnimum Cash Batance 3500,000,000 hline Note Provalin as of December 31,2023 $120,000,000 to be paid January 1,2024 hline I 4 8000 000 to be paid Jaruary 1 2024 hline Other hline hline end tabular 1 A c E G H 2 Land O'Lakes Cash CHECK TORALS Ending Cash Balance Ending Cash Balance in Ouarter 4 Working down Total column Difference Provided Check Figure (Mhould be $0) (check for match) begin tabular l 31 32 33 34 hline end tabular 135 486 960 (check for match) Inputs Beg Balance Sheet Salos Colloctions Production Production Cost S8A Cash Income Statement Land O'Lakes The company uses variable costing The company shows the following beginning balance sheet as of December 31, 2023t Case Wo will be focusing on the master budget for Land O'Lakes butter divistion The information below pertains to Land O'Lakes budgeting process for the upcoming year, 2024 Budgeting Info All of this information is already placed into the INPUTS and Beginning Balance Sheet tobs from which you should work Land O'Lakes sells butter in one pound packages which include 4 sticks of butter Each pound of butter is reforred to as a finished good unit Each package of butter sells for $6 10 Budgeted sales in units are as followss Quarter 12024 75,000,000 pounds of butter Quarter 22024 62,000,000 pounds of butter Quarter 32024 50,000,000 pounds of butter Quarter 42024 106,000,000 pounds of butter Quarter 12025 72,000,0000 pounds of butter Quarter 22025 60,000,000 pounds of butter 'Land O'Lakes sells primarily to grocery stores Data shows the company's collection pattern is 85 of sales are collected in the quarter of sale 15 of sales are collected in the quarter following the sale Land O'Lakes strives to have ending Inventory of 4 of the next quarter's butter sales on hand at the end of each quarter On December 31, 2023, the company has 3,000,000 pounds (packages) of butter on hand One quarter of cream is required to produce one pound of butter Land O'Lakes desires to have raw materials on hand at the end of each quarter equal to 3 of the following quarter's production needs On December 31, 2023, the company has 2,234,400 quarts of cream on hand Land O'Lakes contracts with suppliers to purchase the cream used in butter production for $2 75 per quart The company's payment pattern for cream tis 90 of the quarter's purchases are paid for in the quarter of purchase 10 of the quarter's purchases are paid for in the quarter following the purchase Each package of butter requires 0 05 hours ( 3 minutes) of labor time to make and the hourly employees are pald $18 00 per hour Wages are paid in the quarter incurred Variable manufacturing overhead is $0 35 per pound of butter Fixed manufacturing overhead is $20,000,000 per quarter including $14,000,000 in depreciation that is not a current cash outflow All cash disbursements for manufacturing overhead are paid in the quarter incurred Variable selling and administrative expenses are $0 25 per pound of butter Fixed selling and administrative expense is $68,000,000 per quarter including $19,000,000 in depreciation that is not a current cash outflow All cash disbursements for selling and administrative costs are paid in the quarter incurred Land O'Lakes likes to keep a minimum cash balance on hand of $100,000,000 Land O'Lakes had $120,000,000 borrowed as of December 31, 2023 The business repays the full $120,000,000 note plus $4,800,000 of Interest on January 1, 2024 Land O'Lakes is planning for a factory equipment purchase in the fint quarter of 2024 which totals $80,000,000 The company is also planning for a corporate headquarters renovation throughout the third and fourth quarters of 2024 totalling $140,000,000 each quarter Depreclation related to this capital spending is already bullt into the non cash expenses provided The company uses varlable costing Naluation Rubric that will bo used to assoss Excel case 3 t begin tabular c c hline begin tabular l Have correct answors thet properly caleulate match the following check figures end tabular Possible hline Cash collections for 2024 $1,769,310,000 1 hline Raw material purchases for 2024 $805,175,800 1 hline S A cash disbursements for 2024 $269,250,000 1 hline Ending cash balance for 2024 $135,486,960 1 hline Budgeted net income for 2024 $190,050,000 1 hline Total assets (and liabilities equities) at the end 2024 $1,777,897,360 1 hline Goal seok selling price per unit is 57 05 4 hline begin tabular l All colls aro formula driven (you lose 2 points each time there is a number entered in the worksheets) end tabular 10 hline hline begin tabular l Change the row moterial (croam) cest per quart to $2 50 and the budgeted net lincoms for 2024 is $263,300,000 end tabular 5 hline end tabular 2 Complete the Goal Seek tab Land O'Lakes would like to bulld a new production facility in 2025 which would require $300,000,000 of capital However, interest rates are currently high, and they are looking to pay for this projoct without having to borrow the capltal necessary You have been asked to run an analysis that shows how much Land O'Lakes would need to increase their sales price for each package of butter to occommodate the cash necessary for this prolect Rememben Land O'Lakes must maintain a minimum cash balance of $100,000,000 Use Goal Seek to find what the selling price of each package of butter would need to be to end 2024 with an ending cash balance of $400,000,000 ( $300,000,000 that will be used for the new production facility the $100,000,000 minimum ending cash balance) HINT To find goal seek USING MS OFFICE click on the Data tab, and within the Forecast section click on What If and find Goal Seek When you type your info into the template that pops up, you want to do the following Set celli click on the ending cash balance cell for 2024 in the cash budget To value $400,000,000 By changing celli click on the $6 10 sales price cell in the inputs tab Click 'OK' and this function will work through the math within your file to figure out what the sales price would need to be to reach this goal If you have formula drlven everything correctly, you should be ablo to cllick back to the inputs tab and see the new sales price listed as $7 05 Manually change the butter sales price back to $6 10 on the inputs tab and then elick over to the Goal Seek tab and manually enter your answer in the cell provided Ihils the only cell in the prolest in which it is allowed that you menually type a number inte the sells Requirements 1 Prepare a master budget for the year ended December 31,2024 that includes the following Projected Salos Budget, Expected Cash Collections Budget, Production Budget, Variable Product Cost per Unit Calculation, Raw Materials Budget, Direct Laber Budget, Manufacturing Overhead Budget, Salling 2 Administretive Budget, Cash Budget, Budgeted Income Statement, 2 Budgeted Balance Sheet Each budget should be completed as it appears in your template There is a templote provided to you in the the yellow shaded areas of the template only All of your sprect theets must be formula drivenl This means that EVERY cell should contain elther a value referenced from the input worksheet or a formula manually entered into the cell using the referenced volues or the input values You sheuld MOT tyme number the entire time you are yorking on this proinet Any typed numbers will cost you 2 points per instance There is a purpose to this requirement that we hepe you understand The beauty (usefulness) of linking your spreadsheets to an inputs page is when something changes, such as the cost per unit of raw materials, you don't have to go through the entire budget to fix every cell that used that value Instead, when you change it on the inputs tab, It will automatically change every cell which used that value within your entire budget This becomes even more useful as your spreadsheets become more complex Land O'Lakes Goal Seek Gonl seek Selling Price Per Pacage of tuter (will need to type this value in aher running goal seek 8 then replace selling price on inpuss tab back to original value) CHECK TOTALS Selling Price per Package of Butter Provided Check Figure 57 05 (check for makch) y y y y y y y y y y y y y y y y y y y y y y y begin tabular c c hline INPUTS hline hline Sales hline Pounds of Butter 75,000,00062,000,00050,000,000106,000,000 72,000,00060,000,000 hline Soling Price por Pound (4 sticks) 3 hline Collections hline Quarter of Sale 85 hline Duater after Salo hline Anished Goods I inventary hline Ending Fo imemory Feguirement 4 of next quarters sales hline Ending FG inventory Uhis (Decenber 31,2023 ) 3,000,000 Units (1 pound pkg w 4 sticks) hline Raw Materials invertory hline Raw Moterials (oroam) Required per Pound of Butter 1 quart hline Raw Mrerias cost per ouat 2 75 per quart hline Ending PMM Invertay Poguirement 35 of next quarters production needs hline Ending Fim Iirromtory Unts (Docember 31, 2023) begin tabular c c 2,234,400 quants end tabular hline Pad in Ouarter of Purchase hline Pad in followng ovarter 10 1 hline Direct Labor hline hline Lasor cost per hour hline Mandactiring Overiead hline Varabite 0 35 per pound af butter hline Fined $20,000,000 per quarter hline Wonkash Fred (nclubed h above) is 14,000,000 per quarder hline Selling s deminatiative Experises hline 0 25 per pound of butier hline Fand 568,000,000 per quarter hline Wonctash Fixed (neluded in above) is 19,000,000 per quarter hline hline Wnimum Cash Batance 3500,000,000 hline Note Provalin as of December 31,2023 $120,000,000 to be paid January 1,2024 hline I 4 8000 000 to be paid Jaruary 1 2024 hline Other hline hline end tabular

Question

1 A c E G H 2 Land O'Lakes Cash CHECK TORALS Ending Cash Balance Ending Cash Balance in Ouarter 4 Working down Total column Difference Provided Check Figure  (Mhould be  $0) (check for match)  begin tabular  l  31    32    33    34     hline  end tabular  135 486  960 (check for match) Inputs Beg Balance Sheet Salos    Colloctions Production Production Cost S8A Cash  Income Statement Land O'Lakes   The company uses variable costing    The company shows the following beginning balance sheet as of December 31, 2023t Case  Wo will be focusing on the master budget for Land O'Lakes butter divistion  The information below pertains to Land O'Lakes budgeting process for the upcoming year, 2024  Budgeting Info  All of this information is already placed into the INPUTS and Beginning Balance Sheet tobs from which you should work    Land O'Lakes sells butter in one pound packages which include 4 sticks of butter  Each pound of butter is reforred to as a finished good unit  Each package of butter sells for  $6 10    Budgeted sales in units are as followss   Quarter 12024 75,000,000 pounds of butter   Quarter 22024 62,000,000 pounds of butter   Quarter 32024 50,000,000 pounds of butter   Quarter 42024 106,000,000 pounds of butter   Quarter 12025 72,000,0000 pounds of butter   Quarter 22025 60,000,000 pounds of butter   'Land O'Lakes sells primarily to grocery stores  Data shows the company's collection pattern is   85  of sales are collected in the quarter of sale    15  of sales are collected in the quarter following the sale   Land O'Lakes strives to have ending Inventory of 4  of the next quarter's butter sales on hand at the end of each quarter  On December 31, 2023, the company has 3,000,000 pounds (packages) of butter on hand    One quarter of cream is required to produce one pound of butter  Land O'Lakes desires to have raw materials on hand at the end of each quarter equal to 3  of the following quarter's production needs  On December 31, 2023, the company has 2,234,400 quarts of cream on hand    Land O'Lakes contracts with suppliers to purchase the cream used in butter production for $2 75 per quart  The company's payment pattern for cream tis   90  of the quarter's purchases are paid for in the quarter of purchase   10  of the quarter's purchases are paid for in the quarter following the purchase   Each package of butter requires 0 05 hours ( 3 minutes) of labor time to make and the hourly employees are pald $18 00 per hour  Wages are paid in the quarter incurred    Variable manufacturing overhead is $0 35 per pound of butter  Fixed manufacturing overhead is $20,000,000 per quarter including $14,000,000 in depreciation that is not a current cash outflow  All cash disbursements for manufacturing overhead are paid in the quarter incurred    Variable selling and administrative expenses are $0 25 per pound of butter  Fixed selling and administrative expense is $68,000,000 per quarter including $19,000,000 in depreciation that is not a current cash outflow  All cash disbursements for selling and administrative costs are paid in the quarter incurred    Land O'Lakes likes to keep a minimum cash balance on hand of $100,000,000    Land O'Lakes had $120,000,000 borrowed as of December 31, 2023  The business repays the full $120,000,000 note plus $4,800,000 of Interest on January 1, 2024    Land O'Lakes is planning for a factory equipment purchase in the fint quarter of 2024 which totals $80,000,000  The company is also planning for a corporate headquarters renovation throughout the third and fourth quarters of 2024 totalling $140,000,000 each quarter  Depreclation related to this capital spending is already bullt into the non cash expenses provided    The company uses varlable costing   Naluation Rubric that will bo used to assoss Excel case   3 t   begin tabular   c c    hline  begin tabular  l  Have correct answors thet properly caleulate    match the following check    figures   end tabular    Possible     hline Cash collections for 2024 $1,769,310,000   1     hline Raw material purchases for 2024 $805,175,800   1     hline S  A cash disbursements for 2024 $269,250,000   1     hline Ending cash balance for 2024 $135,486,960   1     hline Budgeted net income for 2024 $190,050,000   1     hline Total assets (and liabilities    equities) at the end 2024 $1,777,897,360   1     hline Goal seok selling price per unit is 57 05   4     hline  begin tabular  l  All colls aro formula driven (you lose 2 points each time there is a number    entered in the worksheets)  end tabular    10     hline       hline  begin tabular  l  Change the row moterial (croam) cest per quart to $2 50 and the budgeted    net lincoms for 2024 is $263,300,000   end tabular    5     hline  end tabular  2  Complete the Goal Seek tab  Land O'Lakes would like to bulld a new production facility in 2025 which would require $300,000,000 of capital  However, interest rates are currently high, and they are looking to pay for this projoct without having to borrow the capltal necessary  You have been asked to run an analysis that shows how much Land O'Lakes would need to increase their sales price for each package of butter to occommodate the cash necessary for this prolect  Rememben Land O'Lakes must maintain a minimum cash balance of $100,000,000  Use Goal Seek to find what the selling price of each package of butter would need to be to end 2024 with an ending cash balance of $400,000,000 ( $300,000,000 that will be used for the new production facility   the $100,000,000 minimum ending cash balance)  HINT  To find goal seek      USING MS OFFICE click on the Data tab, and within the Forecast section click on What If and find Goal Seek When you type your info into the template that pops up, you want to do the following    Set celli click on the ending cash balance cell for 2024 in the cash budget   To value  $400,000,000   By changing celli click on the  $6 10 sales price cell in the inputs tab   Click 'OK' and this function will work through the math within your file to figure out what the sales price would need to be to reach this goal  If you have formula drlven everything correctly, you should be ablo to cllick back to the inputs tab and see the new sales price listed as  $7 05  Manually change the butter sales price back to $6 10 on the inputs tab and then elick over to the Goal Seek tab and manually enter your answer in the cell provided  Ihils the only cell in the prolest in which it is allowed that you menually type a number inte the sells  Requirements  1  Prepare a master budget for the year ended December 31,2024 that includes the following  Projected Salos Budget, Expected Cash Collections Budget, Production Budget, Variable Product Cost per Unit Calculation, Raw Materials Budget, Direct Laber Budget, Manufacturing Overhead Budget, Salling 2 Administretive Budget, Cash Budget, Budgeted Income Statement, 2 Budgeted Balance Sheet  Each budget should be completed as it appears in your template  There is a templote provided to you in the the yellow shaded areas of the template only All of your sprect theets must be formula drivenl This means that EVERY cell should contain elther a value referenced from the input worksheet or a formula manually entered into the cell using the referenced volues or the input values  You sheuld MOT tyme number the entire time you are yorking on this proinet  Any typed numbers will cost you 2 points per instance  There is a purpose to this requirement that we hepe you understand  The beauty (usefulness) of linking your spreadsheets to an inputs page is when something changes, such as the cost per unit of raw materials, you don't have to go through the entire budget to fix every cell that used that value  Instead, when you change it on the inputs tab, It will automatically change every cell which used that value within your entire budget  This becomes even more useful as your spreadsheets become more complex  Land O'Lakes Goal Seek Gonl seek Selling Price Per Pacage of tuter (will need to type this value in aher running goal seek 8 then replace selling price on inpuss tab back to original value) CHECK TOTALS Selling Price per Package of Butter Provided Check Figure 57 05 (check for makch) y y y y y y y y y y y y y y y y y y y y y y y  begin tabular   c c    hline   INPUTS     hline       hline Sales       hline Pounds of Butter   75,000,00062,000,00050,000,000106,000,000 72,000,00060,000,000     hline Soling Price por Pound (4 sticks)   3     hline Collections       hline Quarter of Sale   85      hline Duater after Salo        hline Anished Goods I inventary       hline Ending Fo imemory Feguirement   4  of next quarters sales     hline Ending FG inventory Uhis (Decenber 31,2023 )   3,000,000 Units (1 pound   pkg w 4 sticks)     hline Raw Materials invertory       hline Raw Moterials (oroam) Required per Pound of Butter   1 quart     hline Raw Mrerias cost per ouat   2 75 per quart     hline Ending PMM Invertay Poguirement   35 of next quarters production needs     hline Ending Fim Iirromtory Unts (Docember 31, 2023)    begin tabular   c c   2,234,400 quants     end tabular      hline Pad in Ouarter of Purchase       hline Pad in followng ovarter   10 1     hline Direct Labor       hline       hline Lasor cost per hour       hline Mandactiring Overiead       hline Varabite   0 35 per pound af butter     hline Fined   $20,000,000 per quarter     hline Wonkash Fred (nclubed h above)   is 14,000,000 per quarder     hline Selling s deminatiative Experises       hline   0 25 per pound of butier     hline Fand   568,000,000 per quarter     hline Wonctash Fixed (neluded in above)   is 19,000,000 per quarter     hline       hline Wnimum Cash Batance   3500,000,000     hline Note Provalin as of December 31,2023   $120,000,000 to be paid January 1,2024     hline   I 4 8000  000 to be paid Jaruary 1 2024     hline Other       hline       hline  end tabular  1 A c E G H 2 Land O'Lakes Cash CHECK TORALS Ending Cash Balance Ending Cash Balance in Ouarter 4 Working down Total column Difference Provided Check Figure  (Mhould be  $0) (check for match)  begin tabular  l  31    32    33    34     hline  end tabular  135 486  960 (check for match) Inputs Beg Balance Sheet Salos    Colloctions Production Production Cost S8A Cash  Income Statement Land O'Lakes   The company uses variable costing    The company shows the following beginning balance sheet as of December 31, 2023t Case  Wo will be focusing on the master budget for Land O'Lakes butter divistion  The information below pertains to Land O'Lakes budgeting process for the upcoming year, 2024  Budgeting Info  All of this information is already placed into the INPUTS and Beginning Balance Sheet tobs from which you should work    Land O'Lakes sells butter in one pound packages which include 4 sticks of butter  Each pound of butter is reforred to as a finished good unit  Each package of butter sells for  $6 10    Budgeted sales in units are as followss   Quarter 12024 75,000,000 pounds of butter   Quarter 22024 62,000,000 pounds of butter   Quarter 32024 50,000,000 pounds of butter   Quarter 42024 106,000,000 pounds of butter   Quarter 12025 72,000,0000 pounds of butter   Quarter 22025 60,000,000 pounds of butter   'Land O'Lakes sells primarily to grocery stores  Data shows the company's collection pattern is   85  of sales are collected in the quarter of sale    15  of sales are collected in the quarter following the sale   Land O'Lakes strives to have ending Inventory of 4  of the next quarter's butter sales on hand at the end of each quarter  On December 31, 2023, the company has 3,000,000 pounds (packages) of butter on hand    One quarter of cream is required to produce one pound of butter  Land O'Lakes desires to have raw materials on hand at the end of each quarter equal to 3  of the following quarter's production needs  On December 31, 2023, the company has 2,234,400 quarts of cream on hand    Land O'Lakes contracts with suppliers to purchase the cream used in butter production for $2 75 per quart  The company's payment pattern for cream tis   90  of the quarter's purchases are paid for in the quarter of purchase   10  of the quarter's purchases are paid for in the quarter following the purchase   Each package of butter requires 0 05 hours ( 3 minutes) of labor time to make and the hourly employees are pald $18 00 per hour  Wages are paid in the quarter incurred    Variable manufacturing overhead is $0 35 per pound of butter  Fixed manufacturing overhead is $20,000,000 per quarter including $14,000,000 in depreciation that is not a current cash outflow  All cash disbursements for manufacturing overhead are paid in the quarter incurred    Variable selling and administrative expenses are $0 25 per pound of butter  Fixed selling and administrative expense is $68,000,000 per quarter including $19,000,000 in depreciation that is not a current cash outflow  All cash disbursements for selling and administrative costs are paid in the quarter incurred    Land O'Lakes likes to keep a minimum cash balance on hand of $100,000,000    Land O'Lakes had $120,000,000 borrowed as of December 31, 2023  The business repays the full $120,000,000 note plus $4,800,000 of Interest on January 1, 2024    Land O'Lakes is planning for a factory equipment purchase in the fint quarter of 2024 which totals $80,000,000  The company is also planning for a corporate headquarters renovation throughout the third and fourth quarters of 2024 totalling $140,000,000 each quarter  Depreclation related to this capital spending is already bullt into the non cash expenses provided    The company uses varlable costing   Naluation Rubric that will bo used to assoss Excel case   3 t   begin tabular   c c    hline  begin tabular  l  Have correct answors thet properly caleulate    match the following check    figures   end tabular    Possible     hline Cash collections for 2024 $1,769,310,000   1     hline Raw material purchases for 2024 $805,175,800   1     hline S  A cash disbursements for 2024 $269,250,000   1     hline Ending cash balance for 2024 $135,486,960   1     hline Budgeted net income for 2024 $190,050,000   1     hline Total assets (and liabilities    equities) at the end 2024 $1,777,897,360   1     hline Goal seok selling price per unit is 57 05   4     hline  begin tabular  l  All colls aro formula driven (you lose 2 points each time there is a number    entered in the worksheets)  end tabular    10     hline       hline  begin tabular  l  Change the row moterial (croam) cest per quart to $2 50 and the budgeted    net lincoms for 2024 is $263,300,000   end tabular    5     hline  end tabular  2  Complete the Goal Seek tab  Land O'Lakes would like to bulld a new production facility in 2025 which would require $300,000,000 of capital  However, interest rates are currently high, and they are looking to pay for this projoct without having to borrow the capltal necessary  You have been asked to run an analysis that shows how much Land O'Lakes would need to increase their sales price for each package of butter to occommodate the cash necessary for this prolect  Rememben Land O'Lakes must maintain a minimum cash balance of $100,000,000  Use Goal Seek to find what the selling price of each package of butter would need to be to end 2024 with an ending cash balance of $400,000,000 ( $300,000,000 that will be used for the new production facility   the $100,000,000 minimum ending cash balance)  HINT  To find goal seek      USING MS OFFICE click on the Data tab, and within the Forecast section click on What If and find Goal Seek When you type your info into the template that pops up, you want to do the following    Set celli click on the ending cash balance cell for 2024 in the cash budget   To value  $400,000,000   By changing celli click on the  $6 10 sales price cell in the inputs tab   Click 'OK' and this function will work through the math within your file to figure out what the sales price would need to be to reach this goal  If you have formula drlven everything correctly, you should be ablo to cllick back to the inputs tab and see the new sales price listed as  $7 05  Manually change the butter sales price back to $6 10 on the inputs tab and then elick over to the Goal Seek tab and manually enter your answer in the cell provided  Ihils the only cell in the prolest in which it is allowed that you menually type a number inte the sells  Requirements  1  Prepare a master budget for the year ended December 31,2024 that includes the following  Projected Salos Budget, Expected Cash Collections Budget, Production Budget, Variable Product Cost per Unit Calculation, Raw Materials Budget, Direct Laber Budget, Manufacturing Overhead Budget, Salling 2 Administretive Budget, Cash Budget, Budgeted Income Statement, 2 Budgeted Balance Sheet  Each budget should be completed as it appears in your template  There is a templote provided to you in the the yellow shaded areas of the template only All of your sprect theets must be formula drivenl This means that EVERY cell should contain elther a value referenced from the input worksheet or a formula manually entered into the cell using the referenced volues or the input values  You sheuld MOT tyme number the entire time you are yorking on this proinet  Any typed numbers will cost you 2 points per instance  There is a purpose to this requirement that we hepe you understand  The beauty (usefulness) of linking your spreadsheets to an inputs page is when something changes, such as the cost per unit of raw materials, you don't have to go through the entire budget to fix every cell that used that value  Instead, when you change it on the inputs tab, It will automatically change every cell which used that value within your entire budget  This becomes even more useful as your spreadsheets become more complex  Land O'Lakes Goal Seek Gonl seek Selling Price Per Pacage of tuter (will need to type this value in aher running goal seek 8 then replace selling price on inpuss tab back to original value) CHECK TOTALS Selling Price per Package of Butter Provided Check Figure 57 05 (check for makch) y y y y y y y y y y y y y y y y y y y y y y y  begin tabular   c c    hline   INPUTS     hline       hline Sales       hline Pounds of Butter   75,000,00062,000,00050,000,000106,000,000 72,000,00060,000,000     hline Soling Price por Pound (4 sticks)   3     hline Collections       hline Quarter of Sale   85      hline Duater after Salo        hline Anished Goods I inventary       hline Ending Fo imemory Feguirement   4  of next quarters sales     hline Ending FG inventory Uhis (Decenber 31,2023 )   3,000,000 Units (1 pound   pkg w 4 sticks)     hline Raw Materials invertory       hline Raw Moterials (oroam) Required per Pound of Butter   1 quart     hline Raw Mrerias cost per ouat   2 75 per quart     hline Ending PMM Invertay Poguirement   35 of next quarters production needs     hline Ending Fim Iirromtory Unts (Docember 31, 2023)    begin tabular   c c   2,234,400 quants     end tabular      hline Pad in Ouarter of Purchase       hline Pad in followng ovarter   10 1     hline Direct Labor       hline       hline Lasor cost per hour       hline Mandactiring Overiead       hline Varabite   0 35 per pound af butter     hline Fined   $20,000,000 per quarter     hline Wonkash Fred (nclubed h above)   is 14,000,000 per quarder     hline Selling s deminatiative Experises       hline   0 25 per pound of butier     hline Fand   568,000,000 per quarter     hline Wonctash Fixed (neluded in above)   is 19,000,000 per quarter     hline       hline Wnimum Cash Batance   3500,000,000     hline Note Provalin as of December 31,2023   $120,000,000 to be paid January 1,2024     hline   I 4 8000  000 to be paid Jaruary 1 2024     hline Other       hline       hline  end tabular

Accepted Answer

The Answer is in the image, click to view ...

Question

1 A c E G H 2 Land O'Lakes Cash CHECK TORALS Ending Cash Balance Ending Cash Balance in Ouarter 4 Working down Total column

Step by Step Solution

Step: 1

Get Instant Access to Expert-Tailored Solutions

Step: 2

Step: 3

Ace Your Homework with AI

Recommended Textbook for

IT Auditing Using A System Perspective Premier Reference Source

Students also viewed these Accounting questions

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question