1. Prepare a detailed statement of Cost of Sales, using a volume level of 3,000 units, for each process (process choices in columns, input factors of production in rows). Organize the rows into variable and fixed cost sections. Include one input factor per row. The output of this exercise ends up being a Cost of Sales budget for 3,000 units of output that shows all of the input factors and delineates between variable costs and fixed costs. Try to make each cell a function of x (units produced), and include a single cell at the top of the column that contains output (3,000). . In other words, build a model of input factor cost that relies only on a single input - production volume, or x in F +vx. If you are having problems pre-visualizing the output schedule for this item, see the last page in this packet for my solution schedule - note that many elements have been blacked out. 2. Using the production technique costs in Figure 1: Production Technique Costs, derive the cost curve function (TC = F +vx) for each process Colt James is considering using. 3. For each process, add a column (to the right) to the schedule you prepared in item 1. In this column, calculate a budget for all of the input factors you listed in item 1, but this time assume a production output of 6,000 units. If you built a dynamic model in item 1, to do this task you should only have to copy formulas from column 1 to column 2. In other words, we are building a flexible budget for two different volume levels (3,000 and 6,000). 4. Now add a row at the top of your schedule and create a formula for total revenue (Px). Add a row at the bottom that calculates Gross Profit (1 = Px - (Ucost' + Fcos)). 5. Prepare a table of price per unit, variable cost per unit, and fixed cost per unit, and profit per unit for each process at the two volume levels (3,000 and 6,000). Finally, add a column to this table that calculates percent change for each of the rows (price, var cost, fixed, cost, profit) in the two volume levels (for each process). Beef per lb costs are all net yield unless otherwise indicated 1 lb of butchered beef yields 3 packages CJ mixes, CJ does SDO/o/s pkging full mfg Beef cost, per lb 7.5 3 3 Spice & other ingredients, per mix lb 2.5 1.5 1.5 0/S mixing cost, per mix lb 1 0 0 o/s packaging cost, per pkg 1 1 0 Cattle shipping cost, per 100 lbs 3.25 0 0 Outbound shipping cost, per lb 0 2 0 Inbound shipping cost, per lb 3.5 3.5 0 Variable materials costs: Packaging materials, per pkg 0 0 0.1 Variable labor costs: Butchering, per lb Mixing, per lb Bulk packaging for shipping, per lb Final packaging, per pkg 0 0 0 0 2.1 0.25 0.5 0 2.1 0.25 0 0.2 Fixed costs (per year): Shop foreman salary & benefits Rent/land usage (build on unused land on ranch) Utilities (primarily electric) Maintenance 0 0 2500 250 10000 0 3000 500 45000 0 6000 1000 Capital costs (per year): Production facility brick & mortar Freezer Storage shelving Mixing machine Packaging machine Oven/smoker Misc production equipment 1000 640 0 0 0 0 0 2400 640 200 500 0 0 200 5000 1920 1000 500 1000 650 1000 1. Prepare a detailed statement of Cost of Sales, using a volume level of 3,000 units, for each process (process choices in columns, input factors of production in rows). Organize the rows into variable and fixed cost sections. Include one input factor per row. The output of this exercise ends up being a Cost of Sales budget for 3,000 units of output that shows all of the input factors and delineates between variable costs and fixed costs. Try to make each cell a function of x (units produced), and include a single cell at the top of the column that contains output (3,000). . In other words, build a model of input factor cost that relies only on a single input - production volume, or x in F +vx. If you are having problems pre-visualizing the output schedule for this item, see the last page in this packet for my solution schedule - note that many elements have been blacked out. 2. Using the production technique costs in Figure 1: Production Technique Costs, derive the cost curve function (TC = F +vx) for each process Colt James is considering using. 3. For each process, add a column (to the right) to the schedule you prepared in item 1. In this column, calculate a budget for all of the input factors you listed in item 1, but this time assume a production output of 6,000 units. If you built a dynamic model in item 1, to do this task you should only have to copy formulas from column 1 to column 2. In other words, we are building a flexible budget for two different volume levels (3,000 and 6,000). 4. Now add a row at the top of your schedule and create a formula for total revenue (Px). Add a row at the bottom that calculates Gross Profit (1 = Px - (Ucost' + Fcos)). 5. Prepare a table of price per unit, variable cost per unit, and fixed cost per unit, and profit per unit for each process at the two volume levels (3,000 and 6,000). Finally, add a column to this table that calculates percent change for each of the rows (price, var cost, fixed, cost, profit) in the two volume levels (for each process). Beef per lb costs are all net yield unless otherwise indicated 1 lb of butchered beef yields 3 packages CJ mixes, CJ does SDO/o/s pkging full mfg Beef cost, per lb 7.5 3 3 Spice & other ingredients, per mix lb 2.5 1.5 1.5 0/S mixing cost, per mix lb 1 0 0 o/s packaging cost, per pkg 1 1 0 Cattle shipping cost, per 100 lbs 3.25 0 0 Outbound shipping cost, per lb 0 2 0 Inbound shipping cost, per lb 3.5 3.5 0 Variable materials costs: Packaging materials, per pkg 0 0 0.1 Variable labor costs: Butchering, per lb Mixing, per lb Bulk packaging for shipping, per lb Final packaging, per pkg 0 0 0 0 2.1 0.25 0.5 0 2.1 0.25 0 0.2 Fixed costs (per year): Shop foreman salary & benefits Rent/land usage (build on unused land on ranch) Utilities (primarily electric) Maintenance 0 0 2500 250 10000 0 3000 500 45000 0 6000 1000 Capital costs (per year): Production facility brick & mortar Freezer Storage shelving Mixing machine Packaging machine Oven/smoker Misc production equipment 1000 640 0 0 0 0 0 2400 640 200 500 0 0 200 5000 1920 1000 500 1000 650 1000