Exercise
3
Construct
the following spreadsheet to calculate a company's cashflow over
a six-month period. The company makes three products.
- Calculate
the sales for Product A for January. (Units Sold * Selling Price).
NB - The Selling Price for Product A remains constant throughout
the six month period, so give it an absolute reference (=$B$5*D5).
- Copy
this formula to obtain the sales of Product A throughout the
six-month period.
- Calculate
the sales for Product B and Product C throughout the six-month
period.
- Calculate
the Total Sales for each month.
- Calculate
the costs for Product A for January. (Units Sold * Cost Price).
NB - The Cost Price for Product A remains constant throughout
the six month period, so give it an absolute reference (=$C$5*D5).
- Copy
this formula to obtain the variable cost of Product A throughout
the six-month period.
- Calculate
the Total Variable Costs for each month.
- Calculate
the Total Costs for each month (Total Variable Costs + Fixed
Costs).
- Enter
the January figures; Bal B/Fwd (0), the Total Sales reference
(=D15) and the Total Costs (=D26) into the Cashflow Analysis
grid.
- Calculate
the Monthly Surplus/Deficit for January (Total Sales - Total
Costs).
- Calculate
the Bal C/Fwd for January (Bal B/Fwd + Monthly Surplus/Deficit).
-
Enter the Bal B/Fwd for February. This is equal to the Bal C/Fwd
for January (=A33).
- Copy
each formula throughout the six month period.
You
can check your answers here.
 Contents:
Spreadsheets
Skill
Check: Spreadsheets
|
