Tuesday, May 26, 2015

Bundle of my ExcelCalcs UpLoads

{The link to download the bundle is at the bottom of the post.}

Whilst my preference is that my spreadsheets are downloaded via ExcelCalcs and that queries are placed in the ExcelCalcs forum, it is apparent that people request the spreadsheets without need to join ExcelCalcs. Most of my spreadsheets are dependent on links to other workbooks, some .xls and others .xla, in consequence the download limits on ExcelCalcs may prevent new users from obtaining a fully working set of my workbooks. None of my spreadsheets are dependent on XLC , whilst I believe it is good software, I have moved beyond the need to format my calculations in standard text book format. For my comparison of MathCAD/SMath type applications versus spreadsheets read:
Electronic Calculations (eCalc's) .
My primary concern is calculating results and making decisions, not documenting the journey taken, as a consequence I make extensive use of visual basic for applications (vba), with MS Excel primarily being used to provide: a file format, editor, and reporting capability.

The spreadsheets are primarily concerned with structural design of manufactured structural products (MSP). Such products mainly comprise of steel, cold-formed steel, and timber sheds and canopies. The spreadsheets are modifications of the production spreadsheets we have used for design for many years at MiScion Pty Ltd (also Trading as Roy Harrison and Associates).

The spreadsheets we use in-house are for more complete buildings, involving member and connection design (eg. schShedDesignerR01.xls is a cut down version). The idea of releasing the spreadsheets was to provide the building blocks for others to build custom workbooks for other more specific building forms. If people want custom workbooks or vb.net/vba applications for their structural product then I can be contacted at MiScion Pty Ltd.

Structural design of a product can be divided between the following three major activities:
  1. Brief Description: Design Brief
  2. Evidence-of-Suitability
  3. Detail Description:Specification
Provision of Structural Calculations primarily falls into the evidence-of-suitability activity. Whilst drawing falls into both the design brief and specification activities.

Structural design can also be considered divided into the following:
  1. Product Structure/Description
  2. Dimension & Geometry
  3. Design Actions
  4. Design Action-Effects
  5. System/Component Stability/Resistance
    • Design/Assessment of Structural Form
    • Design/Assessment of Members
    • Design/Assessment of Connections
    • Design/Assessment of Interface/Supports (Footings)
The spreadsheets are listed below roughly divide into the above categories. For further information links to ExcelCalcs and Blog posts are provided. At present most of the blog posts simple display the ExcelCalcs page, but in the future I will add more detail about the workbooks. Also note that the graphics on the ExcelCalcs page were put there by the site administrator not myself, and don't always reflect the nature of the spreadsheet: and editing the page is limited, therefore the blog posts here will be up dated and modified first.

(c)Copyright 2015 Steven Conrad Harrison
The Bundled Package Comprises of the following Files:
FILENAME DESCRIPTION Blog ExcelCalcs
gpl.txt

readme.txt

Chart9BTC3 y04m06d14.pdf ColdFormed Steel Sheds Australia Height Span Limits of C-Sections. blog ExcelCalcs


TECHNICAL LIBRARY
schTechLIB.xla Libary of functions. schTechLIB contents blog ExcelCalcs
schTechLIBV2.xla Library without DAO references

ENVIRONMENT
Beaufort.xls Beaufort wind Scale blog ExcelCalcs
as4055.xls AS4055 Simplified wind loading for products blog ExcelCalcs
as4055v1.xls AS4055v2 Simplified wind loading for products blog ExcelCalcs
schWindAssessment_r02.xls Wind Loading to AS1170.2 blog ExcelCalcs



DIMENSION & GEOMETRY


drawWorkSheet2009.xls Experiments with Parametric Sketches using XY Charts. blog ExcelCalcs
schAcadLTCivilScriptWriter.xls Civil engineering Long Profiles and Sections. blog ExcelCalcs
schBuildingDimensions.xls Dimension and Geometry of Gable Frame shed Frame Member Lengths and Bracing Lengths. blog ExcelCalcs
schDrawSection.xls Draw Sections. blog ExcelCalcs
schCADDv2.xls CADD. blog ExcelCalcs


drawShed.zip CAD: Automatic generation of framing plans and elevations simple gable frame. blog ExcelCalcs
sample.dwg

schDrawShed.xls



vbaDXF.zip VBA Experiments Parsing ACAD DXF files. blog ExcelCalcs
DXFtoolsV01.xls

vbaDXF1.xls

vbaDXF2.xls

vbaDXF3.xls



drawShedDC1.zip CAD: Experiments with DesignCAD: Draw 3D framing of American Barn type structure. blog ExcelCalcs
Column1.dcd

schDrawShedDC1.xls



ExcelShapes.zip VBA Experiments with Excel Shapes Layer: Structural Framing Plans. blog ExcelCalcs
struMtrl.mdb

shapesTut01B.xls



drawTut.zip VBA Experiments with ACAD Script Automation. blog ExcelCalcs
drawTut01.xls

drawTut02.xls

drawTut03.xls

drawTut04.xls

drawTut05.xls

SampleSCR1.xls

UnSymmetricalGableSCR.xls

vbaDraw01punch.xls

vbaDraw02.xls

vbaDraw03.xls

vbaDraw04.xls



schHolePunching.xls Estimating: Hole punching requirements for roll-formed sections.




PRODUCT STRUCTURE TREE
bomStructureTreeStage3.xls exploded BOM (Bill of Materials). blog ExcelCalcs
schBOMStructureTreeStage1.xls Indented Bill of Material. blog ExcelCalcs


explodedBOM.zip IE/POM/CAPM Automatic Explosion of Bill of Materials. blog ExcelCalcs
Assemblies.xls

Materials.xls

mrpBOMv2.xls



ASSEMBLY ANALYSIS/DESIGN
schGableCanopyTimber.xls Gable Canopy to Australian Codes. blog ExcelCalcs
schKleinlogel03.xls Kleinlogel. blog ExcelCalcs
schShedDesignerR01.xls Wind Loads on Gable Frame to Australian Wind Code AS1170.2. blog ExcelCalcs


schDesignEngineR01.zip Application for Generation of Height Span Charts Gable Frame Sheds. blog ExcelCalcs
AcadScript.xls

BeamCalc.xls

Building00.xls

DBGtrace.xls

DataCosmos.xlt

DesignEngine.xls

GUI_lib.xls

Geom3D.xls

HeightSpanTableForm.xlt

Klein3.xlt

Primer.xls

RigidFrame.xls

Structure.xls

XStrings.xls

Xmaths.xls

as1170.xls

as4600.xls

struMtrl.mdb





MATERIALS
schStruMtrl.xls Structural Materials Data Steel. blog ExcelCalcs
schTimberMatrl.xls Timber Data for AS1720. blog ExcelCalcs
struMtrl.mdbMS Access database of properties. origin of schStruMtrl.xls.
MEMBER DESIGN
schDsgn1720.xls Calculator assessment of timber structures to AS1720. blog ExcelCalcs
schColdformedCee.xls Example Using Circular References to Force Iteration: Calculation Effective Section Modulus for Coldformed C-Section to AS4600. blog ExcelCalcs
schDsgn4600.xls
schDsgn4600R2013.xls
Calculator for assessment of cold-formed steel structures to AS4600.

Further information on set up can be found here.
blog ExcelCalcs
schDsgn4100.xls Calculator for assessment of steel structures to AS4100. blog ExcelCalcs


CONNECTIONS DESIGN
schTechNote022pt2.xls Tables for strength of bolted joints in thin cold-formed steel sheets to AS4600. blog ExcelCalcs


PRODUCTION AND OPERATION MANAGEMENT
schPlannerCalendar.xls Planner Calendar. blog ExcelCalcs
schWorkStudy.xls IE: Work study flow process chart. blog ExcelCalcs


GEOGRAPHICAL INFORMATION SYSTEMS
centralPlaces4.zip Experiments with Geographical Information System (GIS) central places. blog ExcelCalcs
CentralPlaces4ShedSuppliers.xls



MISCELLANEOUS
vbaObjects.zip VBA Experiments with Class Objects. blog ExcelCalcs
objTut01.xls

objTut02.xls

objTut03.xls



dataStruct.zip VBA Experiments with Abstract Data Structures. blog ExcelCalcs
dataStruct00.xls

dataStruct01.xls

dataStruct02.xls

dataStruct03.xls

dataStruct04.xls

dataStruct05.xls

orgDataStru.xls

treeExperiments.xls



vbaTuts.zip Excel/VBA Tutorials. blog ExcelCalcs
Node.dwg

NodeA.dwg

MyTest.txt

MyTest2.txt

TestNodes2.txt

vbaTut33.TXT

vbaTut00index.xls

vbaTut01.xls

vbaTut02.xls

vbaTut03.xls

vbaTut04.xls

vbaTut05.xls

vbaTut06.xls

vbaTut07.xls

vbaTut08.xls

vbaTut09.xls

vbaTut10.xls

vbaTut11.xls

vbaTut12.xls

vbaTut13.xls

vbaTut14.xls

vbaTut15.xls

vbaTut16.xls

vbaTut17.xls

vbaTut18.xls

vbaTut19.xls

vbaTut20.xls

vbaTut21.xls

vbaTut22.xls

vbaTut23.xls

vbaTut24.xls

vbaTut25.xls

vbaTut26.xls

vbaTut27.xls

vbaTut28.xls

vbaTut29.xls

vbaTut30.xls

vbaTut31.xls

vbaTut32.xls

vbaTut33.xls

vbaTut34.xls

vbaTut35.xls

vbaTut36.xls

vbaTut37.xls

vbaTut38.xls

vbaTut39.xls

vbaTut40.xls

vbaTut41.xls

vbaTut42.xls

vbaTut43.xls

vbaTut44.xls

vbaTut45.xls

vbaTut46.xls

vbaTut47.xls


The zip package can be downloaded free off charge from MiScion Pty Ltd: spreadsheet Bundle . MS Excel should automatically update the workbook links to the current folder. If create a subfolder of "My Documents" called eCalcs and below this create a folder called materials. The materials data files should be placed in this folder. The materials files are:
  • struMtrl.mdb
  • schStruMtrl.xls
  • schTimberMatrl.xls

Revisions:


  1. [26/5/2015] : Original Bundle Release
  2. [11/6/2015] : Updated the zip file to include revised versions of workbooks which had previously been uploaded to ExcelCalcs. These mainly comprise of changes to the AS4600 and AS4100 workbooks, which now have a button to open the section library, and  also worksheet application parameters to enable the DAO functions to find the MS Access database of sections properties (this currently only required for AS4600.). For more information refer to : My spreadsheets DAO and 64 bit Windows 7. For those not using AS4600 there is also a alternate version of schTechLIB which does not have the references to Microsoft DAO 3.6 object library, this is named schTechLIBV2.
  3. [01/02/2016] : Changed source of zip file from dropbox to MiScion Pty Ltd (the family business)

Sunday, May 24, 2015

Ramblings on Pricing and Selling Time

"The successful producer of an article sells it for more than it cost him to make, and that's profit. But the customer buys it only because it is worth more to him than he pays for it, and that's his profit. No one can long make a profit producing anything unless the customer makes a profit using it. " [Samuel B. Pettengill]
Price is not cost plus desired profit. Price is the mutually agreed value of exchange between buyer and seller. Further more unless there are crazy laws in place, it is not necessary to make a profit on each item, it is the over all business which needs to make a profit. Considered another way the contents of a retail store are an assembly of components, it is the sale of this assembly which has to make a profit, not the sale of an individual component within the assembly. If a sole proprietor does not make a profit then they will not get fed: profits are their bread and butter. If a proprietary limited  (Pty Ltd) company with owner-operators does not make a profit, it does not matter as the owners are paid wages by the company which is a different entity than the owners themselves. Therefore a Pty Ltd company can operate as a zero profit company. If there are non-working shareholders then the company needs to make profits.

Desired weekly wages, annual salary or hourly pay rate, are simply numbers pulled out off a hat, they are subjective opinions, they are not a matter of science. At any point in time, there are items in the market place available at some currently defined price. Some of these things are essential to the individual for  survival, others are imposed needs by society, and still others are optional extras. Thus we can identify 3 different levels of income:

1) Survival
2) Social
3) Optional

