Case : Campanile Caf is a small, local business that sells hot coffee and cold brew. The

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: 

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: o 85% of sales are collected in the quarter of sale o 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: o 90% of the quarter’s purchases are paid for in the quarter of purchase o 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: 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. 60,000$ 180$ 14,300$ 20,000$ 502$ 2,000$ -$ Common Stock 80,000$ 88,000$ 60,622$ 162,802$ 162,802$ Total Liabilities and Equities Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory Property, Plant and Equipment, net Total Assets Accounts Payable Notes Payable Interest Payable Retained Earnings Individual Excel Spreadsheet Project Campanile Café Balance Sheet Balance Sheet as of December 31, 2021 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).

Answer rating: 100% (QA)

3 Projected Sales Budget Quarter 1 15000 units Quarter 2 17000 units Quarter 3 16000 units Quarter 4 ... View the full answer