Trusted by +2 million users,
assist thousands of students everyday
assist thousands of students everyday
Showing pages 1 to 3 of 8 pages
1Administrative DetailsAll submissions must have a cover page that lists the names of group members, section andinstructor’s name. In addition,be sure to format your submission so that it is easy to read(e.g., each schedule prints on a single page or you tape together two pages and then foldthem. Please do not use a font size smaller than 10. Marks will be deducted if it is not easy tofollow.Purpose of the ProjectThe project consists of two parts: the purpose of Part I is to get the financial model up andrunning so that you can do the “what-if” calculations comprising Part II of the project. Part IIinvolves running a few “what if” scenarios. Regardless of the path you follow in your businesscareer, the ability to create a financial model, run sensitivity or “what-if” analyses, derive basicfinancial advice based on running such a model, and having proficiency in excel are all thingsthat will not only serve you well, but are skills that employers expect a university businessgraduate to possess. Thus while this project is a lot of work, itwillserve you well in the future.Moreover, this assignment will help solidify many of the topics taken earlier in the course.Finally, it will serve to reinforce material taken in Financial Accounting as you have toremember how statements articulate and how certain items are calculated.Anticipated Workload and AdviceThis assignment will go considerably smoother if you heed the following advice:Be sure all group members are working off the same set of assumptions.In anticipation of the “what-if” analysis to be conducted in Part II, it isNECESSARYtodevelop your spreadsheets by makingreferenceto the assumptions worksheet ratherthan simply putting the assumption value manually in the cell of the schedule you areworking on. This way, you will only need to change the value of the assumption in theassumption worksheet and not worry about changing anything else. This is what makesworking with spreadsheets so powerful.You will lose considerable marks if you do notdo this.
2oTo put this in different words, other than the assumption worksheet, eachworksheet you prepare should NEVER enter a hard number in a cell. Instead,each cell needs to reference other cells or the assumptions worksheet.Utilize the check numbers that are provided below to make sure you are on the righttrack.Follow the hints provided in the section “Hints for Part I” that will help steer youthrough the difficult parts of the assignment.Read the relevant sections of your textbook to create each schedule. Other than for thecombined cash budget, the assignment follows the text very closely. Also, review theinstructor slides used in our November 2 class connected with P9-61A.Don’t spin your wheels.If you can’t match the check number provided or if yourbalance sheet doesn’t balance and you have made a reasonable attempt to discoveryour error (say, 20 minutes) please come and seek help from your professor or emailhim.
3The Assignment – Part I (worth approximately 65% of the total)CapCo is a small manufacturer of “toppers” that fit over the truck boxes of pick-up trucks. Thecompany is located in Lethbridge, Alberta. You are a recent business school graduate and havebeen employed with the company for two months. The company has not previously engaged ina formal budgeting process and you suggested they should because it is your belief thatconsiderable improvements could be made in how the business is operated. You have beenasked to help create next year’s2016 budget since you are the only one with formal businesstraining. You are excited by this prospect as it will allow you to apply the skills and tools youlearned in MGT 2400.Part I of the assignment is to develop a financial model using the information provided in theassumptions worksheet and the December 31, 2015 Balance Sheet (prepared by the firm’sexternal accountants) that can be located in the excel template that has been provided to youin the Budget Assignment folder of MAL (the file is called CapCo student excel template).Among other things, you need to submit the following:Paper submission:oCover sheet with group member names, class section number (B) andinstructor’s name (Darrell Mathews)oSchedule 1: Sales BudgetoSchedule 2: Production BudgetoSchedule 3: Direct Materials BudgetoSchedule 4: Direct labor BudgetoSchedule 5: Manufacturing Overhead BudgetoSchedule 6: Operating Expense BudgetoSchedule 7: Cash Collections BudgetoSchedule 8: Cash Payments BudgetoSchedule 9: Combined Cash BudgetoSchedule 10: Budgeted Income Statement for the 2016 yearoSchedule 11: Budgeted Balance Sheet as at December 31, 2016At the bottom of the balance sheet SHOW how you obtained yourbalances for:Accounts receivablePrepaid insuranceFixed AssetsAccounts payableInterest Payable