This is the only cell in the project in which it is allowed that you manually type a number into the cell. Accounting 285 Online Excel Spreadsheet Project Summer 2020 Evaluation Rubric that will be used to assess your project 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. Possible Have correct answers that properly calculate & match the following check figures: Your Score 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. Cash Collections in July = $1,579,350 Desired Units of Finished Goods Ending Inventory for the Quarter = 33,000 DUE TUESDAY, JULY 21st @ 11:59 PM via CANVAS Complete this assignment individually. Objective: The purpose of this assignment is to let you see the complexity of budgeting and to develop your spreadsheet skills. Good luck. Have fun. I hope you enjoy this project! *****Rubric below***** Raw Material Purchases September = 5547,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 + 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 5114,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 ASSETS LIABILITIES & EQUITIES 5572,000 Accounts Payable Accounts Receivable Loan Payable Raw Materials Inventory Interest Payable Finished Goods Inventory TOTAL LIABILITIES Retained Eamings TOTAL EQUITIES $1,315.084 TOTAL ASSETS $2,212,484 TOTAL LIABILITIES & EQUITIES saa12.484 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 5500,000. They think that customers may 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 (5750,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 Goal Seek Selling Price per Unit is $8.48 All cells are formula driven (you lose 2 points each time there is a number entered in the worksheets) Case: Winnie's Wearable Masks is a manufacturing company that shifted away from their normal clothing manufacturing business to make and sell protective masks r 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. 20 points 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: 5 5700.000 5375.000 5197.904 5197.5 5870,000 $897.400 51.315.08 PPE, net Change the selling price to $9.00 and net income should become 51,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 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) Requirements 1. Enter your name at the top of the INPUTS tab. Total Score 50 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 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. NOVEMBER Selling & Administrative Expense Budget Sales: Sales in Units (Masks) Selling Price per Mask JULY 222,000 AUGUST SEPTEMBER OCTOBER 290.000 216,000 165,000 197.000 October November $ 7.75 2020 August September July Production Budget 2020 July August September TOTAL Sales in Units Plus: Destred Units of Ending Inventory Total Unit Requirements Less: Units in Beginning Inventory PRODUCTION IN UNITS ASSETS Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory PPSE, net TOTAL Winnie's Wearable Masks Balance Sheet As of June 30, 2020 LIABILITIES & EQUITIES 5572,000 Accounts Payable 5375.000 Loan Payable S102.004 Interest Payable $197.58 TOTAL LIABILITIES S70,000 Retained Earnings TOTAL EQUITIES saa12,484 TOTAL LIABILITIES & EQUITIES $197.400 $700,000 SO $897.400 $1,315.084 $1,319.08.11 32,212,484 Collections: Cash Collected in the Month of Sale Cash Collected in Following Month 7046 3096 Unit Sales Variable S&A per Unit Total Variable SAA Fixed S&A TOTAL S&A EXPENSE Noncash S&A Expenses TOTAL S&A DISBURSEMENTS TOTAL ASSETS Winnie's Wearable Masks Budgeted Income Statement For the Quarter Ending September 30, 2020 Sales Variable Expenses: Variable Manufacturing Expense Variable S&A Expense Contribution Margin Fixed Expenses: Fixed Manufacturing Expense Fixed S&A Expense Net Operating Income Interest Expense Net Income Finished Goods Inventory Note: The amounts for October & November are necessary in order to complete September on this budget and for the Raw Materials Budget Ending PG Inventory Requirement Beginning TG Inventory, July 1, 2020 20% of next month's unit sales 44.400 masks i Finished Goods Inventory Cost per Unit Per Unit Direct Material Direct Labor Variable Overhead TOTAL FG INVENTORY COST PER UNIT Cash Budget 2020 July August September TOTAL 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 6096 4096 14.136 yards of fabric Direct Labor: Labor Required per Mask Labor Cost per Hour 0.05 hours 18.00 per hour ASSETS Beginning Cash Balance Cash Collections Available Cash Balance Cash Disbursements: Direct Material Prior Direct Material Current Direct Labor Overhead S&A Equipment Purchase 1 Interest Loan Repayment Total Cash Disbursements ENDING CASH BALANCE Cash $ Note: Computing the cost of a unit of inventory will be useful later on when you have to do a budgeted income statement & budgeted balance Sales Budget 2020 July August September TOTAL Unit Sales Selling Price per Unit TOTAL SALES Schedule of Expected Cash Collections 2020 July August September TOTAL Cash from Current Month Cash from Prior Month TOTAL CASH COLLECTIONS 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 EQUITIES Manfacturing Overhead: Variable OH Fixed OH Noncash Fixed OH (included in above) Raw Materials Purchases Budget 2020 July August 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 Lece Yards in Beginning Inventory RAW MATERIALS PURCHASES (YARDS Cost per Yard RAW MATERIALS PURCHASES (COST) Direct Labor Budget 2020 July August September TOTAL Units Produced Labor Hours per Unit TOTAL LABOR HOURS REQUIRED Rate per Labor Hour TOTAL DIRECT LABOR COST Manufacturing Overhead Budget 2020 July August September TOTAL Units Produced Variable Overhead Rate per Unit Total Variable Overhead Cost Fixed Overhead TOTAL OVERHEAD COST Noncash Overhead Expenses TOTAL OVERHEAD DISBURSEMENTS 0.75 per mask 114.000 per month 22,000 per month Accounts Receivable Raw Materials inventory Finished Goods Inventory PPRE, net $ TOTAL ASSETS TOTAL LIABILITIES & EQUITIES Selling & Administrative Expenses Variable S&A $ Fixed SAA Noncash Fixed SRA (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 $