Add Formulas

Complete this assignment individually. Objectives The purpose of this argent is to let you see the complexity of budgeting and to develop your prendre Cases Winnie's Wearable Mess is a manufacturing company that shifted www from the normal clothing manufacturing business to make and sell protective masks for adults and children to help meet the demand created by COVID-19. The information below pertains to the company's budgeting process for the quarter of 2020 which is expected to be a very busy time for the company Budgeting Info: This fe is a placed in INPUTS Maloner Shelf Each mask is referred to as a finished good unit. Although the company makes both adult and children's sizes, each mask will be treated the same Budgeted sales in units are as follows: July - 222,000 masks August - 290,000 masks September - 216,000 masks October - 165,000 masks - November - 197,000 masks Each unit sells for $7.75 The company sells their masks to merchandisers. All sales are on account. The company's collection pattern is - 70 % of sales are collected in the month of sale - 30 % are collected in the month following the sale The company desires to have finished goods inventory (masks) on hand at the end of each month equal to 20% of the following month's budgeted sales in units. On June 30, 2020, the company had 44.400 masks on hand 2 yards of fabric are required for each mask produced. The company desires to have materials on hand at the end of each month equal to 30% of the following month's production needs. On June 30, 2020, the company had 14,136 yards of fabric on hand. The fabric used in production costs $14.00 per yard. The company's payment pattern is: 60% of the month's purchases are paid for in the month of purchase . 30% are paid for in the month following the purchase Each mask requires 3 minutes (0.2 hours) of labor time to make and the hourly employees are paid $18/hour. Wages are paid in the month incurred. Variable manufacturing overhead is $0.75 per mask. Fixed manufacturing overhead is $114,000 per month including $22,000 in depreciation that is not a current cash outflow All cash disbursements for manufacturing overhead are paid in the month incurred. Variable selling and administrative expenses are $1.35 per mask sold. Fixed selling and administrative expense is $65,000 per month including $9,000 in depreciation that s not a current cash outflow. . All cash disbursements for selling and administrative costs are paid in the month incurred. W prof. 2020 The 2000 Winnie Wearable HSRO Balance sheet As of June 30, 2020 A Pay so). Antece Na Mataventory Finished Gedenventory 37.000 8127.204 B. ST. Interest Paya TOTAL LIABILITIES TOTAL COUNTIES YOYAL CARICHES EGUITIES TOTAL ASSETS Requirements 1. Enter your name at the top of the INPUTS tab 2 Prepare a master budget for the quarter ended September 30, 2020 that includes: Finished Goods Inventory Cost per Unit, Sales Budget, Schedule of Expected Cash Collections, Production Budget, Raw Materials Purchases Budget, Direct Labor Budget, Manufacturing Overhead Budget Selling & Administrative Expense Budget, Cash Budget, Budgeted Income Statement, & Budgeted Balance Sheet (a total of 11 schedules). Each schedule should be on a separate worksheet as appears in your template. There is a template provided to you in the assignments link in the Freel Proje folder with an input page you MUST Complete the shaded areas of the template only. All of your spreadsheets must be formula driven from the input tab! 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 out 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 dont 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 fab. Winnie's Wearable Masks would like to make a charitable contribution of $750,000 at the end of September to help support individuals and families who have been negatively impacted by COVID-19. However, the company must maintain a minimum cash balance of $500,000. They think that customers may be willing to pay a small premium for masks 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 would need to be to end September with an ending cash balance of $1,250,000 ($750,000 that will be donated + the $500,000 minimum ending cash balance). HINT: To find goal seek, click on the Data tab and under Data Tools click on the What If Analysis. When you type your info into the template this provides you want to do the following: Set cell: click on the ending cash balance cell for the quarter in the cash budget Manually change the sales priehackt 87.75 hp and then click over to the Goal Seck tab and your aner in the cell provided 4 grade before you even submit your project by comparing yourself in the brie 5 des projeto You may aly subentonces make sure this is your final version Good luck. Have fun. I hope you enjoy this project Rubric belo Evaluation Rubric that will be used to assess your project Possible Your Score 2 Have correct answers that properly calculate & match the following check figures Cash Collections in July = $1,579,350 Desired Units of Finished Goods Ending Inventory for the Quarter - 33,000 Raw Material Purchases in September - $547,344 Quarter Finding Cash Balance - $762,846 2 2 2 Net Income for the Quarter $866,850 2 Total Assets at the end of the Quarter = $2,400,872 2 Goal Seek Selling Price per Unit is $8.48 3 20 points All cells are formula driven you lose 2 points each time there is a number Jenicred in the worksheets Your worksheet passes the following tests of your formulas. Be sure that pou 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 to $9.00 and net income should become $1,776,850. 5 5 Change cash collections to 50% in month of sale and 50% in next month and the ending cash balance for the quarter on the cash budget is $428,046. Change units sold in September to 300,000 and the balance sheet should balance with both Total Assets and Total Liabilities & Equity equaling $2.617,356 B C D E F G Sales: Sales in Units (Masks) Selling Price per Mask JULY 222,000 7.75 AUGUST 290,000 SEPTEMBER 216,000 OCTOBER 165,000 NOVEMBER 197,000 $ 70% 30% 20% of next month's unit sales 44,400 masks 0.2 yards of fabric $14.00 per yard 30% of next month's production needs 60% 40% 14,136 yards of fabric Collections: 2 Cash Collected in the Month of Sale Cash Collected in Following Month 4 5 Finished Goods Inventory: 6 Ending FG Inventory Requirement 7 Beginning FG Inventory, July 1, 2020 8 19 Raw Materials Inventory: 20 Raw Materials Required per Mask 21 Raw Materials Cost per Yard 22 Ending RM Inventory Requirement 23 Paid in Month of Purchase 24 Paid in Following Month 25 Beginning AM Inventory, July 1, 2020 26 27 Direct Labor: 28 Labor Required per Mask 29 Labor Cost per Hour 30 31 Manfacturing Overhead: 32 Variable OH 33 Fixed OH 34 Noncash Fixed OH (included in above) 35 36 Selling & Administrative Expenses: 37 Variable S&A 38 Fixed S&A 39 Noncash Fixed S&A (included in above) 40 41 Other: 42 Equipment Purchase 43 INPUTS Beg. Balance Sheet Ready 0.05 hours 18.00 per hour $ $ $ 0.75 per mask 114,000 per month 22,000 per month $ $ 1.35 per unit sold 65,000 per month 9,000 per month $ 68,000 purchased on July 1, 2020 'depreciation is already included in noncash fixed OH above FG Inventory Cost Per Unit Sales Budget Expected Cash Collections $ $ $ 0.75 per mask 114,000 per month 22,000 per month $ $ $ 1.35 per unit sold 65,000 per month 9,000 per month 68,000 purchased on July 1, 2020 *depreciation is already included in noncash fixed OH above Manfacturing Overhead: 2 Variable OH 3 Fixed OH Noncash Fixed OH (included in above) 6 Selling & Administrative Expenses: 7 Variable S&A 8 Fixed S&A -9 Noncash Fixed S&A (included in above) 10 1 Other: 2 Equipment Purchase 23 14 5 Cash Borrowings: 46 Interest Payment 47 Repayment 48 49 50 51 52 53 54 55 56 57 58 59 60 $ 5,250 per month until repayment occurs 700,000 at the end of August 1 2 3 4 ASSETS s Cash 6 Accounts Receivable 7 Raw Materials Inventory 8 Finished Goods Inventory 9 PP&E, net 10 11 TOTAL ASSETS Winnie's Wearable Masks Balance Sheet As of June 30, 2020 LIABILITIES & EQUITIES $572,000 Accounts Payable $375,000 Loan Payable $197,904 Interest Payable $197.580 TOTAL LIABILITIES $870,000 Retained Earnings TOTAL EQUITIES $2,212,484 TOTAL LIABILITIES & EQUITIES $197,400 $700,000 $0 $897,400 $1,315,084 $1,315,084 $2,212,484 12 13 14 15 16 17 18 Finished Goods Inventory Cost per Unit Per Unit Direct Material Direct Labor Variable Overhead TOTAL FG INVENTORY COST PER UNIT Note: Computing the cost of a unit of inventory will be useful later on when you have to do a budgeted 2 3 4 15 16 17 18 19 20 21 22 23 24 25 26 A B D E Sales Budget July August 2020 September TOTAL Unit Sales Selling Price per Unit S TOTAL SALES 3 8 10 11 12 13 14 15 16 17 18 19 20 Schedule of Expected Cash Collections July August 2020 September TOTAL Cash from Current Month Cash from Prior Month TOTAL CASH COLLECTIONS B D E F G Production Budget July August 2020 September TOTAL October November Sales in Units Plus: Desired Units of Ending Inventory Total Unit Requirements Less: Units in Beginning Inventory PRODUCTION IN UNITS Note: The amounts for October & November are necessary in order to complete September on this budget and for the Raw Materials Budget. Raw Materials Purchases Budget July August 2020 September TOTAL October Units Produced Yards of RM Required Per Unit of FG Total Yards Used in Production - Plus: Desired Yards in Ending Inventory Total Yards Required 9 Less: Yards in Beginning Inventory 10 RAW MATERIALS PURCHASES (YARDS) 11 Cost per Yard 12 RAW MATERIALS PURCHASES (COST) 13 + 15 16 17 18 19 20 21 E F G Direct Labor Budget July 2020 September August TOTAL Units Produced Labor Hours per Unit TOTAL LABOR HOURS REQUIRED Rate per Labor Hour TOTAL DIRECT LABOR COST 8 9 0 21 22 23 24 25 26 27 28 29 30 31 32 33 36 35 INPUTS Beg. Balance Sheet FG Inventory Cost Per Unit Sales Budget Expected Cash Collections Ready MacBook FI F2 80 F3 F4 F5 NB % & 1 2 3 4 5 6 7 Manufacturing Overhead Budget 2 July August 2020 September TOTAL 4 Units Produced 5 Variable Overhead Rate per Unit 6 Total Variable Overhead Cost 7 Foed Overhead TOTAL OVERHEAD COST Noncash Overhead Expenses 10 TOTAL OVERHEAD DISBURSEMENTS 12 13 15 16 17 18 19 20 21 22 23 24 25 26 27 28 23 30 11 32 34 35 Beg. Balance Sheet FG Inventory Cost Per Unit Ready Sales Budget Expected Cash Collections MacBook F1 80 F2 F3 F4 FS F6 # 1 $ A 2 3 % 5 & 4 6 7 D Selling & Administrative Expense Budget July 2020 September August TOTAL Unit Sales Variable S&A per Unit Total Variable S&A Fixed S&A TOTAL S&A EXPENSE s Noncash S&A Expenses 10 TOTAL S&A DISBURSEMENTS 11 12 13 15 16 17 18 19 20 21 22 23 24 25 26 27 28 30 31 32 33 34 35 FG Inventory Cost Per Unit Sales Budget Expected Cash Collections Ready Production Budget RM Bus MacBook F1 000 F2 80 F3 F4 F5 17 $ % A 1 w # 2 & 3 4 5 6 7 Cash Budget July August 2020 September TOTAL Beginning Cash Balance Cash Collections Available Cash Balance Cash Disbursements: Direct Material Prior 9 Direct Material Current 10 Direct Labor 1 Overhead 12 S&A 13 Equipment Purchase 14 Interest 15 Loan Repayment 16 Total Cash Disbursements 17 ENDING CASH BALANCE 18 19 20 21 22 23 24 25 27 28 29 31 33 34 FG Inventory Cost Per Unit Sales Budget Ready Expected Cash Collections Production Budget RM Budge MacBook F1 F2 80 F3 000 000 F4 FS 56 F7 # 1 Ne % A & 3 4 5 6 7 Q 14 1 Winnie's Wearable Masks 2 Budgeted Income Statement 3 For the Quarter Ending September 30, 2020 4 Sales 5 Variable Expenses: 6 Variable Manufacturing Expense 7 Variable S&A Expense 8 Contribution Margin 9 Fixed Expenses: 10 Fixed Manufacturing Expense 11 Fixed S&A Expense 12 Net Operating Income 13 Interest Expense 14 Net Income 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 B C D E ASSETS Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory PP&E, net Winnie's Wearable Masks Budgeted Balance Sheet As of September 30, 2020 LIABILITIES & EQUITIES Accounts Payable Notes Payable Interest Payable TOTAL LIABILITIES Retained Earnings TOTAL EQUMES TOTAL LIABILITIES & EQUITIES TOTAL ASSETS 3 B C D E F H GOAL SEEK Selling Price per Unit: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20