For an individual can also identify their current level of income, this may be higher than the minimum social level but less than the desired optional level. Whilst we can calculate these levels based on prices in the market place none of these levels are fixed. The survival income for one person can be considerably different than that for another. There will also be differences between different geographical locations, and different cultures. The income levels are therefore relative values not absolute values. Further more individuals have differing capacities to adapt and improvise: so that whilst one person may struggle with a specified minimum wage, another person may flourish or at least get by comfortably. As I have mentioned many times before, once something is released to the market and environment, people will find uses for it way beyond the original intents of the designer.

In similar manner to the income levels for individuals, it is possible to work out different price levels for goods and/or services. However when working these prices out we should also give consideration to the entire assembly of goods and services, or the over all product mix, and  the over all performance of the business. Most important of all is what the buyer is willing to pay. An amount for the price which exceeds what the customer is willing to pay, isn't a price. The amount the customer is willing to pay, is an impression, a feeling, a perception of worth: something pulled out off a hat. That whimsical intangible perception of worth, is transient and can be changed.

So can work out accurately how long a job is going to take and then multiply by an hourly rate, the resultant value however is not the price unless a customer is willing to pay such amount. There should be no buts or may be's about it, the hourly rate was pulled out off a hat. Sure may have checked industrial awards for minimum rates, and checked job advertisements for market rates in excess of the minima: but at the end  of the day all the numbers were dreamed up by someone, and the numbers felt right, or they were all they could afford. Not much point conducting complex mathematical calculations with fiction.

