Objective: The purpose of this assignment is to let you see the complexity of budgeting and to develop your spreadsheet skills. Case: Campanile Caf is a small, local business that sells hot coffee and cold brew. The information below pertains to the company's budgeting process for the upcoming year, 2022 Budgeting Info: All of this information is already placed into the INPUTS and Beginning Balance Sheet tabs from which you should work. . Campanile Caf only sells two products: a 12 ounce cup of hot coffee and a 12 ounce cup of cold brew. Each cup is referred to as a finished good unit. It is important to remember that a cup of hot coffee and a cup of cold brew require different amounts of coffee grounds to yield a finished good unit. Although the company makes and sells two different types of products, each cup of hot coffee and cold brew will be treated the same excluding selling price. Each cup of hot coffee sells for $3.00. Each cup of cold brew sells for $4.50. Budgeted sales in units are as follows: 2022 Projected) 2023 Pertad Quarter 1 Quarte 2 Quarter 3 Date & Orte 2 Sales Cups of Hot Corleo 9000 7200 5300 8100 9450 7050 Cups of Cold Brew 1950 8100 7200 6300 5400 9000 Campanile Caf does quite a bit of catering business for groups hosting events on campus. Therefore, they don't collect cash for all of their sales immediately. 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 Due to the company's product lines, there are no units of finished goods inventory (cups of hot coffee and cold brew) on hand at the end of each quarter. Three ounces of coffee beans are required for each cup produced. The company desires to have raw materials on hand at the end of each quarter equal to 30% of the following quarter's production needs. On December 31, 2021, the company has 12,555 ounces of coffee beans on hand The coffee beans used to produce the coffee costs $0.04 per ounce. The company's payment pattern for their coffee grounds is: 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 cup of coffee requires 0.06 hours (3.6 minutes) of labor time to make and the hourly employees are paid $15.00 per hour. Wages are paid in the quarter incurred. Variable manufacturing overhead is $0.70 per cup. . Fixed manufacturing overhead is $10,000 per quarter including $600 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.85 per cup sold Fixed selling and administrative expense is $2,000 per quarter including $400 in depreciation that is not a current cash outflow. All cash disbursements for selling and administrative costs are paid in the quarter incurred. . Campanile Caf likes to keep a minimum cash balance on hand of $10,000. Campanile Caf had $20,000 borrowed as of December 31, 2021. The business repays the full $20,000 note plus $2,000 of interest on January 1, 2022 . Campanile Caf plans to purchase a few new pieces of equipment in 2022. Once piece will be purchased in March 2022 for $2,600, and the other piece will be purchased in April 2022 for $1,800. Depreciation for these is already built into the non-cash expenses provided. The company uses variable costing. . The company shows the following beginning balance sheet as of December 31, 2021: The company uses variable costing. The company shows the following beginning balance sheet as of December 31, 2021: Individual Excel Spreadsheet Project Campanile Cale Balance Sheet Balance Member 31, 2021 Assets Labarties and Equites Cash $ 60.000 Accounts Payable 180 Accounts Receivable 14.300 Notes Payable $20.000 Raw Material vertory 502 Prostate S2000 Finished Goods hvertory Common Stock $ 30 000 Proper part and Equipmentre 3.88.000 Retired Eamines 5.80622 Total Assets 162802 Total Liabilities and Equities 51622302 Requirements: 1. Enter your name in the NAME tab. 2. Prepare a master budget for the year ended December 31, 2022 that includes the following: Projected Sales Budget, Expected Cash Collections Budget, Production Budget, Raw Materials Budget, Direct Labor Budget, Manufacturing Overhead Budget, Selling & Administrative Budget, Cash Budget, Budgeted Income Statement, & Budgeted Balance Sheet (a total of 10 schedules). Each schedule should be completed as it appears in your template. There is a template provided to you in the assignments link in the Excel Project folder with an INPUTS tab you MUST use. Complete the yellow shaded areas of the template only. All of your spreadsheets must be formula driven! This means that EVERY cell should contain either a value referenced from the input worksheet or a formula manually entered into the cell using the referenced values or the input values. You should NOT type a number the entire time you are working on this project. Any typed numbers will cost you 2 points per instance, There is a purpose to this requirement that we hope 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. 3. Complete the Goal Seek tab. Campanile Caf would like to make a charitable contribution of $50,000 at useful as your spreadsheets become more complex 3. Complete the Goal Seek tab. Campanile Caf would like to make a charitable contribution of $50,000 at the end of September (Quarter 3) to help support individuals in Central lowa who have been impacted by Breast Cancer. However, the company must maintain a minimum cash balance of $10,000. They think that customers may be willing to pay a small premium for their cold brew if it is known that a portion of the cost will be applied toward this cause. Use Goal Seek to see what the selling price of cold brew would need to be to end September (Quarter 3) with an ending cash balance of $60,000 ($50,000 that will be donated the $10,000 minimum ending cash balance) HINT: To find goal seek... USING MS OFFICE 365: click on the Data tab, and within the Forecast section click on What-If and find Goal Seek USING PRIOR VERSIONS OF MS OFFICE: click on the Data tab, and within the Data Tools section click on the What-If Analysis and find Goal Seek When you type your info into the template that pops up, you want to do the following: Set cell: click on the ending cash balance cell for the 3quarter in the cash budget To value: $60,000 By changing cell: click on the $4.50 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 driven everything correctly, you should be able to click back to the inputs tab and see the new sales price listed as $4.71 (answer is provided in the rubric at the end of this document as a check figure) Manually change the cold brew sales price back to $4.50 on the inputs tab and then click over to the Goal Seek tab and manually enter your answer in the cell provided. This is the only cell in the project in which it is allowed that you manually. type a number into the cell 4. Compare your file against the project rubrio below You should be able to figure out your grade before you even submit your project by comparing yourself against the rubric. waruny Crangu ulu CUIU Drew Sales Price back to $4.50 on the inputs tab and then click over to the Goal Seek tab and manually enter your answer in the cell provided. This is the only cell in the project in which it is allowed that you manually. type a number into the cell. 4. Compare your file against the project rubric below. You should be able to figure out your grade before you even submit your project by comparing yourself against the rubric 5. Upload your completed Excel file to Canvas via the project link to submit . You may only submit once so make sure this is your final version. Have fun and good luck! I hope you enjoy this project! *****Rubric below***** Evaluation Rubric that will be used to assess your project Possia Your Score Have correct answers that properly calculate & match the following check figures: Cash collections in the 1" quarter 556.183.75 Raw material purchases in the 4 quarter - 51,744.20 Product Cost per unit$1.72 Ending cash balance for the year 2022 = $60,364.02 Net income for the year 2022 = $16.399.50 Total assets at the end of the year 2022 = $157,196.12 Goal seek seling price per unit is $4.79 10 Al cells are formula driven you lose 2 points noch normisa pombered in the worst Your worksheet passes the following tests of your formulas. Be sure that you only make one change at a time and then change back to the original value on the inputs tab before moving on to the next test). On your input tab I will: Change the selling price of hot coffee to $4.00 and set income should become $46.999.50 Change cash collections to 50% in quarter of sale and 50% in next quarter and the ending cash balance for the year on the cash budget is $41.936.52 Change the number of cups of cold brow sold in the 4 quarter to 9,000 and the balance sheet should balance with both Total Assets and Total Liabilities & Equity equaling $162.429.80 Total Score 40 Objective: The purpose of this assignment is to let you see the complexity of budgeting and to develop your spreadsheet skills. Case: Campanile Caf is a small, local business that sells hot coffee and cold brew. The information below pertains to the company's budgeting process for the upcoming year, 2022 Budgeting Info: All of this information is already placed into the INPUTS and Beginning Balance Sheet tabs from which you should work. . Campanile Caf only sells two products: a 12 ounce cup of hot coffee and a 12 ounce cup of cold brew. Each cup is referred to as a finished good unit. It is important to remember that a cup of hot coffee and a cup of cold brew require different amounts of coffee grounds to yield a finished good unit. Although the company makes and sells two different types of products, each cup of hot coffee and cold brew will be treated the same excluding selling price. Each cup of hot coffee sells for $3.00. Each cup of cold brew sells for $4.50. Budgeted sales in units are as follows: 2022 Projected) 2023 Pertad Quarter 1 Quarte 2 Quarter 3 Date & Orte 2 Sales Cups of Hot Corleo 9000 7200 5300 8100 9450 7050 Cups of Cold Brew 1950 8100 7200 6300 5400 9000 Campanile Caf does quite a bit of catering business for groups hosting events on campus. Therefore, they don't collect cash for all of their sales immediately. 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 Due to the company's product lines, there are no units of finished goods inventory (cups of hot coffee and cold brew) on hand at the end of each quarter. Three ounces of coffee beans are required for each cup produced. The company desires to have raw materials on hand at the end of each quarter equal to 30% of the following quarter's production needs. On December 31, 2021, the company has 12,555 ounces of coffee beans on hand The coffee beans used to produce the coffee costs $0.04 per ounce. The company's payment pattern for their coffee grounds is: 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 cup of coffee requires 0.06 hours (3.6 minutes) of labor time to make and the hourly employees are paid $15.00 per hour. Wages are paid in the quarter incurred. Variable manufacturing overhead is $0.70 per cup. . Fixed manufacturing overhead is $10,000 per quarter including $600 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.85 per cup sold Fixed selling and administrative expense is $2,000 per quarter including $400 in depreciation that is not a current cash outflow. All cash disbursements for selling and administrative costs are paid in the quarter incurred. . Campanile Caf likes to keep a minimum cash balance on hand of $10,000. Campanile Caf had $20,000 borrowed as of December 31, 2021. The business repays the full $20,000 note plus $2,000 of interest on January 1, 2022 . Campanile Caf plans to purchase a few new pieces of equipment in 2022. Once piece will be purchased in March 2022 for $2,600, and the other piece will be purchased in April 2022 for $1,800. Depreciation for these is already built into the non-cash expenses provided. The company uses variable costing. . The company shows the following beginning balance sheet as of December 31, 2021: The company uses variable costing. The company shows the following beginning balance sheet as of December 31, 2021: Individual Excel Spreadsheet Project Campanile Cale Balance Sheet Balance Member 31, 2021 Assets Labarties and Equites Cash $ 60.000 Accounts Payable 180 Accounts Receivable 14.300 Notes Payable $20.000 Raw Material vertory 502 Prostate S2000 Finished Goods hvertory Common Stock $ 30 000 Proper part and Equipmentre 3.88.000 Retired Eamines 5.80622 Total Assets 162802 Total Liabilities and Equities 51622302 Requirements: 1. Enter your name in the NAME tab. 2. Prepare a master budget for the year ended December 31, 2022 that includes the following: Projected Sales Budget, Expected Cash Collections Budget, Production Budget, Raw Materials Budget, Direct Labor Budget, Manufacturing Overhead Budget, Selling & Administrative Budget, Cash Budget, Budgeted Income Statement, & Budgeted Balance Sheet (a total of 10 schedules). Each schedule should be completed as it appears in your template. There is a template provided to you in the assignments link in the Excel Project folder with an INPUTS tab you MUST use. Complete the yellow shaded areas of the template only. All of your spreadsheets must be formula driven! This means that EVERY cell should contain either a value referenced from the input worksheet or a formula manually entered into the cell using the referenced values or the input values. You should NOT type a number the entire time you are working on this project. Any typed numbers will cost you 2 points per instance, There is a purpose to this requirement that we hope 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. 3. Complete the Goal Seek tab. Campanile Caf would like to make a charitable contribution of $50,000 at useful as your spreadsheets become more complex 3. Complete the Goal Seek tab. Campanile Caf would like to make a charitable contribution of $50,000 at the end of September (Quarter 3) to help support individuals in Central lowa who have been impacted by Breast Cancer. However, the company must maintain a minimum cash balance of $10,000. They think that customers may be willing to pay a small premium for their cold brew if it is known that a portion of the cost will be applied toward this cause. Use Goal Seek to see what the selling price of cold brew would need to be to end September (Quarter 3) with an ending cash balance of $60,000 ($50,000 that will be donated the $10,000 minimum ending cash balance) HINT: To find goal seek... USING MS OFFICE 365: click on the Data tab, and within the Forecast section click on What-If and find Goal Seek USING PRIOR VERSIONS OF MS OFFICE: click on the Data tab, and within the Data Tools section click on the What-If Analysis and find Goal Seek When you type your info into the template that pops up, you want to do the following: Set cell: click on the ending cash balance cell for the 3quarter in the cash budget To value: $60,000 By changing cell: click on the $4.50 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 driven everything correctly, you should be able to click back to the inputs tab and see the new sales price listed as $4.71 (answer is provided in the rubric at the end of this document as a check figure) Manually change the cold brew sales price back to $4.50 on the inputs tab and then click over to the Goal Seek tab and manually enter your answer in the cell provided. This is the only cell in the project in which it is allowed that you manually. type a number into the cell 4. Compare your file against the project rubrio below You should be able to figure out your grade before you even submit your project by comparing yourself against the rubric. waruny Crangu ulu CUIU Drew Sales Price back to $4.50 on the inputs tab and then click over to the Goal Seek tab and manually enter your answer in the cell provided. This is the only cell in the project in which it is allowed that you manually. type a number into the cell. 4. Compare your file against the project rubric below. You should be able to figure out your grade before you even submit your project by comparing yourself against the rubric 5. Upload your completed Excel file to Canvas via the project link to submit . You may only submit once so make sure this is your final version. Have fun and good luck! I hope you enjoy this project! *****Rubric below***** Evaluation Rubric that will be used to assess your project Possia Your Score Have correct answers that properly calculate & match the following check figures: Cash collections in the 1" quarter 556.183.75 Raw material purchases in the 4 quarter - 51,744.20 Product Cost per unit$1.72 Ending cash balance for the year 2022 = $60,364.02 Net income for the year 2022 = $16.399.50 Total assets at the end of the year 2022 = $157,196.12 Goal seek seling price per unit is $4.79 10 Al cells are formula driven you lose 2 points noch normisa pombered in the worst Your worksheet passes the following tests of your formulas. Be sure that you only make one change at a time and then change back to the original value on the inputs tab before moving on to the next test). On your input tab I will: Change the selling price of hot coffee to $4.00 and set income should become $46.999.50 Change cash collections to 50% in quarter of sale and 50% in next quarter and the ending cash balance for the year on the cash budget is $41.936.52 Change the number of cups of cold brow sold in the 4 quarter to 9,000 and the balance sheet should balance with both Total Assets and Total Liabilities & Equity equaling $162.429.80 Total Score 40