Accounting 285 Online Excel Spreadsheet Project Summer 2020 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. + DUE TUESDAY, JULY 21st @ 11:59 PM via CANVAS Complete this assignment individually. 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. 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. + 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 ASSETS LIABILITIES & EQUITIES Cash $572,000 Accounts Payable Accounts Receivable $375,000 Loan Payable $700,000 Raw Materials Inventory $197,904 Interest Payable Finished Goods Inventory $197,580 TOTAL LIABILITIES $897,400 PP&E, net $870,000 Retained Earnings $1,315,084 TOTAL EQUITIES $1,315,084 TOTAL ASSETS $2,212,484 TOTAL LIABILITIES & EQUITIES $2,212,484 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). $197.400 $o 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. 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) + 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 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. 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 Your Score Have correct answers that properly calculate & match the following check figures: Cash Collections in July = $1,579,350 2 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. 2 Desired Units of Finished Goods Ending Inventory for the Quarter = 33,000 Good luck. Have fun. I hope you enjoy this project! *****Rubric below***** Raw Material Purchases in September = $547,344 2 Quarter Ending Cash Balance = $762,846 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 entered in the worksheets) 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 Change the selling price to $9.00 and net income should become $1,776,850. 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. 5 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. Total Score 50 JULY AUGUST SEPTEMBER OCTOBER NOVEMBER Sales! Sales in Units (Masks) Selling Price per Mask 222,000 290,000 216,000 165.000 197.000 7.75 ASSETS Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory PPSE, net Balance Sheet As of June 30, 2020 LIABILITIES & EQUITIES $572.000 Accounts Payable 5375.000 Loan Payable $197.904 Interest Payable $197.580 TOTAL LIABILITIES 5870,000 Retained Earnings TOTAL EQUITIES Sa, 212,484 TOTAL LIABILITIES & EQUITIES Collections: Cash Collected in the Month of Sale Cash Collected in Following Month 7096 3046 $197.400 S700,000 SO 897.400 $1,315.084 $1,315.080 32,212,484 TOTAL ASSETS Finished Goods Inventory: Finished Goods Inventory Cost per Unit 20% of next month's unit sales Ending FG Inventory Requirement Beginning TG Inventory, July 1, 2020 44.400 masks Per Unit 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 Direct Material Direct Labor Variable Overhead TOTAL FG INVENTORY COST PER UNIT 0.2 yards of fabric $14.00 per yard 30% of next month's production needs 6096 4046 14.136 yards of fabric 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 Direct Labor: Labor Required per Mask Labor Cost per Hour Sales Budget 0.05 hours 18.00 per hour July 2020 August September TOTAL Manfacturing Overhead: Variable on Fixed OH Noncash Fixed OH (included in above) $ 0.75 per mask 114,000 per month 22,000 per month Unit Sales Selling Price per Unit TOTAL SALES Schedule of Expected Cash Collections Selling & Administrative Expenses! Variable S&A Fixed SRA $ Noncash Fixed S&A (included in above) $ 1.35 per unit sold 65.000 per month 9,000 per month 2020 August September July TOTAL Other: 2 Equipment Purchase Cash from Current Month Cash from Prior Month TOTAL CASH COLLECTIONS 68,000 purchased on July 1, 2020 depreciation is already included in noncash fixed OH above Cash Borrowings: 5 Interest Payment Repayment 5.250 per month until repayment occurs 700,000 at the end of August CI Selling & Administrative Expense Budget October November 2020 Production Budget 2020 July August September TOTAL Sales In Units plus: Desired Units of Ending Inventor total Unit Requirements Less: Units In Beginning Inventory PRODUCTION IN UNITS July August September TOTAL Unit Sales Variable S&A per Unit Total Variable S&A Fixed S&A TOTAL S&A EXPENSE Noncash S&A Expenses TOTAL S&A DISBURSEMENTS 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 Note: The amounts for October & November are necessary in order to complete September on this budget and for the Raw Materials Budget Cash Budget October July 2020 August September TOTAL Raw Materials Purchases Budget 2020 July August September TOTAL Units Produced Yards of RM Required Per Unit of FG Total Yards Used in Production Plus: Destred Yards in Ending Inventory Total Yards Requtred Less Yards in Beginning Inventory RAW MATERIALS PURCHASES (YARDS Cost per Yard RAW MATERIALS PURCHASES (COST) Direct Labor Budget Beginning Cash Balance Cash Collections Available Cash Balance Cash Disbursements: Direct Material Prior Direct Material Current Direct Labor Overhead S&A Equipment Purchase Interest Loan Repayment Total Cash Disbursements ENDING CASH BALANCE 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 TOTAL LIABILITIES & EQUITIES ASSETS Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory PPRE, net TOTAL TOTAL ASSETS 2020 July August September 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 Units Produced Variable Overhead Rate per Unit Total Variable Overhead Cost Fixed Overhead TOTAL OVERHEAD COST Noncash Overhead Expenses TOTAL TOTAL OVERHEAD DISRURSEVENTS