So calculated an amount from time and hourly rate, and the customer perceives it as way too high. If the customer can perceive it is too high, then the person doing the calculation should also be equally able  to perceive that it is too high. Now could review the project, reduce feature's and thus reduce total time, or otherwise revise estimates of times for the existing features, however the real problem is that not every hour is worth the same value.

By being focused on selling time and using a constant hourly rate as a measure of self worth, proposed prices are exceeding the markets willingness to buy. If can pull a number out off a hat for desired annual salary or for hourly rate, then can also pull a number out off a hat to define a fixed price for a project.

Prices placed on goods in a retail store are an indirect negotiation with the customer. If they like the proposed prices they will buy, if they do not like the proposed prices then they will not buy. The retailer therefore has to adjust their prices in response to the market behaviour.

In a more traditional market place, there is a more direct negotiation between buyer and seller. Further more the buyers go to the sellers: there is no annoying people who are not in the market to buy. Such people have no stated need for the items, and no stated discretionary income they are willing to spend on such items, then some seller comes disturbing them: the negotiation has one major outcome no current sale and no future sale. No future sale because there is no price low enough to encourage keeping such an annoying enterprise in business.

Like a retail store, or mail order catalogue, the proposed price has to be there to be found by the potential customer. That means that potential customers need to have reason to visit the vicinity of the stores even though they have no need or intention of buying anything. Shopping malls, plaza's and precincts draw people in with regular consumable services, and whilst there, they may then become buyers of more discretionary items.

