Accounting 285 Excel Spreadsheet Project Spring 2019

Objective: The purpose of this assignment is to let you see the complexity of budgeting and to develop your spreadsheet skills.

Case: ChocoLasting Chocolate Company is a small manufacturing company that manufactures the cocoa powder used by hot chocolate vendors. The information below pertains to the companys budgeting process during their busiest time of year.

Please help me to complete this Excel project.

I've done half of them, now I only need the Expected Cash Collections, Cash Budget, Income Statement, Balance Sheet, and Goal Seek.

Requirements are listed in the pictures. Thanks.

Accounting 285 Excel Spreadsheet Project pring 2019 . Chocelasting purchased a new piece of equipment at the end of December on the 31. The equipment cost $35,700. Chocolasting barrowed $100,000 on Saptember 30t as they normally need a lat of working capital to support the winter sales. Interest payments of $625 are made on the last day of each month. The Company uses variable costing in its budgeted income statement and its balance sheet. DUE TUESDAY, APRIL 9th @ 11:59 PM via CANVAS Additionally, Shecelasting has the following balance sheet as of September 30, 2019 ChocoLasting Chocolate Company Balance Sheet As of September 30, 2019 Complete this assignment individually. Objective: The purpose of this assignment is to lel you see the complexity of budgeting and to dlevelop your spreadsheet skills dsheet skills. LIABILITIES & EQUITIES. Cash $130,000 Accounts Payable Notes Payable $100,000 s0 $128,200 $80,000 5182,550 5262,550 Case: Chocolasting Chocolato Company is a small manufacturing company that manufactures the cocoa powder used by hot chocolate vendlors. The information below pertains to the company's budgeting procass during thair busiost tima af yoar Raw Materials Inventory Finished Goods Inventory PP&E, net $21,600 Interest Payable $17,550 TOTAL LIABILITIES $190,000 Common Stock The cocoa powder is sold in five pound containers, alternatively referred to as units. Budgeted sales in units are as follows: Retained Earnings TOTAL EQUITIES TOTAL LIABILITIES & EQUITIES . . o October 15,000 units o November 35,000 units o December- 40,000 units TOTAL ASSETS 750 $390,750 Requirements 1. Enter your name at the top of the INPUTS tab. units o February 45,000 units Each unit sells for $15.00. . 2. Prepare a master budget for the quarter ended December 31, 2019 that includes: Finished Goods Inventory Cost per Unit, Sales Budget, Schedule of Expected Cash Collections, Production Budget, Raw Matorials Purchasas Budgat, Dirac Labor Budgot, Manufacturing Ovarhaad Budgat, Salling & Administrative Expense Budget, Cash Budget, Budgeted Income Statement, & Budgeted Balance Sheet total o 11 schadulas. Each schadule should ba on a separata worksheet as appears in your templata. There isa template provided to you in the assignments link in the Excel Project folder with en input page you must use. Complete the shaded areas of the template only .All salas are on account. The company's collaction pattern is: 75% of sales are collected in the month of sale; 25% are collected in the month following. o o The company desires to have finished goods inventory on hand ot the end of each month equal to 15% of the following month's budgeted sales in units. On September 30, the company had 2,250 . 8 pounds of ccoo beans are recuired for each unit produced. The company dlesires to have materials on hand ct the end of each month equal to 20% of the following month's production needs. On September 30, the company had 28,800 pounds of cacoo beans on hand. 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 vlues or the input values. . . The cacco beans used in production cost 50.75 per pound. The company's payment pattem is 3. ChocoLasting Chacolata Company borrowed $100,000 so they would have adequate working capital to get through the busy season. The company would like to be able to repay the $100,000 at the end of December. However, a large, new competitor has emerged in the cocoa powder market, and Shecolastinsg is concornod that tha incroasad markat damand for cacoo beans will driva up the cost of thoir raw matorials and hurt their profitability and cosh flow. Use Goal Seek to see how far the cost per pound of coco becans could rise and still keep the company with a cash balance of $300,000 et the end of December. This will allow them to repay the loan and still have $200,000 cah on hand. o 70% of the month's purchases are paid for in the month of purchase; 30% is paid for in the following month. '" Each unit requires 6 minutes of labor time to make (0.1 hours) and the hourly employees are paid $15/hour. Wages are paid in the month incurred. Variable manufacturing overhead is $3.00 per labor hour. Fixed manufacturing overhead is $28,000 per month including S8,000 in depreciation that is not a * . HINT: To find gol seek, dlick on the Data tab and under Data Tools click on the Whct-If Analysis. If you are using an old version of Excel, you can find goal seek by going to the Tools menu. Enter your answer on the Goal Seek tab and then change the cost per pound of raw materials back to $0.75 on the INPUTS tab. current cosh outflow, . All cash disbursements for manufacturing overhead are paid in the month incurred. ariable salling and administrative oxpensas are $1.50 per unit sold. 4. After comparing your file against the project rubric below, yplood your completedExce file toCanvas yig the project link to submit. You may only submit once so make sure this is your final version Fixed selling and administrative expense is $25,000 par month including 57,000 in depreciation that snot a cash outflow of the aurrent month. All cash disbursements for administrative costs are poid in the month incurred. Good luck. Have fun. I hope you enjoy this project! Evaluation Rubric that will be used to assess your project Have correct answers that match the following check figures:- Total Sales for the Quarter$1,350,000 Cash Collections in October-$200,350. Desired Units of Finished Goods Ending Inventory for the Quarter7,500. Raw Material Purchases in December$258,300 Total Overhead Disbursements for the Quarter $88,575 October Ending Cash Balance $118,115- Net Income for the Quarter$352,125 Total Assets at the End of the Quarter $792,165 Goal seek cost per pound is $0.81 Possible Your Score 2 All cells are formula driven (you lose 2 points each time there is a number entered in the worksheets)- 15 points Your worksheet passes our tests of your formulas. Be sure that you only make one change at a time (and then change back to the original value before moving on to the next test). On your input tab we will: Change the selling price to $13.00 and net income should become $172,125 Change cash collections to 80% in month of sale and 20% in next month and the ending cash balance for the quarter on the cash budget is $373,865 Change units sold in December to 60,000 and the ending cash balance for December becomes $407,345 Total Score. 40 Comments PROJECTED BUDGETING DATA 2019 ChocoLasting Chocolatc Company 2020 Januar Februar Balance Sheet As of September 30, 2019 October November December y alos in tinits LLABILITLES&EQUITIES ccounts Pavabl ASSETS elling Price per Unit 130,Ace 31 EDNoes Payable $21, 600 $17, 550 10 12 13 Collections Collncte in tha Vonth of Sala Collected in Folloving Month Ram Vaterials Inventor Hinished Gd nventory R&D, Interest Peyeble LIABILITI 190Cu Slock Retained Earn TOTAL FEOUITIES TOTAL LIABILITIES& 16 Finished Goods Inventory: TOTAL ASSETS S390. 750QUITIES ing FG Inventory Requircnent 15% of naxt month'sunit sales 18 units 19 Raw Vatcrials Invcntory 21 2019 28, 300 pounds alerials Requird per Unil o FG aterials Cast pr $0. a prr ound 24 Ending RM Inventory Requirenent of next onth' s production needs id in onth of Purchase aid in Folowing oth Direct Lahor: Required per Uit of o Cost per lour hours per our 32 Manfacturing Overhead: per lebor hour Pixed CL $28, 000 per th pcr month cash Fixnd OH (includnd in ahove) 36 Selling & Administrative Expenses per unit $ 1.50 svl a, ouprr month cash Fixad SA included in abore) per month Cash Borrowings 13 per month Othcr urchaged Deceuber 31, 2019 nished Goods Inventory Cost per Un 4 Per Unit Direct Material Direct Labor 6 7 Variable Overhead 8 TOTAL FG INVENTORY COST PER UNIT 10 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 sheet 12 13 14 15 16 17 INPUTS Cost per Unit Sales Budget 2019 October November December TOTAL 6 Unit Sales 7Selling Price per Unit TOTAL SALES 10 13 15 16 18 Activate Windo Ga to Settings to Schedule of Expected Cash Collections 2019 October November December TOTAI Cash from Current Month Cash from Prior Month TOTAL CASH COLLECTTONS 10 12 13 15 16 Activate Windows Go to Setings to activete Wido 18 Exected Cash Colleztiors Production Budget 2020 January February 2019 October NovemberDecember TOTAL 6Sales in Units 7Plus: Desired Units of Ending Inventory 8 Total Unit Requirements 9 Less: Units in Beginning Inventory 10 PRODUCTION IN UNITS 12 13 Note: The amounts from January& February are necessary in order to complete December on this budget and for the Raw Materials Budget. 17 18 19 20 21 Activate Windows Go to Serting, tn artivate windo 4) Raw Materials Purchases Budget 2019 November December 2020 October TOTAL anuar 6Units Produced 7Pounds of RM Required Per Unit of FG lotal Pounds Used in Production 9Plus: Desired Pounds in Ending Tnventory 10 Total Pounds Required Il Less: Pounds in Beginning Inventory 12 RAW MATERIALS PURCHASES (POUNDS) 13 Cost per Pound 14 RAW MATERIALS PURCHASES (COST) 15 17 Activate Windows C6 Direct Labor Budget 2019 October November DecemberTOTAL 6 Units Produced 7 Labor Hours per Unit 8 TOTAL LABOR HOURS REQUIRED 9Rate per Labor Hour 10 TOTAL DIRECT LAB0R COS1 12 13 14 15 Activate Windows Go to Settings to activata Windows - Manufacturing Overhead Budget 4 2019 October November December TOTAL 6 Units Produced 7 Variable Overhead Rate per Unit 8 Total Variable Overhead Cost 9 Fixed Overhead 10 TOTAL OVERHEAD COST 11 Noncash Overhead Expenses 12 TOTAL OVERHEAD DISBURSEMENTS 13 14 15 16 Activate Wind to Settings to a Sudget elling & Administrative Expense Budget 2019 4 TOTAL ctober November Decembe Unit Sales Variable S&A per Unit Total Variable S&A Fixed S&A 6 10 TOTAL S&A EXPENSE 11 Noncash S&A Expenses 12 13 14 15 TOTAL S&A DISBURSEMENTS Activate Cash Budget 2019 October November December QUARTER 6 Beginning Cash Balance Cash Collections Available Cash Balance Cash Disbursements Direct Material Prior Direct Material Current Direct Labor Overhead S&A Equipment Purchase Interest Total Cash Di sbursements ENDING CASH BALANCHE 9 10 12 13 15 16 18 19 20 ActivateV Go to Setting ChocoLas ting Chocolate Company Budgeted Income Statement For the Quarter Ending December 31, 2019 Sales Variable Lxpenses: Variable Manufacturing Experse Variable S&A Expense 7 Contribution margin 10 Fixed Expenses: Fixed Manufacturing Expense Fixed S&A Expense 12 13 Net Operating Income Interest Expense 15 Net Income 16 17 INPU Income Statement ChocoLasting Chocolate Company Budgeted Balance Shee1t As of December 31, 2019 ASSETS LIABILITIES & EQUITIES 6 Cash 7 AccountsReceivable 8Raw Materials Tnventor 9 Finished Goods Inventory 10 PP&E, net Accounts Payable Notes Payable Interest Payable TOTAL LIABILITIES Common Stock Retained Earnings TOTAL EQUITIES TOTAL LTABTLTTTES & EQUITTES 12 13 TOTAL ASSETS 14 16 17 18 Go to Setings to ance Shaon F15 GOAL SEEK Raw Material Cost per Pound 10 12 13 14 15 16 17 INPUTS Goal Seelk