The Financial Model Up and Running
Added on - 16 Sep 2019
1Purpose 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 AdviceTen to 15 hours is not an unreasonable amount of time to complete Part I so you areencouraged to manage your time accordingly and, most importantly, split the work up so thatgroup members can work on separate spreadsheetsconcurrently. Google spreadsheets willallow you to create aspreadsheetand edit with others at the same time!Part of the difficultystudents have is that they have to utilize basic knowledge of material learned in financialaccounting (MGT 2100) and have forgotten it.This 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.oTo 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.
2Utilize 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:Email the instructor a copy of your model putting the last names of group members inthe subject title of the email. Note: as will be clear below,the electronic model youemail the instructor is ONLY the one used to arrive at schedules 1 to 11 and schedule15.You do NOT need to submit an electronic model for schedules 12 to 14.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 receivable