So for example, the confectionery stall in a department store draws parents, with kids, into the store. If the confectionery stall is near the middle of the store, the customers then have to pass more expensive items, where upon there is potential to buy such things. The confectionery may not make much profit itself, but it is essential to the over all experience of the store which ultimately results in sale of high value items. Similarly the cafe, potentially makes less profit than the department store selling electrical gadgets and operating from the same precinct. But without the cafe, the precinct is likely to be empty and no one buying tthe electrical goods. Owners of such shopping centres should take such things into consideration before charging the shop rents which are typically seen as extortionate. Be certain as to who is generating the traffic, and who is benefiting from such traffic.

The point is, that price, cannot be calculated. Calculate expected times, give consideration to the cashflow requirements over the time frame of the project., use an hourly rate if must do, but make the final proposed price a judgement. This can be done by applying weightings either to the over all calculated price or to the components of the calculated price.

It should also be noted that whether selling goods or services, there is a component of work which is not directly attributed to the product being sold. The cost of this work needs to be recovered by applying an overhead charge to each item sold. Say for example that intend to spend 2000 hours per year, but only 1000 of those hours are directly billable. The other 1000 hours is used up on adiministration, research design and development of product made available, marketing and finding new projects to work on, learning and training to keep up to date and develop greater proficiency, and a multitude of other minor activities too small to identify or otherwise track. So every hour billed has to cover the cost of every two hours worked. if throw sick days and vacation time in, then every hour worked has to cover even more indirect costs.

