Objective: The purpose of this assignment is to let you see the complexity of budgeting and to develop your spreadsheet skills. Case: Winnie's Wearable Masks is a manufacturing company that shifted away from their 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 3rd quarter of 2020 which is expected to be a very busy time for the company. Budgeting Info: All of this information is already placed into the INPUTS & Beg. Balance Sheet tabs for you to work from. + 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 is not a current cash outflow. All cash disbursements for selling and administrative costs are paid in the month incurred. Winnie purchased a new piece of equipment on July 1, 2020. The equipment cost $68,000. + When the company decided to shift their business to mask production some additional working capital was needed to stock the production facility with the appropriate equipment and to ramp up production volume. At this time, Winnie took out a $700,000 loan. Interest payments of $5,250 are due each month until repayment of the capital is made. Interest payments are always made on the last day of each month. Winnie's Wearable Masks uses variable costing. The company shows the following balance sheet as of the end of the 2nd quarter on June 30, 2020: Winnie's Wearable Masks Balance Sheet As of June 30, 2020 LIABILITIES & EQUITIES $572,000 Accounts Payable $375,000 Loan Payable Raw Materials Inventory Interest Payable $197.580 PP&E, net $ 870,000 $1,315.084 ASSETS Cash Accounts Receivable $197.400 $700.000 SO $897.400 S197,904 Finished Goods Inventory TOTAL LIABILITIES Retained Earnings TOTAL EQUITIES TOTAL LIABILITIES & EQUITIES $1,315,0B4 52,212,484 TOTAL ASSETS 52,212,484 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 Excel Project folder with an input page you MUST use. 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 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. 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 To value: $1,250,000 By changing cell: click on the $7.75 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 in replacement of $7.75 (answer is provided in the rubric at the end of this document as a check figure) Manually change the sales price back to $7.75 on the inputs tab and then click over to the Goal Seek tab and 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. Evaluation Rubric that will be used to assess your project Possible Your Score 2 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 Ending Cash Balance = $762,846 Net Income for the Quarter = $866,850 Total Assets at the End of the Quarter = $2,400,872 Goal Seek Selling Price per Unit is $8.48 2 2 2 2 3 20 points All cells are formula driven (you lose 2 points each time there is a number entered in the worksheets) 5 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 to $9.00 and net income should become $1,776,850 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. 5 5 Total Score 50 Winnie's Wearable Masks Projected Budgeting Data Sales: Sales in Units (Masks) Selling Price per Mask JULY 222,000 7.75 AUGUST 290,000 2020 SEPTEMBER 216,000 OCTOBER 165,000 NOVEMBER 197,000 Collections: Cash Collected in the Month of Sale Cash Collected in Following Month 70% 30% Finished Goods Inventory: Ending FG Inventory Requirement Beginning FG Inventory, July 1, 2020 20% of next month's unit sales 44,400 masks Raw Materials Inventory: Raw Materials Required per Mask Raw Materials Cost per Yard Ending RM Inventory Requirement Paid in Month of Purchase Paid in Following Month Beginning RM Inventory, July 1, 2020 0.2 yards of fabric $14.00 per yard 30% of next month's production needs 60% 40% 14,136 yards of fabric Direct Labor: Labor Required per Mask Labor Cost per Hour 0.05 hours 18.00 per hour Manfacturing Overhead: Variable OH Fixed OH Noncash Fixed OH (included in above) $ $ $ 0.75 per mask 114,000 per month 22,000 per month Selling & Administrative Expenses: Variable S&A Fixed S&A Noncash Fixed S&A (included in above) $ $ $ 1.35 per unit sold 65,000 per month 9,000 per month Other: Equipment Purchase $ 68,000 purchased on July 1, 2020 depreciation is already included in noncash fixed OH above Cash Borrowings: Interest Payment Repayment $ 5,250 per month until repayment occurs 700,000 at the end of August $ Objective: The purpose of this assignment is to let you see the complexity of budgeting and to develop your spreadsheet skills. Case: Winnie's Wearable Masks is a manufacturing company that shifted away from their 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 3rd quarter of 2020 which is expected to be a very busy time for the company. Budgeting Info: All of this information is already placed into the INPUTS & Beg. Balance Sheet tabs for you to work from. + 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 is not a current cash outflow. All cash disbursements for selling and administrative costs are paid in the month incurred. Winnie purchased a new piece of equipment on July 1, 2020. The equipment cost $68,000. + When the company decided to shift their business to mask production some additional working capital was needed to stock the production facility with the appropriate equipment and to ramp up production volume. At this time, Winnie took out a $700,000 loan. Interest payments of $5,250 are due each month until repayment of the capital is made. Interest payments are always made on the last day of each month. Winnie's Wearable Masks uses variable costing. The company shows the following balance sheet as of the end of the 2nd quarter on June 30, 2020: Winnie's Wearable Masks Balance Sheet As of June 30, 2020 LIABILITIES & EQUITIES $572,000 Accounts Payable $375,000 Loan Payable Raw Materials Inventory Interest Payable $197.580 PP&E, net $ 870,000 $1,315.084 ASSETS Cash Accounts Receivable $197.400 $700.000 SO $897.400 S197,904 Finished Goods Inventory TOTAL LIABILITIES Retained Earnings TOTAL EQUITIES TOTAL LIABILITIES & EQUITIES $1,315,0B4 52,212,484 TOTAL ASSETS 52,212,484 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 Excel Project folder with an input page you MUST use. 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 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. 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 To value: $1,250,000 By changing cell: click on the $7.75 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 in replacement of $7.75 (answer is provided in the rubric at the end of this document as a check figure) Manually change the sales price back to $7.75 on the inputs tab and then click over to the Goal Seek tab and 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. Evaluation Rubric that will be used to assess your project Possible Your Score 2 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 Ending Cash Balance = $762,846 Net Income for the Quarter = $866,850 Total Assets at the End of the Quarter = $2,400,872 Goal Seek Selling Price per Unit is $8.48 2 2 2 2 3 20 points All cells are formula driven (you lose 2 points each time there is a number entered in the worksheets) 5 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 to $9.00 and net income should become $1,776,850 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. 5 5 Total Score 50 Winnie's Wearable Masks Projected Budgeting Data Sales: Sales in Units (Masks) Selling Price per Mask JULY 222,000 7.75 AUGUST 290,000 2020 SEPTEMBER 216,000 OCTOBER 165,000 NOVEMBER 197,000 Collections: Cash Collected in the Month of Sale Cash Collected in Following Month 70% 30% Finished Goods Inventory: Ending FG Inventory Requirement Beginning FG Inventory, July 1, 2020 20% of next month's unit sales 44,400 masks Raw Materials Inventory: Raw Materials Required per Mask Raw Materials Cost per Yard Ending RM Inventory Requirement Paid in Month of Purchase Paid in Following Month Beginning RM Inventory, July 1, 2020 0.2 yards of fabric $14.00 per yard 30% of next month's production needs 60% 40% 14,136 yards of fabric Direct Labor: Labor Required per Mask Labor Cost per Hour 0.05 hours 18.00 per hour Manfacturing Overhead: Variable OH Fixed OH Noncash Fixed OH (included in above) $ $ $ 0.75 per mask 114,000 per month 22,000 per month Selling & Administrative Expenses: Variable S&A Fixed S&A Noncash Fixed S&A (included in above) $ $ $ 1.35 per unit sold 65,000 per month 9,000 per month Other: Equipment Purchase $ 68,000 purchased on July 1, 2020 depreciation is already included in noncash fixed OH above Cash Borrowings: Interest Payment Repayment $ 5,250 per month until repayment occurs 700,000 at the end of August $