Cash Budget and Financial Statements: A Comprehensive Analysis

The assignment requires a financial model to be created using Excel, which involves calculating cash balances, interest expenses, cost of goods sold, and depreciation. The model should also account for non-cash expenses such as bad debt expense, prepaids, and depreciation. Additionally, the instructor has provided hints and guidance on how to use the IF THEN function in Excel to calculate repayments and borrowings. Furthermore, the assignment emphasizes the importance of balancing the balance sheet by ensuring that total assets equals total liabilities and shareholders' equity.

1
Administrative Details
All submissions must have a cover page that lists the names of group members, section and
instructor’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 fold
them. Please do not use a font size smaller than 10. Marks will be deducted if it is not easy to
follow.
Purpose of the Project
The project consists of two parts: the purpose of Part I is to get the financial model up and
running so that you can do the “what-if” calculations comprising Part II of the project. Part II
involves running a few “what if” scenarios. Regardless of the path you follow in your business
career, the ability to create a financial model, run sensitivity or “what-if” analyses, derive basic
financial advice based on running such a model, and having proficiency in excel are all things
that will not only serve you well, but are skills that employers expect a university business
graduate to possess. Thus while this project is a lot of work, it will serve 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 to
remember how statements articulate and how certain items are calculated.
Anticipated Workload and Advice
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 is NECESSARY to
develop your spreadsheets by making reference to the assumptions worksheet rather
than simply putting the assumption value manually in the cell of the schedule you are
working on. This way, you will only need to change the value of the assumption in the
assumption worksheet and not worry about changing anything else. This is what makes
working with spreadsheets so powerful. You will lose considerable marks if you do not
do this.

2
o To put this in different words, other than the assumption worksheet, each
worksheet 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 right
track.
 Follow the hints provided in the section “Hints for Part I” that will help steer you
through the difficult parts of the assignment.
 Read the relevant sections of your textbook to create each schedule. Other than for the
combined cash budget, the assignment follows the text very closely. Also, review the
instructor 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 your
balance sheet doesn’t balance and you have made a reasonable attempt to discover
your error (say, 20 minutes) please come and seek help from your professor or email
him.

3
The 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. The
company is located in Lethbridge, Alberta. You are a recent business school graduate and have
been employed with the company for two months. The company has not previously engaged in
a formal budgeting process and you suggested they should because it is your belief that
considerable improvements could be made in how the business is operated. You have been
asked to help create next year’s2016 budget since you are the only one with formal business
training. You are excited by this prospect as it will allow you to apply the skills and tools you
learned in MGT 2400.
Part I of the assignment is to develop a financial model using the information provided in the
assumptions worksheet and the December 31, 2015 Balance Sheet (prepared by the firm’s
external accountants) that can be located in the excel template that has been provided to you
in 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:
o Cover sheet with group member names, class section number (B) and
instructor’s name (Darrell Mathews)
o Schedule 1: Sales Budget
o Schedule 2: Production Budget
o Schedule 3: Direct Materials Budget
o Schedule 4: Direct labor Budget
o Schedule 5: Manufacturing Overhead Budget
o Schedule 6: Operating Expense Budget
o Schedule 7: Cash Collections Budget
o Schedule 8: Cash Payments Budget
o Schedule 9: Combined Cash Budget
o Schedule 10: Budgeted Income Statement for the 2016 year
o Schedule 11: Budgeted Balance Sheet as at December 31, 2016
 At the bottom of the balance sheet SHOW how you obtained your
balances for:
 Accounts receivable
 Prepaid insurance
 Fixed Assets
 Accounts payable
 Interest Payable

4
 Retained Earnings
 You can than link your balance sheet amounts for these items directly to