That a product has to be designed before it can be made, isn't always noted by the customer, plus many products are just repetitive copies, so there is an expectation that costs of design have been recovered already. Additionally such costs of design are hidden from the customer with respect to physical goods like cars and electrical gadgets, whilst with services it is not all that apparent that design is required in the first place. So can end up formulating an hourly rate which is heavily weighted by factors which have little value to the buyer of services.

Consider a retail store again. The store sits there and people come in and either buy or don't buy the goods which are on display. At the end of the year either the shop made enough money to cover the living expenses of the owner or it didn't. One major problem for the store owner is not so much the price of the items, but the mix of the items and whether or not they are of any value to any of the people who visit the store. If have the wrong product then it won't sell at any price. If have the right product but wrong price then it won't sell.

Now goods and services are interchangeable. A product previously offered as a goods, can be converted to a service or substituted/complemented by a service. A product previously offered as a service can be changed into a physical goods. For example when lawnmowers are few and expensive, then more likely to hire someone to cut the lawn with a mower. When lawn mowers are plentiful and low cost then people more likely to cut their own lawn, if they have the time. There are thus competing  circumstances which shift the demand in favour of either the service or the dependent product.

Similarly designing a building is time consuming activity, but if design the building once and distribute the cost of design across many buildings then the cost of design per item is considerably reduced. This is essential for small buildings, as the cost of engineering is not proportional to the size of the building but to the complexity and risk involved. Small buildings with the same structural form as large buildings require the same design effort. With small buildings therefore the cost of engineering can exceed the cost of the building. With large building projects, fees are likely to be a proportion of the capital value of the project: this can be far in excess of the costs and therefore highly profitable. Whilst for the small buildings the cost exceeds the market price of the engineering: and therefore its is not a viable business to engineer small buildings on a one-off project basis. There has to be repetitious use of the engineering, in the form of documentation, so that the cost of the engineering can be recovered by the sales of multiple documents. Price is therefore intimately tied to the product definition.

Another example is the design and development of web sites, this can be a time consuming exercise and expensive. However today there are many blogging platforms, and more general purpose platforms like WordPress. Almost everyone can build themselves a website in a few minutes. The value of the services offered by the website designers/builders thus decreases significantly. The web designers can jump up and down and complain about the quality of such sites, identify flaws, wish for regulations and monopolies, talk about solutioneering and insist that custom design is a must: but at the end of the day they have to prove the value of their services. Just like architects and engineers of buildings the majority just talk about custom design for the site, they don't actually do it.

A general principle is to introduce no more than 20% of the total features as new, novel and original. These 20% of the features will take 80% of the time. The implication is that if we therefore know 80% of the features or characteristics with certainty then we can calculate the total time for the project: however doing so is likely to generate an unacceptable time frame and unreasonable price if we resort to selling time.

