Name and submit your completed case study file as BudgetModuleLastnameFirstname if two members or BudgetModuleTeam# if working in your team. Before starting, you are encouraged to plan your file set up that will make your worksheet efficient and easy to reuse for the entire case study. Consider inserting additional columns for the past and future transactions that lead to amounts that will appear in the budgeted period, which is the twelve months ending December 31, 2022. Additional columns for these months, outside the budget period can be hidden at the end of the budgeting process when preparing the file for submission. Required: Part 1: Outside of Class - to be prepared individually Step 1: Prepare a tab for the data to be used. See below for all of the data you need. Step 2: Prepare a master budget for Cool Ski Goggles Unlimited for the twelve months ending December 31, 2022. Please ensure that your spreadsheets include the following individual monthly budgets in this order: Sales Budget Merchandise Purchases Budget Selling and Administrative Expenses Budget Budgeted Income Statement Capital Acquisitions Budget Schedule of Cash Collections Schedule of Cash Disbursements You must have completed this before class for this week Step 3: In class - with your team 1. Compare your results above to each other and correct any errors on your individual work. 2. Based on the above prepare a Cash Budget individually. Use the cash invested/borrowed to update any of the above schedules as needed. PART 1 SUBMISSION: After completing the Cash Budget, save your individual file and submit in the individual submission folder. Also save one file as the group submission file and submit through the team submission folder.Required: Part 2 - 20 class - work with your team Step 1: Ensure all feedback from your professor has been taken into account and correct any errors from Part 1. Step 2: With your group, complete a comparative Budgeted Balance Sheet as of December 31, 2022. Step 3: In a separate tab, prepare a Stacked Area chart comparing the change in cash flow (Cash & investments) to the net income monthly for 2022. Use a Chart Data Table as a basis for preparing the chart. Title the graph "Change in Cash Flow compared to Net Income" include major gridlines for both the x and y axis. Use appropriate legends and formats. Answer the following questions on the "Answers" worksheet provided within this file. How many months are we predicting increases in cash? How many months are we predicting to be profitable? Step 4: In a separate tab, prepare a 3-D pie chart showing the breakdown of total operating expenses for the 2022 year excluding Cost of Goods Sold. Use a Chart Data Table as a basis for preparing the chart. Use appropriate titles, legends and formats. Include "%" in the data labels Answer the following questions on the "Answers worksheet provided within this file. What is the largest operating expenses excluding Cost of Goods Sold? PART 2 SUBMISSION: Submit one complete team file by the end of class. Submit under the group submission folder.*If working in groups of 2, have one person submit through the individual submission folder.BUSINESS FACTS & ASSUMPTIONS: Cool Ski Goggles Unlimited Balance Sheet As at December 31, 2021 Assets Current assets: Cash $30,000.00 Prepaid insurance 48,000.00 Accounts receivable 895,252.50 Inventory 228,000.00 $1,201, Fixed assets: Warehouse $1,500,000.00 Less: Accumulated amortization 37,500.00 1.462,500.00 Total Assets $2,663,752.50 Liabilities & Owners' Equity Current liabilities: Interest payable $2,083.33 Accounts payable 465,255.00 Sales commission payable 54,270.00 Bank loan 39,537.21 561, 145.54 Long term liabilities Bank loan 460,462.79 Total liabilities 1,021,608.33 Owners' Equity Capital, ending 1,642, 144.17 Total Liabilities & Owners' Equity $2,663,752.50Actual quantity of sales for November and December 2021 as well as budgeted quantity of sales for Cool Ski Goggles Unlimited (CSGU) for the next thirteen months follow. Clear Vision | Bright Eyes Supreme November 2021 (actual) 4,250 4,750 3,250 December 2021 (actual) 5,000 6,000 4,500 January 2022 6,000 7,000 7,000 February 2022 5,000 6,000 5,500 March 2022 4,000 5,000 4,500 April 2022 1,400 1,800 1,700 May 2022 200 400 300 June 2022 200 400 100 July 2022 200 400 100 August 2022 200 400 100 September 2022 1,200 1.400 1, 100 October 2022 2,000 4,000 1,000 November 2022 4,000 5,000 3,500 December 2022 6,000 7,000 5,500 January 2023 7,000 8,000 1 1,000 a) The selling price per unit for CSGU's 3 types of ski goggles, are as follows: Clear Vision $39.00 . BrightEyes $59.00 Supreme $79.00 The cost per unit for CSGU's 3 types of ski goggles, are as follows: Clear Vision $18.00 . BrightEyes $29.00 Supreme $37.00 CSGU has no influence over the cost of the items it purchases from its suppliers. b) In order not to miss out on sales opportunities, sufficient inventory should be on hand, at the end of each month to supply the sales of the following month. Mrs. Henderson wishes to have 40% of following month's cost of goods sold, on hand in ending inventory. C) All sales are on account with various ski and sporting goods stores across Canada. The business has found that 21 % of a month's sales on account are collected in the month of sale. 51% of a month's sales on account are collected in the month after the month of sale. 27% of sales on account are collected in the second month after the month of sale. The remaining 1% is on average uncollectible each month and written off as a bad debt in the month of sale. The accounts receivable balance at December 31, 2021 reported on the balance sheet reflects that trend.d) Purchases of merchandise for resale are all made on account. Past experience indicates that the payments to suppliers are made 35 % in the month of the purchase, 25% in the following month and 40% in th second following month. The accounts payable balance at December 31, 2021, as reported on the balance sheet reflects that trend. e) Variable expenses include: a. commission on sales. 6% of sales IF total monthly sales are equal to or greater than $ 100,000 4% of sales IF total monthly sales are less than $ 100,000 You are expected to use the Excel "IF" function in your project b. Othervariable expenses are advertising and freight-out, which are incurred at the rate of 8% and 2.5% of all sales, respectively. Variable freight out is paid out in the same month the expense is incurred Variable advertising is paid out in the month following. Note that variable advertising for December 2021 was actually paid in Dec 2021, so do not pay out in January 2022. Payments for the sales commissions are made in the following month. f) With the exception of insurance and amortization, fixed monthly selling and administrative expenses, are incurred and paid in the same month for the amounts that follow. Advertising 15,000 Rent 16,000 Salaries 32,000 Administration 8,300 Utilities 5,700 Amortization - warehouse Amortization - newassets Insurance g) The Hendersons are also planning the following capital asset additions for 2022 Computer Equipment $ 45,000 June 1, 2022 Vehicles $ 80,000 Nov 1, 2022 The Hendersons have been advised by their accountant to amortize both of these new assets at a rate of 30% dedining balance for the year. Allocate the annual depreciation equally over the 12 months. The building is amortized at an annual rate of 5%.(2h) Prepaid insurance on December 31, 2021 represents 12 months of insurance prepaid for 2022. CSGU also expects to prepay the 2023 insurance coverage in December 2022 at a premium of $ 50,000. Bank loan on December 31, 2021 represents a $ 500,000 10 year loan on the warehouse building purchased for $ 1,500,000 December 1,2021. This loan bears interest at 5% and is repayable in monthly payments of blended principal and interest in the amount of $ 5,303.28 beginning January 1, 2022. You are required to prepare and submit a loan amortization table to determine the breakdown of monthly and annual principal and interest. A preprogrammed Excel loan template has been included herein for your use The warehouse building is amortized at a rate of 5% dedining balance. CSGU desires to maintain a minimum cash balance of $ 30,000 at the end of each month. Any projected cash surplus in excess of this amount at the end of each month will be invested to provide an average annual return of 6%. Ignore interest earned before January 1, 2022. The monthly interest revenue is expected to be received paid at the end of the following month. Account for this as interest revenue. When they encounter a shortfall to meet the minimum $ 30,000 monthly cash balance they plan on cashing in or retiring any existing investments at the end of that month to maintain their desired monthly minimum cash balance. Check figures to the original master budget: January 2022 monthly totals: Sales $ 1,200,000 COGS 570,000 Purchases 529,000 Total selling & Admin expenses 299, 164 Net Income 330,836 Total Cash Collections 903,038 Total Cash Payments 622,458 Ending Cash Balance 30, 000 Term deposit investments 280,579 Capital Acquisitions 0 December 31 and fiscal year 2022 totals Sales $ 5,876,400 COGS 2,797,200 Purchases 2,875,200 Total selling & Admin expenses 2,105,847 Net Income 1,027,000 Total Cash Collections 5,726,541 Total Cash Payments 4,807,065 Ending Cash Balance 30,000 Term deposit investments 919,475 Capital Acquisitions 125,000 Total assets 3,847,970Loan Calculator Enter Values Loan Summary Loan Amount Scheduled Payment Annual Interest Rate Scheduled Number of Payments Loan Period in Years Actual Number of Payments. Number of Payments Per Year Total Early Payments ." Start Date of Loan Total Interest.504 .Optional Extra Payments L...... 3..... Lender Name: XXX Pmt Payment Beginning Scheduled Extra Total Ending No Date Balance Payment Payment Payment Principal Interest Balance 1 17 171 1 1 1117 1111 1 1 131 1 1 1 1 171 171n | u | u u | |_ I r I u I I I I I 111360012 Budgeting Case Study Cool Ski goggles Unlimited AN SWER SHEET Part 2. Stacked Area chart comparing the change in cash ow {Cash 8. Investmentsl to the net income monthly for 2022 How many months are we predicting Increases In cash? How many months are we predIctIng to be protable? Part 3. What is the largest operating expense excluding Cost of Goods Sold