these calculations
Quick check: be sure you understand why you have been asked to prepare these schedules in
the above order rather than in a different order.
It is recommended that every student in the group attempt Schedules 7, 8 and
9 on their own because it is customary on the final exam to ask students to do
a cash budget.
BE SURE YOU USE COMMAS WITHIN YOUR NUMBERS (I.E., 100,000 AND NOT 100000) AND
ROUND CALCULATIONS TO THE NEAREST DOLLAR. Normally, you only use decimals in the
assumption worksheet when a number is an input into the model, such as the selling price of
$2.52. This allows someone reviewing the model to assess the input values going into the
model.
Other requirements for Part I
To ensure that your model is properly operating please make the following changes to your
assumptions and rerun the model to ensure that your balance sheet balances and you obtain
the correct check figure. The electronic version of your model that you submit by email to the
instructor does not need to consider these changes. Simply add the following schedules to your
paper submission:
Schedule 12: Assume the original assumptions except that the beginning January sales figure is
100 units rather than 40 units. Total assets on the balance sheet should equal $646,430.
Simply submit your revised balance sheet as schedule 12 in the paper submission. On the line
below the title “Schedule 12,” indicate the assumption changed.
Schedule 13: Return to the original assumptions. Assume the original assumptions except that
finished goods inventory is equal to 50% of next month’s sales. Total assets on the balance
sheet should equal $380,067. Simply submit your revised balance sheet as Schedule 13 in the
paper submission. On the line below the title “Schedule 13,” indicate the assumption changed.
Schedule 14: Return to the original assumptions. The spoilage factor for direct materials
decreases to 5% from 15%. This translates into fiberglass fabric in square meters and resin in
litres decreasing to 10.8 and 4.5, respectively. Total assets on the balance sheet should equal

5
$403,256. Simply report your revised balance sheet as Schedule 14 in the paper submission. On
the line below the title “Schedule 14,” indicate the assumption changed.
Schedule 15: Return to the original assumptions. The purpose of this question is to help
solidify your understanding of chapter 3 material. It consists of Parts A to F. Part A: on the
student template labeled as Sch 15 there is a template for completing the cost flows. Complete
these cost flows to show how the number transferred to COGS is equal to the COGS number
you calculated on Schedule 10. Respond in the space provided to questions B, C, D, E and F.
Part I check figures
Schedule 1: total sales for the year = $2,146,200
Schedule 2: total cash collected in January = $122,179. Total cash collected in February =
$133,876.
Schedule 3: Units to produce in May = 48
Schedule 4: total cost of direct material in August = $135,520 (fabric purchases = 68,992, resin
purchases = 18,144 and glass assemblies = 48,384)
Schedule 5: Total direct labor cost for 2016 = $107,184
Schedule 6: total Manufacturing Overhead in April = $14,652
Schedule 7: total operating expenses in November = $33,467
Schedule 8: Total cash paid in March = $158,540
Schedule 9: Ending cash balance in January = $19,285. Borrowing in June = $87,069. Interest
payment in September = $858. Repayment of credit line in October = $2,530. Ending credit line
balance in December = $129,265.
Schedule 10: interest expense (paid plus accrued) = $6,281; cost of goods sold = 1,693,923
Schedule 11: total assets = 757,283.