Rather what it tells us, is that the time for 80% of the characteristics is relatively certain, and that it effectively defines a fixed product with a fixed price. The other 20% of the characteristics involve a high level of uncertainty, and therefore a highly variable cost and consequently unknown ultimate price to the buyer. I would contend that the majority of people want to buy off-the-shelf exactly what they want. They do not want the uncertainty of custom design at the point in time they seek suitable product to satisfy their needs. Designers and makers therefore need to be pro-active and make product available for selection. Concept documents are easier to produce than full production documentation, and documentation is easier than actual manufacture of physical object. When it comes to computers and digital products then production and distribution are relatively low costs.

A society built around selling time is going to be in big trouble when production times are cut to near zero. Whilst time may be a measure of an employees work, it is not a measure of the employees contribution to the income of the business. People become self-employed so that they get a greater share of the income that they perceive their work actually generates: though without all the resources of their previous employer the usual result is that the self-employed person earns less but possibly with less pressure. Some employees have to generate income to cover wages of other employees: employees who do not produce directly billable or saleable work.

If don't sell time then what do you sell? In the main sell expertise and know how, and willingness to do the work required. For example the person selling lawn mowing services could charge by the hour or sell at a fixed price. They have a willingness to do work that the home owner may not be interested in, further more they can develop expertise in grass and weeds, they can turn a weed ridden patchy lawn, into a well manicured lawn. Household lawns have relatively similar sizes therefore all take similar time to complete, so a fixed fee would be possible. However different lawns irrespective of the size may require different amounts of work to maintain depending on the environment, and depending on whether in a poor state needing improvement or a good state needing maintenance. This may therefore suggest having hourly rates and selling time. I suggest this is a bad idea.

To start with most things have a minimum fee. So for example say that decide want $30/hour and determine that the time required for the job is 0.5 hours, therefore the fee for the job is $15. From a perspective of convenience however people are likely to have more $20 dollar notes in their wallets than fives and tens, at a sale price of $15 the supplier needs a ready supply of $5 dollar notes to provide change to the customers. The hourly rate was a desired rate, its not an absolute. Does $20 seem unreasonable for the work? Note that people go into stores and hand over a lot more than that, for physical goods, in a few minutes.

Take from another perspective the job takes 2 hours and therefore the fee would be $60, does that seem reasonable and is it convenient to pay? Would it be better if the fee was dropped back to $50? Similarly is an hourly rate of $29.95 a sensible rate to have? It may mislead some people into working with blocks of 20 instead of 30, which is roughly the marketing objective of such prices: however the customer is not going to be happy with the final price if they believe they have been misled.

Note that it is the seller setting the buyer up with perceptions of price. A seller should have some awarenesss of average incomes, and disposable portion of income, for their market sector. Also if accept cheques or credit cards then the inconvenience of various denominations is removed, but then again there are minimum fees which are acceptable.

Given that in an industrialised nation less than 10% of population is involved in agricultural, can assume that the majority of the population is attempting to grab a share of the disposable portion of peoples income, that portion not required for food or other essentials. Assuming that with time all the other essentials also only requires a small portion of the population: housing for example, once it is built it can last for 50 to 100 years possibly longer: so with the bulk of the housing existing already do not expect many people involved in housing construction. So have a relatively small percentage of population supplying essentials, and rest trying to find something to supply.

Time isn't really something that people want to buy, especially if the output of the work effort is lower quality than they could produce themselves, and their own hourly pay rate is less than that of the service purchased. The funds available to the buyer are an important aspect of how they perceive value. For example say that a person can put $50 away each week, then they can save $2600 per year. Then chances are they are not going to be impressed paying some $1800 for a weeks worth of design producing a few drawings and some calculations: if the perception concerns selling and buying time and the report they get turns out to be defective relative to their needs but otherwise what they asked for. The longer it takes to save the money the more discerning the buyer is likely to be about the purchases they make: on the other hand they may lack adequate knowledge to make a proper selection of suitable supplier.

{... lost my track.}


However, just because should avoid selling time, doesn't mean shouldn't measure time.