6
Hints
1. Treatment of noncash expenses like bad debt expense, prepaids and depreciation. These expenses
need to be included when calculating your income statement. However, they need to be backed out
when preparing your cash payments schedule (see p.543-4 of your text).
Question to consider: why do we have to back out bad debt expense from the cash payments
schedule?
2. Accruing Dec 2016 interest expense. Because the model follows the instruction that you pay
interest charges at the start of the month, if you have a credit line outstanding at the end of
December you will have to accrue the interest expense for that month (you don’t pay it until January
1, 2017). Therefore, interest expense for 2016 will consist of the interest accrued for the months of
January to December which equates to interest paid in the months of February to December plus
December’s accrued interest. Note: the interest paid in January 2016 was interest expense accrued
for the 2015 year.
Question to consider: why do we have to accrue the interest rather than wait until we pay it?
3. Income tax. If you have a loss you clearly don’t pay any income tax. However, in Canada if you have
paid income tax in the past you can get a refund of past taxes paid. So, if you report a loss, multiply
the loss by the income tax rate and this will then become a receivable (the government will owe you
money). In other words, you will have a negative number for tax expense. When this negative
number is subtracted from the loss before taxes, your loss will be smaller due to the tax refund.
4. Calculation of Cost of Goods Sold and Ending Inventory. The assumptions worksheet indicates that
a first in first out (FIFO) inventory assumption is being used. Your model will need to incorporate
this assumption in three ways:
a. Raw materials inventory: ending raw materials inventory needs to be valued using end of
year costs, i.e., 2016 material costs * number of units in ending inventory
b. Cost of Goods Sold: needs to be calculated in two steps:
i. Value of beginning finished goods inventory (located on the December 31, 2015
balance sheet) >> this gives you the cost of beginning units in finished goods
inventory that are assumed to be sold first under FIFO
PLUS
ii. (Total unit sales in 2016 minus number of units in beginning finished goods
inventory) * 2016 cost per unit to manufacture. This gives you the cost of the units
that were made and sold in 2016
c. Finished Goods inventory: units in ending finished goods inventory need to be valued using
the 2016 cost per unit to manufacture.

7
5. For calculating Dec 2016 retained earnings see the instructor slide presented in the Nov 3 lecture or
visit your textbook.
6. Combined cash budget – interest rate. Don’t forget to divide the annual interest charge by 12 to
get the monthly interest rate.
7. Combined cash budget – repayments and borrowings.
You will need to use the IF THEN function to perform this calculation. A brief article entitled “Excel
Nested IF statements” has been provided to help you with the syntax of this function.
The syntax for the IF THEN function is made up of three arguments corresponding to IF, THEN, ELSE.
IF corresponds to the statement you are testing
THEN corresponds to what you want to happen if the statement is true
ELSE corresponds to what you want to happen if the statement is false
I have provided you with help on this function as well as an example from P9-61A (see the solution
that was posted for this problem). The command for P9-61A is slightly different because in that
problem you had to borrow in even 1000s rounded up. You don’t have that requirement in this
case.
Under the borrowings row:
IF total cash needed is greater than the total cash available (STATEMENT TO TEST), which reflects a
deficit position, THEN we need to borrow money. If the statement to test is false (i.e., total cash
needed is equal to or less than the total cash available), we do not have to borrow money. The
falsity of this statement triggers the “else” part of the formula and we would simply put “0” (i.e., we
don’t need to borrow)
The syntax for the function is as follows: =IF (statement you want to test, what I want to do if the
statement is true, what I want to do if the statement is false)
Under the repayment row:
If total cash needed is less than total cash available (that is, we have an excess of cash),
then pay the lesser of a) our excess cash or b) last month’s ending line of credit. The
syntax for last week’s problem (P9-61a) under this row is all you need.
8. Depreciation. You need to keep track of the accumulated depreciation separately for plant and
equipment to arrive at appropriate net balances to be reported on the balance sheet.
9. Line of credit. A line of credit simply means that the bank authorizes you to borrow up to the stated
limit. If you don’t need it you don’t borrow it. Also, for the purposes of the financial model, don’t
worry if you exceed the stated credit line. This is simply telling you that management needs to take

8
steps (change some of the assumptions) in order not to exceed the credit limit. This is one of the
benefits of preparing a financial model.
10. Balance sheet. The key step to ensure your model is correct is to (i) arrive at the same total
Assets figure as provided in the check figure and (ii) to have the total liabilities and
shareholders’ equity figure agree with this number (i.e., the balance sheet balances).
Instructor email help
During the period students are working on the assignments, the instructor offer comments based on
common queries received from students. Such comments might range from advice to help overcome
common problems raised by students or to rectify mistakes in any check figures. Thus students should
always pay attention to these emails which will use the subject heading of “budget project help.” Each
email will simply tack on the new point preceding the other points made earlier. This way, by examining
the last email you will have all the points in one handy spot. Be sure to look for these emails.